maintaining record position after a requery

[tania20]

I have a subform that displays a list of records based on a filter. I have a checkbox control that when ticked, I want to requery the form to orderby this control. This all works fine, however I want some way of maintaining the focus at the position where this occured(the next record after the one that just had its box checked). I had a look at doing the following, but this goes to the next record for all and not based on the records in the applied filer. Any ideas appreciated!!

rs.FindFirst ("[Event_No] = " & Me.Event_No)
rs.MoveNext
rs.Edit
rs![ClosedTrack] = True 'set a record flag to true to search for after query
rs.Update

Me.Requery

rs.FindFirst ("[ClosedTrack]=True")
rs.Edit
rs![ClosedTrack] = False
rs.Update

 

[KenReay]

Hi

Does the underlying query (of the subform) have a unique key?

If yes, why not save the key before the requery, then position back to it, if that is it is present in the filtered result set.

I am assuming Rs is the recordsetclone of the rcordset.

So something like this

Dim lSavedKey as Long

lSavedKey = rs!lngUniqueId
'
' do the requery / filter here
rs.findfirst "lngUniqueId = " & lSavedKey
If rs.nomatch then
else
Yoursubformcontrolname.FORM.bookmark = rs.bookmark
end if

you need to use the appropriate column and control names for your app of course

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[KenReay]

Sorry

should have said

I am assuming Rs is the recordsetclone of the subform source recordset

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK