Simon wrote:
Dear reader,
How can I find a record in a sub form, the sub form type is DataSheet and
the record collection in the subform are to many to show them all in the
subform window.
What I want is to show a specific record in the subform together with the
other collection of records in the subform. Now I have to scroll to that
record with the vertical scrollbar.
Is there any alternative for the Docmd.FindRecord ..... because this DoCmd
is not working in the subform or I use it on the wrong way.
Tanks for any help.
Kind regards,
Simon
You could add a text field, FindRec, on the main form named MainForm to
collect the search value in subform name SubForm. When found, go to
that record.
Let's say you are going to search for OrdDate in the subform. In the
AfterUpdate event of FindRec have some code like the following.
Dim rst As Recordset
If Not IsNull(Me.FindRec) Then
Set rst = Forms!MainForm!SubForm.Form.Recordsetclone
rst.FindFirst "OrdDate = #" & Me.FindRec & "#"
If not rst.NoMatch Then
Forms!MainForm!SubForm.Form.BookMark = rst.BookMark
else
msgbox "Not Found!"
Endif
'clear out the search field for the next search
Me.FindRec = Null
Endif
rst.Close
set rst = Nothing
I would suggest you paste this code into a code module and highlight
some of the keywords you are unfamiliar with and press the F1.
BTW, you surround Dates with #, Text fields with quotes, number with
nothing. Ex:
rst.FindFirst "Field = #" & Me.Search & "#" 'date
rst.FindFirst "Field = " & Me.Search 'number
rst.FindFirst "Field = '" & Me.Search & "'" 'string
or
rst.FindFirst "Field = """ & Me.Search & """ 'string
