Subform record selection

Topic

New Reply

Hi All,
I have a subform that has a number of records on it. I want to move to a specific record based on an input I have already collected. What is the command syntax I would use to set the focus to that record?

Thanks,
Mark

Reply | Quote

Replies

You can use code like this. I’ve assumed that the code is to be run from the main form:

Code:

Dim rst As DAO.Recordset
Set rst = Me.SubFormName.Form.RecordsetClone
rst.FindFirst "ID = " & Me.txtFindID
If rst.NoMatch Then
  Beep
Else
  ' Move to the record
  Me.SubFormName.Form.Bookmark = rst.Bookmark
  ' Set focus to the subform
  Me.SubFormName.SetFocus
  ' Optional: set focus to a specific control
  Me.SubFormName!SomeControl.SetFocus
End If
Set rst = Nothing

Notes:
– You need a reference to the Microsoft DAO 3.6 Object Library (if you’re using an .mdb database).
– Replace SubFormName with the name of your subform as a control on the main form.
– Replace SomeControl with the name of the control on the subform you want to set focus to (this is optional).
– Replace “ID = ” & Me.txtFindID with the condition you want to use.

Reply | Quote

Hi Hans,
Thanks again!

I couldn’t get the “nomatch” thing to work but the rest is working. I will play around with it later…

Thanks,
Mark

You can use code like this. I’ve assumed that the code is to be run from the main form:

Code:

Dim rst As DAO.Recordset
Set rst = Me.SubFormName.Form.RecordsetClone
rst.FindFirst "ID = " & Me.txtFindID
If rst.NoMatch Then
  Beep
Else
  ' Move to the record
  Me.SubFormName.Form.Bookmark = rst.Bookmark
  ' Set focus to the subform
  Me.SubFormName.SetFocus
  ' Optional: set focus to a specific control
  Me.SubFormName!SomeControl.SetFocus
End If
Set rst = Nothing

Notes:
– You need a reference to the Microsoft DAO 3.6 Object Library (if you’re using an .mdb database).
– Replace SubFormName with the name of your subform as a control on the main form.
– Replace SomeControl with the name of the control on the subform you want to set focus to (this is optional).
– Replace “ID = ” & Me.txtFindID with the condition you want to use.

Reply | Quote