By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,483 Members | 3,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,483 IT Pros & Developers. It's quick & easy.

To find a record in the sub form

P: n/a
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
Jul 29 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Jul 31 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.