Bookmarking Record & Field

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

After pressing a command button on a main form and
finishing its event procedure, I'd like to return the user
to where he was in the subform before pressing the
button. Can anyone tell me how?

TIA
Kevin Sprinkel
 
Unless your code moves to a different record in the main form or requeries
the subform, you should be able to just set focus to the subform control:
Me.[NameOfYourSubformControlHere].SetFocus

If you do something that changes the source of the subform, you will need to
save the primary key value from the subform's record into a variable, and
then FindFirst in its RecordsetClone afterwards.
 
Allen,

Thank you for your response.

My code does a requery, so the SetFocus method is not
adequate. I implemented the FindFirst method; the
compiler says "Method or data member not found." on the
line:

rst.FindFirst "TakeoffID = '" & intID & "'"

The following references are currently established:

Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library
Microsoft Office 10.0 Object Library

Do you know which library I need to add for this method?

Thank you.
Kevin Sprinkel
 
Assuming this data is in Access tables, you need to DAO library.

This example saves the primary key value from the subform in to variable
named "varID". We use a Variant in case the primary key is null (which it
would be at a new record).

After the Requery, it finds the record again in the subform's clone set.
Note that if TakeOffID is a Text type field (not a Number type field), you
need extra quotes, i.e.:
rs.FindFirst "TakeOffID = """ & varID & """"


Dim frm As Form
Dim rs As DAO.Recordset
Dim varID as Variant

Set frm = Me.[NameOfYourSubformContorlHere].Form
varID = frm!TakeoffID

'other code here.
frm.Requery

If IsNull(varID) 'Must have been a new record
If Not frm.NewRecord Then
Me.[NameOfYourSubformContorlHere].SetFocus
RunCommand acCmdRecordsGotoNew
End If
Else
Set rs = frm.RecordsetClone
rs.FindFirst "TakeOffID = " & varID
If rs.NoMatch Then
Beep
Else
frm.Bookmark = rs.Bookmark
End If

Set rs = Nothing
Set frm = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kevin Sprinkel said:
Thank you for your response.

My code does a requery, so the SetFocus method is not
adequate. I implemented the FindFirst method; the
compiler says "Method or data member not found." on the
line:

rst.FindFirst "TakeoffID = '" & intID & "'"

The following references are currently established:

Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library
Microsoft Office 10.0 Object Library

Do you know which library I need to add for this method?

Thank you.
Kevin Sprinkel
-----Original Message-----
Unless your code moves to a different record in the main form or requeries
the subform, you should be able to just set focus to the subform control:
Me.[NameOfYourSubformControlHere].SetFocus

If you do something that changes the source of the subform, you will need to
save the primary key value from the subform's record into a variable, and
then FindFirst in its RecordsetClone afterwards.
Click to expand...
Click to expand...
 
Thanks, Allen. However, although it goes to the correct
record, the cursor is not in the subform. I added

Dim ctl As Control
Set ctl = Screen.PreviousControl
..... do stuff...
After the bookmark..
ctl.SetFocus
....
Set ctl = Nothing

This returns the cursor to the subform, but goes to the
first control regardless of where the user was, presumably
because ctl is pointing merely to the subform control of
the main form and not the control on the subform.

If you have any thoughts on how to go back to the subform
control itself, I'd appreciate hearing about it.
Additionally, on return, the continuous form scrolls up,
such that the bookmarked record displays as the first
record in the continuous form display. This is a little
unsettling; do you know how to avoid it?

Thank you again for your help.

Kevin Sprinkel
 
Try setting focus to a particular field in the subform, as well as to the
subform control:

Me.[NameOfYourSubformContorlHere].SetFocus
Me.[NameOfYourSubformContorlHere].Form!TakeOffID.SetFocus
 
Thank you, Allen. I would like to use this strategy, but
would rather return to the control the user was in, rather
than arbitrarily choosing for him, i.e., bookmark his
subform control. Is this possible?

Best regards.
 
Selecting the subform control should have done that. You could experiment
with Screen.ActiveControl or Screen.PreviousControl.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kevin Sprinkel said:
Thank you, Allen. I would like to use this strategy, but
would rather return to the control the user was in, rather
than arbitrarily choosing for him, i.e., bookmark his
subform control. Is this possible?

Best regards.
-----Original Message-----
Try setting focus to a particular field in the subform, as well as to the
subform control:

Me.[NameOfYourSubformContorlHere].SetFocus
Me.[NameOfYourSubformContorlHere].Form!TakeOffID.SetFocus
Click to expand...
Click to expand...
 
Back
Top