Scrolling records - move to first after acLast

[mirirom]

hi,

i have buttons that allow users to scroll indefinitely through a form's recordset. it's set up so that when BOF is reached, the last record is opened; conversly, when EOF is reached, the first record should be opened.

the BOF check on movePrevious works fine. unfortunately i can't seem to get the moveNext EOF to work. basically i'm detecting if a new record is being created (EOF has been passed at this point). if so, move first. the exception thrown is 2105: Can't move to the specified record.

here's the code.

Private Sub cmdMoveNext_Click()
On Error GoTo Err_cmdMoveNext_Click

    DoCmd.GoToRecord , , acNext
   
    If Me.NewRecord Then
      DoCmd.GoToRecord , , acFirst
    End If

Exit_cmdMoveNext_Click:
    Exit Sub

Err_cmdMoveNext_Click:
    msgbox Err.Number & vbCrLf & Err.Description
    Resume Exit_cmdMoveNext_Click
    
End Sub

any help is greatly appreciated. thanks!!

..:: mirirom ::..

 

[GoDawgs]

I don't think the new record that you get to from scrolling to the end is actually included in the recordset, that's why you're getting the "Can't move" error. Are the users allowed to put in new records or just scroll through? If they can only scroll then you can just make the form not allow new entries (one of the form's properties). That's probably not the case though...maybe try something like this:

If Me.NewRecord Then
me.recordsetclone.movefirst
me.bookmark=me.recordsetclone.bookmark
End If

I'm not even sure if that will work, but it's a new way to go at least. Hope it helps.

Kevin

 

[mirirom]

Kevin,

thanks! your snippet works fine, and your assumption is correct: the users can enter new records. basically i've created a custom toolbar to control the state machine (rather than relying on the default nav control).

i'm curious about RecordsetClone though. is this in fact a *copy* of [an] underlying recordset or does it act as a pointer? one thing that's always mystified me is how acting upon the clone calls back to the orginal. this infers that its really a reference - yet online documentation states that you're making a copy (doesn't specify deep or shallow).

thanks again for your help. works beautifully.

..:: mirirom ::..

 

[GoDawgs]

You know I've never actually thought that hard about the recordset clone...I've just always thought of it as a copy that's updated every time something is changed which allows you to jump around the recordset without actually leaving the record you are working on. I may be wrong, but it works in my head. Glad I could help.