validate data and cancel if answer is false

[Talismanic]

I have a control that needs to stop a person from entering a employee number that is not on file. I have a dcount to do that part of it and a message that warns the user that the number is not valid.

Now I want to force the focus back to the employee number control and clear the wrong data out of it when it happens. This is what I have:

Private Sub EmployeeNumber_BeforeUpdate(Cancel As Integer)
                                         
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim Answer As Variant
  
If DCount("[LastName]", "EmployeeList", "[EmployeeNumber]= " & _
Me!EmployeeNumber) = 0 Then
 
 strMsg = "Please try a different number"
 strTitle = "Invalid Employee Number."
 intStyle = vbOKOnly + vbInformation
 Answer = MsgBox(strMsg, intStyle, strTitle)
 
 If Answer = vbOKOnly Then Cancel = True
  
End If

End Sub

I can get it to work (partially) if I have the code like this but that allows the user to hit cancel and go on. However when OK was pressed the focus was set back to the employee number.

intStyle = vbOkCancel + vbInformation

If answer = vbCancel then Cancel = True

Exit Sub

 

[BarkerD]

You can use the Docmd.CancelEvent to keep the user in the control.

All you really need is:
msgbox$(strmessage)
doCmd.CancelEvent
me.EmployeeNumber.Setfocus

If you want to replace the text to what was previously in the control, capture the data in a variable with the OnEnter event, and then re-assign the data after the CancelEvent.

Hope this helps.

 

[R. Hicks]

Try this:

Private Sub EmployeeNumber_BeforeUpdate(Cancel As Integer)
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
strMsg = "Please try a different number"
strTitle = " Invalid Employee Number."
intStyle = vbOKOnly + vbInformation

If DCount("[LastName]", "EmployeeList", "[EmployeeNumber]= " & _
Me!EmployeeNumber) = 0 Then
MsgBox strMsg, intStyle, strTitle
Cancel = True
End If
End Sub

HTH
RDH

[This message has been edited by R. Hicks (edited 11-15-2000).]

 

[Talismanic]

Thanks, that did it!