Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Access 2003 (2000 format)

    I have custom navigation buttons on a form. The BeforeUpdate for the form includes the following code:
    Code:
    If IsNull(Me.Status) Then
    Me.Status = "Active"
    
    Select Case MsgBox("     You have entered a new member." _
                       & vbCrLf & "   The Status has been set to ""Active.""" _
                       & vbCrLf & "  " _
                       & vbCrLf & "Is that the correct Status for this Member?" _
                       , vbYesNo Or vbExclamation Or vbDefaultButton1, "Status check")
        Case vbYes
            
        Case vbNo
            Cancel = True
            Me.cboStatus.SetFocus
            Exit Sub
            
    End Select
    
    End If
    
    MyExit:
       On Error GoTo 0
       Exit Sub
    
    Form_BeforeUpdate_Error:
        If Err.Number = 2105 Then
        Resume MyExit
        Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate of VBA Document Form_frmMembers"
        End If
    End Sub
    If data is entered for a new member, and a navigation button clicked...why does the error trap not work, and the message "You can't go to specified record" still occur?

    Tom

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Not sure without seeing the whole BeforeUpdate Event.

    What happens if you change the Me. to Me!

    Me!Status and Me!cboStatus are correct Access Syntax for referencing a field

    Me. is generally associated with Properties and can cause Access Confusion

    However, I tested the code with Me! and Me. against a New Record and it seems to be fine

    It Might be worth putting in a check for a New Record

    IF Me.NewRecord Then
    ........


    This way you can explicitly handle the New Record as opposed to any Update
    Andrew

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here's the entire Before Update code. I didn't include the first part in order to simplify.

    You will note that the test for a new record is included in the code. The piece I posted was in an ElseIf.

    Also, changing Me. to Me! doesn't make a difference. The "You can't go to specified record" still occurs.
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
       On Error GoTo Form_BeforeUpdate_Error
    
    If Me.NewRecord Then
    If IsNull(Me.LastName) Then
    Select Case MsgBox("No Last Name has been entered." _
                       & vbCrLf & "Member record cannot be saved without a Last Name." _
                        & vbCrLf & vbCrLf & " Do you want to continue with this record?" _
                       & vbCrLf & "" _
                       & vbCrLf & "NOTE: Clicking ""No"" will cancel the entry of this record." _
                       , vbYesNo Or vbExclamation Or vbDefaultButton1, "Last Name required")
        Case vbYes
            Cancel = True
            Me.LastName.SetFocus
            Exit Sub
        Case vbNo
            Me.Undo
            DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
            DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
            Me.Requery
            DoCmd.GoToRecord , , acLast
            
        End Select
    
    ElseIf IsNull(Me.Status) Then
    Me.Status = "Active"
    
    Select Case MsgBox("     You have entered a new member." _
                       & vbCrLf & "   The Status has been set to ""Active.""" _
                       & vbCrLf & "  " _
                       & vbCrLf & "Is that the correct Status for this Member?" _
                       , vbYesNo Or vbExclamation Or vbDefaultButton1, "Status check")
        Case vbYes
            
        Case vbNo
            Cancel = True
            Me.cboStatus.SetFocus
            
            
    End Select
    
    End If
    End If
    
    MyExit:
       On Error GoTo 0
       Exit Sub
    
    Form_BeforeUpdate_Error:
        If Err.Number = 2105 Then
        Resume MyExit
        Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate of VBA Document Form_frmMembers"
        End If
    End Sub
    Tom

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Apologies, I did not check the previous one with Custom Navigation buttons
    just the normal buttons in navigation bar.

    It looks like it is the Error Handler in the Navigation Buttons NOT your Code

    At termination of the Before Update, execution is being handed back to the
    Nav Button Error Handler and that is throwing the exception

    Add

    If Err.Number = 2105 Then Exit Sub

    To the Start of the Error Handler of your Navigation Buttons

    e.g. Assumimg it was built by the Button Wizard

    Code:
    Err_cmdNext_Click:
        If Err.Number = 2105 Then Exit Sub
        MsgBox Err.Description
        Resume Exit_cmdNext_Click
    You need the equivalent on the Others as well
    Andrew

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's got it. Thanks so much. I looked long and hard trying to figure that out.

    Greatly appreciated.

    Tom

Posting Permissions

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