Need to Circumvent general Microsoft Access Error?

[Heatshiver]

I have a field to input a User ID. It is validated by another table where all the User ID's are stored. The code I have inserted works and makes sure that the user receives a message to describe the problem:

Private Sub UserID_AfterUpdate()

If IsNull(DLookup("UserNum", "tblUsers", "UserNum = '" & [Forms]![frmGenSum]![UserID] & "'")) Then
MsgBox "Please input a valid User ID."
[Forms]![frmGenSum]![UserID] = ""
Else
'Do Nothing
End If

End Sub

The problem I am having is that Microsoft has its own error message after mine: "You cannot add or change a record because a related record is required in table 'tblUsers'." I need to a way to avoid the Microsoft statement altogether. I was trying On Error GoTo and then the If statement, but it still comes up.

What VBA coding can I use to circumvent this? Thanks for the help.

 

[VilaRestal]

I'm guessing that message happens when you close the form or move to another record. You need to validate the form in the Form_BeforeUpdate event and either Cancel = True or Me.Undo there if it's invalid.

Cancel = True will stop the form closing
Me.Undo will discard the changes

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(DLookup("UserNum", "tblUsers", "UserNum = '" & Me.UserID & "'")) Then
        MsgBox "Please input a valid User ID."
        Cancel = True
    End If
End Sub

or

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(DLookup("UserNum", "tblUsers", "UserNum = '" & Me.UserID & "'")) Then
        Me.Undo
    End If
End Sub

or give the user the choice

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(DLookup("UserNum", "tblUsers", "UserNum = '" & Me.UserID & "'")) Then
        If MsgBox("That User ID is not valid. Do you wish to correct it?", vbYesNo) = vbYes Then
            Cancel = True
            UserID.SetFocus
        Else
            Me.Undo
        End If
    End If
End Sub

 

[Heatshiver]

It's in the actual text field, but I realized, if the user is forced to use the Save button, then it will work there.

I couldn't get Me.Undo or Cancel = True in the text field VBA (for BeforeUpdate or AfterUpdate), but since the user has to use the Save button, pressing it gets it to work without the Microsoft error message.

 

[Heatshiver]

However, I just tested this when it is not blank (just a random User ID that isn't in the table).

This does bring up the Microsoft error still.

It works if it is on a now date without issue. But on a date that already has information, it will give me the Microsoft error.

I noticed that tabbing also causes this after the last tab number. If I go to the last object in the tab index and input to SetFocus on a different object from the LostFocus Event, it will avoid the issue of the Microsoft error. Because I force the user to go back through the switchboards, and forms close through those Back buttons, it does solve the Microsoft error issue from occurring.

 

[Heatshiver]

As always VilaRestal, thank you for the help! You push me to get things done!

 

[VilaRestal]

Glad you got it working (I didn't follow all that you were saying).

It's worth being aware of form's cycle property (Properties>Other>Cycle). For detail forms (rather than continuous forms or datasheets) generally set that to 'Current Record'. That way tabbing through controls won't move to a new record when it goes past the last control.

 

[RainLover]

Heatshiver

Note that VilaRestal's Code was in the Before Update Event not the After Update.

 

[Heatshiver]

Thanks VilaRestal, that's an even better solution!