Validate Form

[scott]

I'm trying to validate a control before a user moves to a new record.
Basically, I want to test if myControl is null or equals zero. If either
conditions are true, I want to display my msgbox and if the user chooses No
(correct the problem), set focus to the control and prevent the error "'You
can't go to the specified record" from popping up.

My Form_BeforeUpdate code below works fine if the control is null or equals
zero and the user tries to go to the previous record. However, if the null
or zero conditions exist and the user attempts to move to a new record, gets
the msgbox warning and chooses No to correct the problem, I get the error
"'You can't go to the specified record" error.

I've read a lot of posts on the subject but can't seem to prevent the error
"'You can't go to the specified record" when moving to a new record.

Can someone help me modify my code below?


CODE ***********************

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Response As String
Dim Title As String, Style As Integer
Title = CurrentDb().Properties("AppTitle")
Style = vbYesNo + vbExclamation

If Me.Dirty Then
On Error GoTo ErrorTrap
If IsNothing(Me!myControl) Then

Msg = "Are you sure you want to leave this value blank?"
Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then

Else
Me!myControl.SetFocus
DoCmd.CancelEvent
Exit Sub
End If
ElseIf Nz(Me!myControl) = 0 Then

Msg = "Are you sure you want to leave this value equal to zero?"
Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then

Else
Me!myControl.SetFocus
Exit Sub
End If
End If

End If

CancelAdvance:
Exit Sub
ErrorTrap:
'ignore error # 2105 - 'You can't go to the specified record'
If Err.Number <> 2105 Then
'MsgBox Err.Number & " - " & Err.Description
End If
Resume CancelAdvance
End Sub

 

[Allen Browne]

Scott, how are you trying to move record?
Do you have other buttons with code in them that move record?

If so, you need to add an error handlet to *that* code that traps for error
2105.

Handling that error in Form_BeforeUpdate won't work, since the error is not
occurring in that routine.

 

[scott]

What I don't understand is I've read where BeforeUpdate happens before the
current record is modified and saved, but the
BeforeInsert fires before adding a new record.

If and when I finally get my code right that will suppress the message, will
I then need to put the same code in both of these events.

Do you think testing for the action of adding a new record should be in the
code? Possibly something like putting the code below to traap the error
warning when the command button for next record happens to be a new record?
It seems no matter what I do, when the next button is clicked, both going to
a new record or just going forward raises the error. Going to the previous
record suppresses the error.

If (Not Screen.ActiveForm.NewRecord) Then

 

[scott]

I got it. It was my Click event on the next button that was causing the
error.

thanks.