Stop update event in ACCESS 2003

I need to have the update stopped if the user has not completed the date when trying to update the country field I get the attached error message.  So, how do I stop in the before update event data entry and move the focus to another field?
Private Sub cboCountry_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.txtDate) Or Me.txtDate = "" Then
        MsgBox "Please enter open date first prior to " & vbCrLf & _
               "selecting a country.  Temp Number assignment " & vbCrLf & _
               "requires this data entry sequence.", vbOKOnly
        Me.cboCountry.Value = ""
        Me.txtDate.SetFocus
        Exit Sub
    End If
End Sub

Open in new window

ErrorMessage.jpg
Sandra SmithRetiredAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
You can't do this in a Before Update event:

Me.cboCountry.Value = ""

You are trying to change the value and that is not allowed in a BU event.  Maybe you want this:

Private Sub cboCountry_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.txtDate) Or Me.txtDate = "" Then
        MsgBox "Please enter open date first prior to " & vbCrLf & _
               "selecting a country.  Temp Number assignment " & vbCrLf & _
               "requires this data entry sequence.", vbOKOnly
        Me.cboCountry.UNDO
       Cancel = True
           'Me.txtDate.SetFocus  ' Can't do this either
        Exit Sub
    End If
End Sub
0
 
lee555J5Commented:
See "Cancel As Integer" in the sub declaration. That is a fake boolean (it says integer) that is checked in this event. Change "Exit Sub" to "Cancel = True".
Lee
0
 
lee555J5Commented:
Cancel initializes to 0. If your data validation condition is executed, make Cancel equal something other than 0, such as True (-1).
Lee
0
 
Sandra SmithRetiredAuthor Commented:
Ok, seems the best I can achieve is at least create a message.  I will have to think about how to create the auto-generated ID as the ID is based on the date and country.  Will have to so a little shifting.  I think you both were pointing the same direction, but DatabaseMX's was easiest as I just plunked it into my code and it worked.
Thank you both.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.