Sub Form - "Continuous Forms" Mode

[Guest]

Hi,
I have a subform in "Continuous Forms" mode. When I double click a row I
bring up a form to modify the record. When I close this form I requery the
subform so any changes are refreshed, but then I want the focus to go back
the row that was initally being modified. How can I do this? By default the
focus goes back up to the first record in the sub form.

Thanks,
Ken

 

[Guest]

Ken:

Before requerying the subform find the row in a clone of the subform's
recordset and synchronise the subform's bookmark with that of the clone.
Something like this for the double click event procedure:

Dim rst As Object
Dim strCriteria as String

strCriteria = "MyID = " & Me.MyID
DoCmd.OpenForm "YourOtherForm", _
WhereCondition:=strCriteria, _
WindowMode:=acDialog

Me.Requery

Set rst = Me.Recordset.Clone

With rst
.FindFirst strCriteria
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Opening the other form in dialogue mode causes the code execution to pause
until the other form is closed (or hidden), at which time it will be
requeried and the bookmarks synchronised.

If you are only modifying the current record, however, and not inserting or
deleting any rows into the table, you should be able to get away with simply
calling the Refresh method of the subform rather than requerying it. Unlike
requerying, refreshing does not move the record pointer, so you should stay
at the current record.

Ken Sheridan
Stafford, England

 

[Guest]

Worked like a charm! Thanks Ken

Ken:

Before requerying the subform find the row in a clone of the subform's
recordset and synchronise the subform's bookmark with that of the clone.
Something like this for the double click event procedure:

Dim rst As Object
Dim strCriteria as String

strCriteria = "MyID = " & Me.MyID
DoCmd.OpenForm "YourOtherForm", _
WhereCondition:=strCriteria, _
WindowMode:=acDialog

Me.Requery

Set rst = Me.Recordset.Clone

With rst
.FindFirst strCriteria
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Opening the other form in dialogue mode causes the code execution to pause
until the other form is closed (or hidden), at which time it will be
requeried and the bookmarks synchronised.

If you are only modifying the current record, however, and not inserting or
deleting any rows into the table, you should be able to get away with simply
calling the Refresh method of the subform rather than requerying it. Unlike
requerying, refreshing does not move the record pointer, so you should stay
at the current record.

Ken Sheridan
Stafford, England