Error Checking 3

[deharris2003]

I have an entry form that the user can enter new data. I have written code to validate the entry of one field. The code does give a message if the user enters a value that has already been entered into the database. The Problem I have is that it will not set the focus back to the field and allow the user to re-enter the data. here is what I currently have.

Dim db As Database
Dim rst As Recordset
Dim StrSQL As String

Verify = False

StrSQL = "SELECT Personnel.[EmpID#] AS " & _
"ID ,Personnel.Name " & _
"FROM Personnel " & _
"WHERE (((Personnel.[EmpID#]) = '" & Me.ID & "'));"

Set db = CurrentDb
Set rst = db.OpenRecordset(StrSQL)

If Not rst.EOF Then
StrMessage = "The employee ID " & rst("ID&quot & " already exists with " & _
"the name of " & rst("Name&quot & ". Please Re-Enter a new " & _
"employee ID or exit."

MsgBox StrMessage, vbOKOnly, "ID Already exists"
Continue = True
End If

If Continue = True Then
Me.ID.SelStart = 0
Me.ID.SelLength = Len(Me.ID.Text)
End If

Any help is much appreciated

 

[hymn]

If Not rst.EOF Then
StrMessage = "The employee ID " & rst("ID&quot & " already exists with " & _
"the name of " & rst("Name&quot & ". Please Re-Enter a new " & _
"employee ID or exit."

MsgBox StrMessage, vbOKOnly, "ID Already exists"
Continue = True
yournamedfield.setfocus

Hope this helps
Hymn

 

[TonyJollans]

Hi deharris2003,

Where are you putting the code? If it is in the Control's BeforeUpdate event and you set Cancel = True when validation fails, then it should work as you want.

Enjoy,
Tony

 

[PaulF]

using vbOKOnly will return vbOK only, not Continue... You need to provide a Selection Option to the User such as vbRetryCancel, or vbOkCancel

If MsgBox StrMessage, vbRetryCancel, "ID Already exists" = vbCancel Then
Cancel = True
Exit Sub
Else
Cancel = True
Me.ControlName.SetFocus
End if


Paul

 

[deharris2003]

Thanks for the Help, I got it working with the Cancel = True
statement.