Error catching a form in Access using VBA

Question

So i have a form that's updating a table in Microsoft Access. I'm using a BeforeUpdate event call to stop the form from updating if the 'first name' hasn't been filled in.

I basically only want the form to be saved once everything has been filled in, and then switch to see the updated table (the bit under If not cancel).

Currently the popup box to say 'You must enter a First Name' correctly fires, the next pop up box asking if the user wants to undo any changes fires. But then a pop up box saying 'No current record' appears and then the view changes to the table. I'm not sure why this is all happening.

Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)

   Cancel = False

' perform data validation
If IsNull(Me.customer_f_name) Then

   MsgBox "You must enter a First Name.", vbCritical, "Data entry error..."
   Me.customer_f_name.BorderColor = vbRed
   DoCmd.GoToControl "customer_f_name"

   Cancel = True

End If

If Not Cancel Then

    DoCmd.SelectObject acTable, "ticket_tracker_table"
    DoCmd.Requery
    DoCmd.GoToRecord acDataTable, "ticket_tracker_table", acLast

End If

' if the save has been canceled or did not pass the validation , then ask to Undo changes
If Cancel Then

    If MsgBox("Do you want to undo any changes?", vbYesNo, "Confirm") = vbYes Then
        Me.Undo
    End If

End If

End Sub

Which statement causes the message that you do not like? Its not clear whether Yes or No was responded to the prior question. You should be able to set debug on the statement Cancel = False and then step thru until you find more info. — donPablo
I am not by a windows pc now, but I think your error is that you are trying to access the new record before you saved it, i,e, this code is still running in the BeforeUpdate time — YHoffman

June7 June7 · Accepted Answer · 2019-01-31T04:38:28

Review https://docs.microsoft.com/en-us/office/vba/api/Access.Form.BeforeUpdate(even)

Instead of GoToControl, use SetFocus. I did a very simple test with the Undo question and it does work:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.customer_f_name) Then
    MsgBox "Must enter customer name."
    Cancel = True
    If MsgBox("Do you want to undo edits?", vbYesNo) = vbYes Then Me.Undo
    Me.customer_f_name.SetFocus
End If
End Sub

Suggest you put table code in AfterUpdate event.

Error catching a form in Access using VBA

2 Answers