Access Question

DannyBuoy

Hi all, I have a problem with a form in Access that looks back to a table, the form has various fields from table, and I've created 2 buttons for next record and previous record because they look tidier than the ones supplied be Access at the bottom of the form.
My problem arises when someone clicks on my 'Next Record' button and tries to goto the next record but they're at the last record, naturally it throws an error. Ideally I'd like the button to check if the current record is the last record in the table and then just throw up a message box. Its the checking the current record I have the problem with!
Obviously I'm not a programmer so any help would be appreciated, thanks.

Delirium

DannyBuoy wrote: »

Hi all, I have a problem with a form in Access that looks back to a table, the form has various fields from table, and I've created 2 buttons for next record and previous record because they look tidier than the ones supplied be Access at the bottom of the form.
My problem arises when someone clicks on my 'Next Record' button and tries to goto the next record but they're at the last record, naturally it throws an error. Ideally I'd like the button to check if the current record is the last record in the table and then just throw up a message box. Its the checking the current record I have the problem with!
Obviously I'm not a programmer so any help would be appreciated, thanks.

You could create a recordset variable containing a copy of the recordset and then check to see if it is the last record.

For example:

Set rst = me.RecordsetClone
 
if rst.eof then
  'msgbox to indicate user is on last record
else
  'run the move to next record code
endif

DannyBuoy

Thanks koth, I think that's something along the lines of what I need, it still seems to open a new record tho, possibly because I have some default values that are automatically put into a new record. I had a thought about something along the lines of if the record id number is the max id number then msg no more records. That could be put on the code of the button. Just have to do some more digging, thanks.

cracker

As a work around you could add an error handler and put an IF condition to do nothing when you get that particular error

DannyBuoy

Good point, but I still have a form with a blank record then tho. And my problem here is that a record must have certain fields filled in before it can be saved, so therefore it throws an error for that then when you try to close it!
What I need to figure out is how to stop my next record button working if the form is on the last record, maybe I cant, but it would be a nice touch in my database.

cracker

I put "DoCmd.GoToRecord , , acNext" under the click event and then had an error handler of

cmdClick_Exit:
    Exit Sub


cmdClick_Error:
If err.Number <> 2105 Then
        MsgBox "show error"
    End If
Resume cmdClick_Exit

in the error handler
This seemed to work fine for me and displayed any error that was not due to EOF.

DannyBuoy

Cheers cracker, I'll try that tonight.

DannyBuoy

Folks, pints are on me. Mixing both solutions gives me exactly what I need.

Thanks