Go To Command

  • Thread starter Thread starter Roger Bell
  • Start date Start date
R

Roger Bell

I have a command button on a form, ( On Click Event Procedure), which when
clicked, will take you to the next record as follows:

Private Sub Next_Envelope1_Click()
On Error GoTo Err_Next_Envelope1_Click
DoCmd.GoToRecord , , acNext
Exit_Next_Envelope1_Click:
Me.W1.SetFocus
Exit Sub

Err_Next_Envelope1_Click:
MsgBox Err.Description
Resume Exit_Next_Envelope1_Click

End Sub

I would like to add a clause as follows:

Where Envelope Number (field name) >0

Could someone tell me where the clause should be inserted and the correct
syntax?

Thanks for any help
 
I have a command button on a form, ( On Click Event Procedure), which when
clicked, will take you to the next record as follows:

Private Sub Next_Envelope1_Click()
On Error GoTo Err_Next_Envelope1_Click
DoCmd.GoToRecord , , acNext
Exit_Next_Envelope1_Click:
Me.W1.SetFocus
Exit Sub

Err_Next_Envelope1_Click:
MsgBox Err.Description
Resume Exit_Next_Envelope1_Click

End Sub

I would like to add a clause as follows:

Where Envelope Number (field name) >0

Could someone tell me where the clause should be inserted and the correct
syntax?

Thanks for any help
Click to expand...

Since nobody here has any idea what or where an "envelope number" is, or what
you mean by "field name", or what you want to happen if (whatever it is) isn't
greater than zero... no.

Give us a little context please?
 
Sorry John for my lack of information.
When the command button is clicked, it takes the user to the next record.
Envelope Number is a field on the form and contains numbers. If this Envelope
Number is Blank, I would like this record to be skipped: Reason for greater
than zero.

Hope I have explained a little better: I am still a novice and appreciate
any assistance.

Roger
 
Sorry John for my lack of information.
When the command button is clicked, it takes the user to the next record.
Envelope Number is a field on the form and contains numbers. If this Envelope
Number is Blank, I would like this record to be skipped: Reason for greater
than zero.
Click to expand...

Still trying to follow here: what do you want "skipped"? Do you not want to
move to the new record in this case, or skip the next record, or what?
 
Roger:

Call the procedure recursively if the envelope number is Null or zero:

Private Sub Next_Envelope1_Click()

On Error GoTo Err_Next_Envelope1_Click
DoCmd.GoToRecord , , acNext
If Nz(Me.[Envelope Number],0) = 0 Then
Next_Envelope1_Click
End If

Exit_Next_Envelope1_Click:
Me.W1.SetFocus
Exit Sub

Err_Next_Envelope1_Click:
MsgBox Err.Description

End Sub

Ken Sheridan
Stafford, England
 
I don't think so. An error would be raised by the GoToRecord line when it
tries to move beyond the last record and code execution would branch to the
error handler. It would of course be better to handle the anticipated error
specifically rather than just the generic error handling which the wizard
generates:

Err_Next_Envelope1_Click:
Select Case Err.Number
Case 2105
' no more records so do nothing
Case Else
MsgBox Err.Rescription, vbExclamation, "Error"
End Select
Resume Exit_Next_Envelope1_Click

Ken Sheridan
Stafford, England
 
Another thought; rather than calling the procedure recursively, it would be
more efficient to just loop until either the next non-null_or_zero is
encountered:

Do
DoCmd.GoToRecord , , acNext
If Nz(Me.[Envelope Number], 0) <> 0 Then
Exit Do
End If
Loop

Don't know why I didn't think of that first. Trying to be too clever, I
guess!

The error handler would be as before to trap the 2105 error.

Ken Sheridan
Stafford, England
 
Thanks for all your help Ken,

Have the procedure as listed below, but getting a Compile Error 450, with
the Nz highlighted. "Wrong number of arguments or invalid property assigned"

Any suggestions?

Private Sub Next_Envelope1_Click()
On Error GoTo Err_Next_Envelope1_Click
Do
DoCmd.GoToRecord , , acNext
If Nz(Me, [Envelope Number], 0) <> 0 Then
Exit Do
End If
Loop
Exit_Next_Envelope1_Click:
Me.W1.SetFocus
Exit Sub

Err_Next_Envelope1_Click:
MsgBox Err.Description
Resume Exit_Next_Envelope1_Click

End Sub
 
Back
Top