Searching data on a linked table

[Thebounder]

I currently have a database which contains the following tables:

Customer_Details
Customer_Number
Customer_Name
Address_Line_1
...
Invoice_Number

Invoice_Details
Sub_Invoice_Number - Primary Key
Invoice_Number
Details


I want to display both tables on the same form.
This is no problem.

But I want to be able to search any of the fields of the form.

I have created another table containing all the fields but I don't know where to do from there!!!

Dim MyDB As Database
Dim MyWorkspace As Workspace

Set MyWorkspace = DBEngine.Workspaces(0)
Set MyDB = MyWorkspace.Databases(0)

Dim stDocName As String
Dim stLinkCriteria As String
Dim SearchForm As Boolean

SearchForm = False
stDocName = "Search_Customer_Invoice"
stLinkCriteria = ""

' Search for the Part Number

If Not IsNull(Current_Date) And Current_Date <> &quot;&quot; Then
stLinkCriteria = stLinkCriteria + _
&quot;[Current_Date] LIKE &quot; & &quot;'*&quot; & Me![Current_Date] & &quot;*'&quot; & &quot; AND &quot;
SearchForm = True

End If

......


If SearchForm = True Then

If Right(stLinkCriteria, 5) = &quot; AND &quot; Then
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)
End If

Set ComponentDetails = MyDB.OpenRecordset(&quot;Invoice_Table&quot;, DB_OPEN_DYNASET)

ComponentDetails.FindFirst stLinkCriteria

If ComponentDetails.NoMatch Then

Dim msgbox_result As Integer

msgbox_result = MsgBox(&quot;There are no matching records&quot;, 49, &quot;No Matching Records&quot

Else

DoCmd.OpenForm stDocName, , , stLinkCriteria

Rem DoCmd.Close acForm, SearchDocForm


End If

End If



Many thanks

TimS

 

[BSman]

I'm not sure about your tables based on the names. It sounds like what you call &quot;Customer Details&quot; is actually the &quot;header&quot; part of the invoice, while Invoice Details contains the line items that make up the invoice. I'm going to go on that premise.

In the form's footer (or header) create two drop down lists, one that list the two tables and the second that has all of the columns you want users to be able to search in each table. Create a text box to contain the value to search for.

When the user selects a table, requery the column combo box, since the list (which could come from a table you set up with tablename and column names) should only display the columns for the selected table. So the user selects the table, the column, and enters the value.

You might also include a &quot;Search&quot; button for the user to click to start the search.

Now comes the tricky part. I'm going to assume that your form is actually a form (based on the Customer Details) and a subform (Invoice Details). When the user clicks &quot;Search&quot; your code should determine which table was selected, then select only records for that table that match the entered criteria.

In order to properly select based on the details, you might want to use a query that combines the two tables, since you may actually need to find all records in the customer table where there is a matching record in the invoice details table (if the user has selected invoice details as the table containing the data to be used for selecting records).