Next Record when at EOF (1 Viewer)

[meanster99]

Help please guys! I have searched the forum but can't find anything specific enough for me to work with.

I have a form that displays the results of a query one at a time. The user then checks a few boxes/fills a few fields before clicking OK to move to the next record. When the user gets to the last record and clicks OK they see the standard Access msgbox stating "you can't go to the specified record". I dont want this! I know I can change the error message decription in the sub routine to say something else but I dont want to mess with this in case of other errors.

I have been looking for "If EOF then..." type of things but cant find anything except the Loop stuff, which is no good for me as i only want to advance one record when OK is pressed.

I need the user to be warned that they are at the end of the recordset and that they should then use the search box provided to enter another area to begin the process again (I know how to do the msgbox stuff).

This sounds like it should be really easy but I have used many different variations on the If EOF statement but none of them work!

Any suggestions?

 

[RuralGuy]

Maybe ghudson's code will give you some ideas. Record X of Y

 

[meanster99]

Thanks RG. I was actually looking to show the record count on the form somewhere so this will come in handy (Thanks to GHudson). However, thanks to the forum (as always!), I now know how to trap errors and so I have created a msgbox for the particular error I was getting while leaving the generic error handling in place. It wasnt exactly what I wanted but it works so it will do for now!

Err_Command130_Click:
    If Err.Number = 2105 Then
          DoCmd.Requery
          MsgBox "my new error message"
    Else
          MsgBox Err.Description
    End If
    Resume Exit_Command130_Click

If anyone has any other ideas please share them....

 

[RuralGuy]

When Me.CurrentRecord = Me.RecordSetClone.RecordCount then you are on the last record of the RecordSet.

 

[ansentry]

Have a look at my sample, I think it will do what you want.

 

[meanster99]

Thanks guys - very helpful. Its amazing how many new things I learn everyday from this site. The great thing is now I have a better way of testing for the last record and I also learnt how to trap errors. 2 for 1!

thanks again.