Filter / Search in two tables

[MarkusJohn]

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

 

[Guest]

Change the RecordSource of the form to a query which includes both tables,
e.g. to order the records in the form by an OrderDate column for instance:

SELECT Order.*, Customer.[Name]
FROM Order INNER JOIN Customer
ON Order.CustID = Customer.CustID
ORDER BY OrderDate;

You can now filter the form to rows where the value of the Name column in
the Customer table matches the selection in the combo box:

If Not IsNull(Me.TxTName) Then
strWhere = strWhere & "([Name] = """ & Me.TxtName & """) AND "
End If

This will return all customers of the same name.

However, I'd suggest that you avoid the use of Name as a column name. It
can easily be confused with the Name property of an object (I recall a case
where someone got the name of their report in every row rather than the name
of each customer). Its better to use something like KundeName.

Have you considered using a combo box rather than the TxTName control? With
a RowSource of:

SELECT DISTINCT [Name]
FROM Customer
ORDER BY [Name];

the user can then select a name from the list rather than typing it in. If
the combo box's AutoExpand property is True the user can type in a name and
it will go to the first match as each character is entered

Ken Sheridan
Stafford, England