Stop moving passed last record

[Rocinante]

I am writing a database with a number of tables with forms attached to each table. I have placed navigation buttons on each form to move to first, last, next, previous. I have set up the code associated with the buttons as "public sub" so it is available for each form to use.

This was working OK except that when I used the NEXT button at the last record it would open a new blank record & keep opening a new blank record on each click.

To over overcome this I put in an If statement to check if it had moved to a new record. To do this I used the following:

If Me!NewRecord Then ..... etc

Now I get an error "Invalid use of Me key word" I suspect it is because I am using ME in a public sub

How do I overcome the problem of moving passed the last record and still have the coding available to all forms?


The Code is below.

Thanks for any help.



Public Sub Next_Record_Click()
On Error GoTo Err_Next_Record_Click

DoCmd.GoToRecord , , acNext

If Me!NewRecord Then
' If new record move back to previous
DoCmd.GoToRecord , , acNext
' Send message
MsgBox "This is the last record", , "No More Records"

End If


Exit_Next_Record_Click:
Exit Sub

Err_Next_Record_Click:
MsgBox Err.Description
Resume Exit_Next_Record_Click

End Sub

 

[Pat Hartman]

Pass the form name to the procedure and use that instead of Me.

Call Next_Record_Click(Me)

Public Sub Next_Record_Click(frm as Form)
On Error GoTo Err_Next_Record_Click

DoCmd.GoToRecord , , acNext

If frm.NewRecord Then
' If new record move back to previous
DoCmd.GoToRecord , , acNext
' Send message
MsgBox "This is the last record", , "No More Records"

End If


Exit_Next_Record_Click:
Exit Sub

Err_Next_Record_Click:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_Next_Record_Click

End Sub

 

[Rocinante]

Thanks Pat
I tried the adaption and it got rid of the ME problem, thnaks. (Note i corrected my posting error and replaced acNext with ac Previous in the If .. End If code)

However it still causes a problem with going past the last record.

If the last record is, say, no 17. on clicking NEXT the code goes to next blank record and created no 18. The checking of new record then takes it back to 17.

However on the next click of the next button it moves to the newly created no 18 which it appears to see now as the last record. The next click created 19 and steps back to 18 and so on.

It seems that the code ..... DoCmd.GoToRecord , , acNext
will automatically go passed the last record and create a new blank record .. which clearly I don't want.

Am I using the DoCmd.GotToRecord correctly?? What am I missing as this seems strange to a newbie like me.

Thanks Stephen

 

[Rocinante]

I have found a solution building on Pat's suggestion. I simply count the number of records and compare the total to thecurrent record number. If they are the same I do not execute the NEXT command. The code is below. I am sure there must be an easier way. Thanks Pat for the assist.


Public Sub Next_Record_Click(frm As Form, tblName As String)
On Error GoTo Err_Next_Record_Click

Dim TotRecords As Long
Dim CurrentRec As Long


TotRecords = DCount("*", [tblName])
CurrentRec = frm.CurrentRecord


If CurrentRec = TotRecords Then

' Send message
MsgBox "This is the last record", , "No More Records"

'If last record exit sub without moving
Exit Sub

End If

' If not last record move to next
DoCmd.GoToRecord , , acNext


Exit_Next_Record_Click:
Exit Sub

Err_Next_Record_Click:
MsgBox Err.Description
Resume Exit_Next_Record_Click

End Sub