Is there a built in MS Access VBA Event to simplify Data Verification and SetFocus

Question

Bit of a rookie issue here. How do you deal with data verification using Access Events? The problem is that when I use SetFocus to return the Cursor to the field with the errant data, Access goes through the _Exit and/or _LostFocus Events of the next Control in the Tab Order. If those include data validation procedures, the desired SetFocus is circumvented.

Using techturtles answer, I came up with this "solution" (read "hack").

Private Sub ServicingEmployee_LostFocus() 'Check data validity
    If dataEntryCancelled Then Exit Sub

    Dim cntrl As Control
    Set cntrl = Me.ServicingEmployee
    Dim myResponse As Integer

    If IsNull(cntrl.Value) Then

        myResponse = MsgBox("This field cannot be left blank.", vbOKOnly, "Enter Date Collected")
        dataErrorInPreviousField = True
        **'Next line sets a Public Control**
        Set controlWithDataEntryError = Me.ServicingEmployee
    End If

End Sub

Private Sub Client_GotFocus() '**Check for data Error in previous Control according to Tab Order**
    If dataErrorInPreviousField Then Call dataErrorProc
End Sub

Public Sub dataErrorProc()
    With controlWithDataEntryError
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    dataErrorInPreviousField = False

End Sub

Private Sub Client_Exit(Cancel As Integer) '**Example of Bypassing _Exit Event**
    If dataEntryCancelled Or dataErrorInPreviousField Then Exit Sub
    .
    ...
End Sub

My question is this: Is there a simpler way to accomplish this?

Consider the control's BeforeUupdate event.instead of LostFocus. — HansUp, Jul 10 '18 at 19:49
I agree with @HansUp - A control's `BeforeUpdate` update is best for individual field validation checks. A Form's `BeforeUpdate` event is great for checking the whole table for blank entries in required fields — dbmitch, Jul 10 '18 at 21:35
@dbmitch, you've answered at least one of my questions before so I have an related question. Are you familiar with VB.net? Is the transition to VB.net worth the learning curve? I am familiar with Objective C. — PhillipOReilly, Jul 12 '18 at 02:41

score 0 · Accepted Answer · answered Jul 11 '18 at 13:01

First, I would strongly encourage you to add table constraints in addition to your form validation, if you haven't already done so. Assuming this form is tied to a table in Access, just make sure the ServicingEmployee field (in the table) is set to required. That way, you have another level of validation making sure the field has a value.

For form validation, you can use either the form's or control's BeforeUpdate event. If you use the form's event, you only have to run the check once.

I would also suggest having a separate function (within the form) to check validity. Something like this (here, I'm using a form tied to an Employees table, with a first name and last field which should both be non-null):

Private Function isFormValid() As Boolean
   ' we start off assuming form is valid
   isFormValid = True

   If IsNull(Me.txtFirstName) Then
      MsgBox "First name cannot be blank", vbOKOnly
      Me.txtFirstName.SetFocus
      isFormValid = False
   ElseIf IsNull(Me.txtLastName) Then
      MsgBox "Last name cannot be blank", vbOKOnly
      Me.txtLastName.SetFocus
      isFormValid = False
   End If
End Function

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Not isFormValid Then
      Cancel = 1
   End If
End Sub

Is there a built in MS Access VBA Event to simplify Data Verification and SetFocus

1 Answers1