Unanswered: Trapping error 3022 in order to automatically navigate to the pre-existing record
I've hit a snag. I have a surrogate primary key - a unique ID that is input by the user. Frequently people will enter an ID in order to fill out a different part of my form, and they will have no idea if the record exists yet or not.
I need to trap error 3022 and automatically navigate to the pre-existing record. How can I go about doing this?
On Error GoTo ErrorHandler
If err.number = 3022 Then
MsgBox Err.Number & ": " & Err.Description
I've tried using DoCmd.FindRecord, but there's a new problem.
I can navigate to the new record easily enough, but access wants to save the original record, so I need a way to delete the record in which all the user does is type in the UID. Seems simple, but using the DoCmd menuitems to delete screws up the DoCmd.FindRecord
The IDs are of lab rats - they are assigned far before anything is entered in to my database.
Here's the rather obfuscated code that ended up working!
dim butt As Long
Private Sub Mouse_UID_Exit(Cancel As Integer)
On Error GoTo UIDAlreadyExist
If Err.Number = 3022 Then
Dim Answer As String
Dim MyNote As String
MyNote = "Record for Mouse UID " & [Mouse UID] & " already exists. Navigate to this record? WARNING: Any data currently filled out in this form will be lost!"
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Navigate to pre-existing record?")
If Answer = vbNo Then
butt = Me.Mouse_UID.Value
[Mouse UID] = -1
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.GoToRecord , , acFirst
DoCmd.GoToControl "Mouse UID"