Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2008
    Posts
    22

    Unanswered: Trapping error 3022 in order to automatically navigate to the pre-existing record

    Howdy all,



    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?



    Thanks in advance!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    On Error GoTo ErrorHandler
    
        <do stuff>
    
    ExitHandler:
        Exit Sub
    
    ErrorHandler:
        If err.number = 3022 Then
            <error trapped>
            Resume Next
        Else
            MsgBox Err.Number & ": " & Err.Description
            Resume ExitHandler
        End If
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2008
    Posts
    22
    Beautiful, now all I've got to do is find the syntax for navigating to a record based on its primary key!

    Thanks so much!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    DLookup perhaps?
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2008
    Posts
    22
    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

    Fun times!

  6. #6
    Join Date
    Jun 2008
    Posts
    22
    the runtime error i get is 2162

    something screwy about findrecord, seems you cant delete a record and then run it

    welp, i'm pretty much stumped now

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why do you make your users enter the surrogate key? Would it not be more appropriate to use an AutoNumber - would it solve some of these issues you are coming across?
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2008
    Posts
    22
    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!

    Code:
    dim butt As Long
    
    Private Sub Mouse_UID_Exit(Cancel As Integer)
        On Error GoTo UIDAlreadyExist
    
    'blablabla
    
        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
                Exit Sub
            Else
                
                butt = Me.Mouse_UID.Value
                [Mouse UID] = -1
                DoCmd.SetWarnings False
                DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
                DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
                DoCmd.SetWarnings True
               
                
                DoCmd.GoToRecord , , acFirst
                DoCmd.GoToControl "Mouse UID"
                Me.Refresh
                DoCmd.FindRecord (butt)
            End If
          
            Exit Sub
        Else
        MsgBox Err.Description
        Resume Exit_CmdRefresh_Click
        End If
        
        
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •