Searching a Subform within a Main Form

[Guest]

Lets say you have a main form that displays the business' information. You
then have a subform that displays the owners info as there could be more than
one owner, which this subform points to a seperate table, for this reason.

How do you search this subform or subform (table) when you have the main
form open? The find tool in Access doesn't work and I also have a pull down
that searches for me, which isn't work now that I changed my database around.

Any ideas?

Thanks
Curtis

 

[Allen Browne]

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article describes how to filter the main form so it contains only those
records that have a match in the subform.

 

[Guest]

Can anyone shed a little light on this?

I have three pull downs. For a visual aid go:
www.gotmerchant.com/searchby.gif

The code I have did work at one time when I just had two pull downs, but
have to create a seperate form to be able to search by customer's name as it
is now split up into two fields, first & last name.

You select the first pull down, SEARCHBY (controlname) pull down. It
provides a selection of fields to choose from in the main table, using a
query. For what I'm getting at, the merchant's first & last name fields is
coming from another table, which is also included in the query, so it shows
up.

Now when you select something in SearchBy, you then select something in
SearchFor (control name of the first pull down). It is suppose to get its
info from the recordsource, see code below.

The twist is now I have an additional pull down for SearchBy, since the name
is now spil up into two fields. The control name of the second pull down is
SearchFor1

I have pasted the code below for the three fields. I tried to come up with
on my own and you can see what I have done to the SearchFor & SearchFor1
coding to see if that would work. Gives me this error saying Invalid
bracketing of name 'SELECT DISTINCT [MerchantFName'.


============CODE==========
Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"
End Sub
============CODE==========
Private Sub SearchFor_AfterUpdate()
Me.SearchFor1.RowSourceType = "Table/Query"
Me.SearchFor1.RowSource = "SELECT DISTINCT [" & _
Me.SearchFor & "] FROM [" & _
Me.SearchFor.RowSource & _
"] ORDER BY [" & Me.SearchFor & "]"
End Sub
============CODE==========
Private Sub SearchFor1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[SearchFor] & "] = " & Chr(34) & Me![SearchFor1] &
Chr(34)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End Sub
============CODE==========