next buton NOT NEW

[Guest]

I have a navigation button for next. I DON'T want it to go past the last
record to a NEW record but I don't know how to keep it from doing that and I
want the msg to read "you are already at the last record." I tried a couple
of different options but they didn't work so now I am asking the EXPERTS!
I do have a new record button.
THANKS!

Private Sub cmdNextRec_Click()
On Error GoTo Err_cmdNext_Click
DoCmd.GoToRecord , , acNext
Exit_cmdNext_Click:
Exit Sub
Err_cmdNext_Click:
MsgBox err.Description
Resume Exit_cmdNext_Click

End Sub

 

[Douglas J. Steele]

Check the sample database Stephen Lebans has at
http://www.lebans.com/recnavbuttons.htm

 

[Jerry Porter]

A clean way would be to set the Allow Additions property of the form to
No. If this is all you did, a user trying to go beyond the last record
would get the message "You can't go to the specified record." There are
ways to modify that message.

Another way would be to add to your existing code:

DoCmd.GoToRecord , , acNext
If Me.NewRecord Then
MsgBox "You are already at the last record.", vbInformation,
"End of data"
DoCmd.GoToRecord , , acLast
End If

This checks whether the record is a New record, and if so, goes back to
the last record.

Jerry

 

[BruceM]

Another option is to disable the buttons when there are no records. In the
form's Current event:

Dim strCurrent as String, strTotal as String

'Inserts current record number and total number of records
strCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
strTotal = Me.RecordsetClone.RecordCount

' Place the record count into an unbound text box on the form
Me.txtCounter = strCurrent & " of " & strTotal

'Enable navigation buttons only when there are records available
Me.cmdPrev.Enabled = Not Me.CurrentRecord = 1
Me.cmdFirst.Enabled = Not Me.CurrentRecord = 1
Me.cmdNext.Enabled = (Me.CurrentRecord = 1 And Me.Recordset.RecordCount

1) _

Or Me.CurrentRecord < Me.Recordset.RecordCount
Me.cmdLast.Enabled = Not (Me.cmdNext.Enabled = False)

This assumes an unbound text box named txtCounter, and command buttons named
cmdPrev, etc.

 

[Guest]

Thanks I tried your suggestion but it still went to a new record with a msg box

so I have done the following (I don't want to set the form to no entry at
this point)
But I still don't have the msg box figured out...

Private Sub cmdNextRec_Click()
On Error GoTo Err_cmdNext_Click
Dim frm As Form
Set frm = Screen.ActiveForm

If frm.CurrentRecord < frm.RecordsetClone.RecordCount Then
DoCmd.GoToRecord , , acNext
End If
Exit_cmdNext_Click:
Exit Sub

Err_cmdNext_Click:
MsgBox err.Description
Resume Exit_cmdNext_Click

End Sub

 

[Jerry Porter]

Add the Else and MsgBox lines to your current code:

If frm.CurrentRecord < frm.RecordsetClone.RecordCount Then
DoCmd.GoToRecord , , acNext
Else
MsgBox "You are already at the last record."
End If

 

[Guest]

THANKS!