Search Form using Combo Box and Link Two Subforms

Download Sample Access File:

Create Search Form Using Combo box and Link Two Subforms

This is an example file of how to create a Search or Filter data by using the combo box and how to link two subforms under the same main form. This example shows three different types of data source for combo box.

Part 1

1. Create a combo box with data source links the table.

2.Create a combo box with the data source by manually entering the value in the list

3. Create a combo box with the data source from the list in the same table. The list of data must be grouped in order to not showing a duplicate data in the combo box.

4. The Customer Type Report button will display after the combo box of Customer Type is selected. The report will show only the customer with the customer type matching on the datasheet shown below.

5. The State Report button will display after the combo box of State is selected. The report will show only the customer with the state of CA matching on the datasheet shown below. There is only one report in the database, but using different criteria in Macro Builder to open different report.

6. Example of using visible property on each combo box after update event. Learn how the report name change for each combo box.

7. Create two subforms and link them by Customer_ID. The first subform will display as datasheet and the second subform will show as a regular single form. The data on the second subform will change depending on the row of record selected on the first subform.

Part 2

This part of searching form is similar to part 1, but the user can combine search using more than one combo box. If using only one combo box then the result will be filtered only for that combo box. If two combo boxes are selected then the result will be filtered from both criteria combined from two combo boxes.

User can also view the report after search by selecting the combo box. For instance, there are 4 business customers selected from the combo box then the report will be show only 4 business customers corresponding to the datasheet form.

For example below, the Individual is selected for Customer type, Male is selected for Gender and CA is selected for State. The result of searching will be 5 records. User can click View Report button to view or print the customer list resulted from the searching from three combo boxes.

All three combo boxes are sharing the search function.

The SearchCriteria function is called under the AfterUpdate Event of each combo box.

Private Sub cboCustomerType_AfterUpdate()

Function SearchCriteria()
Dim CustomerType As String
Dim strGender, strState As String
Dim Task As String
Dim strCriteria As String

If IsNull(Me.cboCustomerType) Then   
   CustomerType = "[customer_type_id] like '*'"
Else
   CustomerType = "[customer_type_id] = " & Me.cboCustomerType & ""
End If
......................
......................
.......Full code in the Sample file
......................

   Me.tbl_Customer_subform1.Form.RecordSource = Task
   Me.tbl_Customer_subform1.Form.Requery
   Me.Text89 = findRecordCount(Task)   
If Me.Text89 = 0 Then   
MsgBox "No Record Found!", vbInformation, "Search Result"
End If
End function

Download file contains:

2 tables, 4 Forms, 1 Report and 1 Module