Disconnect subform requery, or scroll a subform to a specific record more cleanly?

[Max Moor]

Hi All,
I have a subform (Access 2002) I want to scroll to a specific record.
Why is the rub...

The selected subform record is the "filter" for the mainform. So, I
requery the mainform from the OnCurrent event of the subform. Of course,
this causes the subform to be requeried, resetting the record pointer.
Therefore, I use a little logic in the OnCurrent event, so I don't end up
in an endless loop of requeries (oh, yes I did), and store the selected
record so I can scroll to it after the requery is done. I swear it's not
as messy as it sounds.

In the past, to scroll, I've used code like this:

Public Sub ScrollToRecord(lngRecordID As Long)
Dim rstMyRecords As Object


Set rstMyRecords = Me.Recordset

If (Not rstModules.EOF) Then
rstMyRecords .MoveFirst
rstMyRecords .FindFirst "[RecordID] = " & lngRecordID
End If
End Sub

Unfortunately, with the requery happening between the selection of the
record and the scrolling, the result is that the location of the selected
record "jumps" to a new location from where it was when the user selected
it. User interface wise, it's just awful.

Ultimately, I'd like to be able to disconnect the subform from being
requeried when the main form is requeried. Is there a way?

Second to that, does anyone have any thoughts on how I can do the
scroll more cleanly?

Thanks, Max