requery subform and find active record afterwards

[Jesper Fjølner]

(repost)
I have a subform (continuous). If say the 5th record is the current record -
How can I remember that the 5th is the current record, requery the subform
and make the 5th record the active record again? I'm trying to do it with a
bookmark, but having trouble.
Thanks.

Jesper

 

[Marshall Barton]

I have a subform (continuous). If say the 5th record is the current record -
How can I remember that the 5th is the current record, requery the subform
and make the 5th record the active record again? I'm trying to do it with a
bookmark, but having trouble.

The fifth record might not be the fifth record after the
requery.

You need to save the record's primary key value(s) before
the requery and find that record after the requery.

Dim lngKey As Long
lngKey = Me.PKfield
Me.Requery
With Me.RecordsetClone
.FindFirst "Pkfield=" & lngKey
Me.Bookmark = .Bookmark
End With

 

[Jesper Fjølner]

You need to save the record's primary key value(s) before

the requery and find that record after the requery.

Dim lngKey As Long
lngKey = Me.PKfield
Me.Requery
With Me.RecordsetClone
.FindFirst "Pkfield=" & lngKey
Me.Bookmark = .Bookmark
End With

Just what I needed! Thank you

Jesper

 

[Guest]

if i can 'jump in' here....

in my form, i want to let the user make changes in one control (On-Study
Date) which are translated into changes in another control (Future Visit). so
in my after update event property i wrote the following code:

Private Sub On_Study_Date_AfterUpdate()
Dim Future_Visit As Date
Dim On_Study_Date As Date
If Me.Dirty Then
If Not IsNull(Me.IRB_) Then
Me.Future_Visit = DLookup("FollowUp", "Query5 B", "[Forms]![Screening
Log]![F/U Status] = 'Alive'")
Me.Months = DLookup("Months", "Query5 B", "[Forms]![Screening Log]![F/U
Status] = 'Alive'")
Me.Requery
End If
End If
End Sub

the 'problem' as they say is that the requery command takes the user to the
first record in the underlying table. what i want is for this to occur as
'seamlessly' as possible and leave the user viewing the same record (s)he
just modified.

i am using a contrived a2k mdb file i threw together to test out my SQL
queries and so forth and the underlying table ("Patients on F/U") has a PK
which is text called "IRB" (in the full blown one, i doubt i can get by with
just one and will use a composite PK consisting of some text and number
fields, but anyway...), when i tried working your code into my own, the l/u
table did not resolve to the "PKfield"...so, can you please tell me what i am
doing wrong?

thanks for any help in advance,

-ted