Filter / Search in two tables

[MarkusJohn]

I want to do a search based on a form with a filter througout two
tables.
There is one table with order data and another table with customer
data. I created text fields where the desired values can be entered.

The table order contains order specific attributes and is linked to the
table customer via customerid.

If I look for a specific customer ID the results are fine. If I look
e.g. for a name (which can occur more than once) I run into serious
problems.

I tried DLookup, however I got only one ID of an customer.

How do I implement something like DLookup that results me all the ID´s
that fit to the entered search term?

If there´s a solution for that problem, would it be reccomendable to
loop over the result set?
How could I do that?
I want to get a term like "id = 1 or id = 2 or id = n".

Thanks very much for your help!

 

[Rick Wannall]

Can you say more about what you're trying to accomplish? THere are
techniques for reports, techniques for data display on a form, techniques
for data entry on a form, and so on.

First approximation: If you want to display all orders for a customer, put
a combo box on an unbound form, and feed its display with the list of
customers. Put a subform in that form, and base the subform on the orders
table. Use the CusomerID (combobox name for the master, order table field
name for the child) as the link field between Master and Child data. When
you select a customer in the combobox on the main form, the order records
will automatically appear in the subform.

 

[Rick Wannall]

Can you say more about what you're trying to accomplish? THere are
techniques for reports, techniques for data display on a form, techniques
for data entry on a form, and so on.

First approximation: If you want to display all orders for a customer, put
a combo box on an unbound form, and feed its display with the list of
customers. Put a subform in that form, and base the subform on the orders
table. Use the CusomerID (combobox name for the master, order table field
name for the child) as the link field between Master and Child data. When
you select a customer in the combobox on the main form, the order records
will automatically appear in the subform.

 

[MarkusJohn]

okey..
I have a form.. in the header there are unbound search fields. The
entered values are concatenated to a string. When you press the filter
button in the form all the found entries appear.

The searchable fields are not only based on one table.
In my case I have the tables order and customer. primary keys for order
is order no and for customer custid. When I created the form I used the
wizzard and selected all required fields. But when I look for a
customers name the realtion order customer is not linked over name but
id. So I get for the name only one result or a mistake.
What I want is all Peter´s for example.
So my question how can I execute in a query in my vba code? DBLookup
works but results only one person..

Here´s a snippet of my code:
If Not IsNull(Me.TxTName) Then
id = 0
id = DLookup("[KundenID]", "Customer", "[Name] = '" &
Me.TxTName & "'")
strWhere = strWhere & "([CustomerID] = " & id & ") AND "
End If