You can't go to specified record

[THWatson]

Using Access 2003 (2000 format)
I have custom navigation buttons on a form. The BeforeUpdate for the form includes the following code:

If IsNull(Me.Status) Then
Me.Status = "Active"
Me.cboStatus.SetFocus

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

 

[dhookom]

I would try to remove one of the

   Me.cboStatus.SetFocus

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Interesting.

I agree that there's little benefit in having the first Me.cboStatus.SetFocus there. So, if I remove the first and click "Yes" things work properly.

However, the error message still occurs when I click "No". The focus sets to Me.cboStatus following the message but the message is annoying.

Maybe it has to do with the custom navigation buttons.

Tom

 

[dhookom]

Agreed on the custom buttons. You might want to disable them unless the status has been entered.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Hmmm, not sure that will work. The purpose of the code is this...

Unless the Status is filled in when the user enters a new record, the record will never show up in the Current Members list, because the code on the Current event brings in only the Active and Senior members (disregarding Deceased, Transferred Out, and Resigned which are shown by clicking a button).

When a new member is added, the Status has to be either Active or Senior. In the vast majority of instances, the Status will be Active. This is why I am setting it that way, but giving the user the option of changing that to Senior if he wishes.

So disabling custom navigation buttons UNTIL the Status is entered doesn't do the trick because it is auto-filled.

Tom

 

[dhookom]

But status is "auto-filled" with the code:

  Me.Status = "Active"

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Right.

I suppose I could stop the auto-fill and make the user fill in the Status if it were blank. Seemed to me that since 98% of any new members entered would be with Status as Active it made sense to auto-fill it with that. Prior to doing so, the user would periodically fail to enter a Status, so the record didn't show up.

I'm still thinking through your response.

Tom

 

[THWatson]

Duane
I found the answer.

The problem is not with my code on the Before Update event.

I had to add

If Err.Number = 2105 Then
    Exit Sub
    Else

To each of the custom navigation buttons.

Tom