How to keep from going to first record when requerying?

ms-access vba recordset
sigil picture sigil · Feb 10, 2012 · Viewed 11.8k times · Source

Making a form in Access 2010. I'm trying to make a button that moves to the next record (or the first if it's at the end), but because I want to account for other users' updates to the data set that have occurred in the meantime, I'm requerying the form before going to the next record.

I'm using the following code, adapted from this SO post:

Private Sub NextRec_Click()

Dim currentID As Long

currentID = Me.id.Value


'Here is where the requery brings the form back to the first record
Me.Requery


With Me.RecordsetClone
    .FindFirst "id=" & currentID
    If Not .NoMatch Then
       If Me.Dirty Then
          Me.Dirty = False
       End If
       Me.Bookmark = .Bookmark
    End If
End With

If Me.CurrentRecord < Me.Recordset.RecordCount Then
    DoCmd.GoToRecord , , acNext
Else
    DoCmd.GoToRecord , , acFirst
End If


End Sub

It's working fine, except that .requery causes the form to briefly return to the first record before going back to the current record and then on to the next record. I don't want it to do this--is there any way I can just keep the current record showing in the form while .requery takes place, instead of showing the first record for the split second while .FindFirst is looking for the record at CurrentID?

Answer

mwolfe02 picture mwolfe02 · Dec 28, 2017

Requery the recordset, not the form itself:

Me.Requery  '<-- resets the current record to the first one in the recordset
Me.Recordset.Requery  '<-- doesn't affect the current record

Related questions

How to populate a ComboBox with a Recordset using VBA

There is some literature available at expert's exchange and at teck republic about using the combobox.recordset property to populate a combobox in an Access form. These controls are usually populated with a "SELECT *" string in the 'rowsource' properties of …

Read More
Why clone an MS-Access recordset?

I'm a newbie at VBA and attempting to understand someone else's code. Set rstClone = Me.RecordsetClone rstClone.MoveFirst Why does the recordset have to be cloned? Why can't the code be Me.Recordset.MoveFirst?

Read More
insert full ADO Recordset into existing ACCESS table WITHOUT LOOP

I have a filled ADO recordset in my VBA module. I also have a table in ACCESS that has exactly the same structure as the recordset. Now I fill the table using a loop (which is fine) going through each …

Read More