Sub Form - "Continuous Forms" Mode

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Worked like a charm! Thanks Ken

Ken Sheridan said:
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
Click to expand...
 
Back
Top