Go To Command

[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

 

[John W. Vinson]

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

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?

 

[Roger Bell]

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

 

[John W. Vinson]

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.

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?

 

[Ken Sheridan]

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

 

[John W. Vinson]

Roger:

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

Won't that cause an infinite loop when it gets to the (blank) New Record?

 

[Linq Adams via AccessMonster.com]

How about:

Private Sub Next_Envelope1_Click()
On Error GoTo Err_Next_Envelope1_Click
Dim RecordsLeft As Integer
RecordsLeft = (RecordsetClone.RecordCount - CurrentRecord) - 1

If CurrentRecord = RecordsetClone.RecordCount Then
MsgBox "There are no more records!"
Exit Sub
End If

DoCmd.GoToRecord , , acNext
If IsNull(Me.EnvelopeNumber) And CurrentRecord = RecordsetClone.
RecordCount Then
MsgBox "There are no more qualifying Records!"
Exit Sub
End If
For i = 1 To RecordsLeft
If IsNull(Me.EnvelopeNumber) Then

DoCmd.GoToRecord , , acNext
If CurrentRecord = RecordsetClone.RecordCount Then
MsgBox "There are no more records!"
End If
Else
Exit Sub
End If
Next i

Exit_Next_Envelope1_Click:
Me.W1.SetFocus
Exit Sub

Err_Next_Envelope1_Click:
MsgBox Err.Description
Resume Exit_Next_Envelope1_Click

End Sub

 

[Ken Sheridan]

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

 

[Ken Sheridan]

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

 

[Roger Bell]

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

 

[John W. Vinson]

If Nz(Me, [Envelope Number], 0) <> 0 Then

That should be

If Nz(Me.[Envelope Number], 0) <> 0 Then

 

[Linq Adams via AccessMonster.com]

I don't think so. An error would be raised by the GoToRecord line when it

tries to move beyond the last record

If CurrentRecord = RecordsetClone.RecordCount Then
MsgBox "There are no more records!"
Exit Sub
End If

prevents this, in my testing.