Navigate Record using variable under BeforeUpdate

[Daiuy]

I'm creating an application that controls visitor's badges.
I want to ensure that one badge can not be issued to more than one
individual at a time.
I have the application looking for possible duplications in the BadgeNumber
field.
I envision the security person having the visitor's ID and the visitor's
badge on their desk while they're inputing the data, so the highest
probability of a duplicate occuring is probably because that visitor's badge
was not logged back in by an entry in the Badge Returned Date field.
I have a message box that presents itself should a duplicate be found.
I'd like to give the user the option of immediately going to that record to
edit it.
I can't seem to navigate in BeforeUpdate
Here's my code:

Private Sub BadgeNumber_BeforeUpdate(Cancel As Integer)

Dim StrWhere As String
Dim varResult As Variant
Dim IntAnswer As Integer ''

With Me.BadgeNumber
If (.Value = .OldValue) Then
'do nothing
Else
StrWhere = "BadgeNumber = """ & .Value & """"
varResult = DLookup("ID", "tblAccessControl", StrWhere)
If Not IsNull(varResult) Then
IntAnswer = MsgBox("Would you like to go to that record
now?", vbExclamation + vbYesNo, "Duplicate Badge Number with Record " &
varResult)
If IntAnswer = vbYes Then

Else:
'do nothing
End If
Me.BadgeNumber.Undo
Cancel = True
End If
End If
End With
End Sub

 

[Allen Browne]

You cannot use the BeforeUpdate event of a bound control to move record,
because that event must complete before the current changes can be handled,
and those changes must complete before you can move record.

You could use the AfterUpdate event of the control, and Undo the form if you
need to move record.

 

[Daiuy]

OK, I cut and pasted the code from BeforeUpdate to AfterUpdate.
Needless to say, it doesn't work anymore.
The ID field is the primary key. I get the ACCESS generated message about
not allowing duplicates, the BadgeNumber field does not UNDO, and the cursor
moves to the next field because Cancel = True does not work here either.
So, I think we'll have to start from scratch. Any ideas?

 

[Allen Browne]

Yes, you will have to take a different approach, and change your code.

The AfterUpdate event does not have a Cancel argument, so drop that line.
You can undo the form with:
Me.Undo