"No Record Selected" after Recordset.Requery

[kdbailey]

I have a situation where a checkbox is changing the underlying recordset. I'm using a rs.requery statement. If I go to hit the checkbox once again, without selecting a textbox or something else within the record, I receive an error for no record being selected.



How do I refresh the recordset and then make sure the same record is the selected record after the requery?

[June7]

Use Bookmark.
Bing: access vba refresh return to record
Review
https://access-programmers.co.uk/for...d.php?t=153989
https://bytes.com/topic/access/answe...-return-record

[pbaldy]

One way:

http://www.baldyweb.com/Requery.htm

[kdbailey]

Can that be adapted to a recordset.requery? That method is requerying the form.

[June7]

Requerying form is requerying the recordset.

I am not familiar with Recordset Requery method.

Post your code.

[kdbailey]

In my experience, doing a form.requery also drives this error (de-selecting all records), unless you use a form.move statement. Going this route forces a continuous form back up to the top, and it causes a visual nightmare if you're on a long list. I found that the recordset.requery does indeed refresh the data, and does the same thing the form.requery does (de-selects records). However, it doesn't force it's way to the top of the list. I.e. the cursor stays on the current record.

Dim rs As Recordset
Set rs = Me.Recordset
'currpos = rs.AbsolutePosition

DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunSQL "UPDATE *********;"
rs.Requery

'rs.MoveFirst
'rs.Move currpos
End Sub

[kdbailey]

I actually was able to use the findfirst method without using a recordsetclone. My dataset includes an ID field, unique of course. I adjusted the code to read...

Dim rs As Recordset
Set rs = Me.Recordset
with rs
   DoCmd.RunCommand acCmdSaveRecord

   taskid = .fields("Task ID")
   DoCmd.RunSQL "UPDATE *********;"

   .Requery
   .FindFirst "[Task ID]=" & taskid
End With
End Sub

Works perfect.