Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Use multiple combo boxes to generate a report

How To

Use multiple combo boxes to generate a report

by  Zuzia  Posted    (Edited  )
I did finally fix the issue that I was having with my combo boxes, and it was actually very different from any of the solutions offered here, so I will elaborate on it below. In any case, I am thankful to all of you for your help.

Goal:
1. Having 4 combo boxes, I needed to generate a report of the matched records, where null values were ignored and not all 4 boxes needed to have input.

Issue:
1. The report would generate only on the first combo box, and would remain blank on all others.
2. The SQL code underlying the report query appeared not to work.

Findings:
1. All combo boxes needed to have a row source property set to individual queries with distinct select statements based on the table.
2. The VBA code behind the Search button was fine in generating a report, and it is as follows:

' This module searches for the records selected in the combo boxes and displays them as a list in a report
Private Sub cmdSearch_Show_Click()

Dim strDocName As String
strDocName = "rptMetersList"

'Check combo boxes for Null Entry first
'** Note: in conditional control statements, if the condition yields Null,
' its associated sequence of statements is NOT executed.

If Not (IsNull(Me![cmbMeter]) And IsNull(Me![cmbAccount]) _
And IsNull(Me![cmbAddress]) And IsNull(Me![cmbDate])) Then
'execute report display upon entering a meter type
DoCmd.OpenReport strDocName, acPreview

Else:
' Display error message on all empty fields search
MsgBox "Please enter at least one search value! Click OK, and try again.", vbOKOnly _
+ vbCritical, "Invalid Search Criteria"
Me![cmbMeter].SetFocus
Exit Sub
End If

End Sub

3. The control query behind the generated report needed to have built expressions in the Criteria section, where each given 4 combo boxes resulted in 16 choices of null or non null values.

The code follows: (built by Access so not too pretty, and long!)

SELECT tblMeterInventory.[Type of Meter], tblMeterInventory.[Account No], tblMeterInventory.[Service Address], tblMeterInventory.Date
FROM tblMeterInventory
WHERE (((tblMeterInventory.[Type of Meter])=[Forms]![mainmenu]![cmbMeter]) AND ((tblMeterInventory.[Account No])=[Forms]![mainmenu]![cmbAccount]) AND ((tblMeterInventory.[Service Address])=[Forms]![mainmenu]![cmbAddress]) AND ((tblMeterInventory.Date)=[Forms]![mainmenu]![cmbDate])) OR (((tblMeterInventory.[Account No])=[Forms]![mainmenu]![cmbAccount]) AND ((tblMeterInventory.[Service Address])=[Forms]![mainmenu]![cmbAddress]) AND ((tblMeterInventory.Date)=[Forms]![mainmenu]![cmbDate]) AND (([Forms]![mainmenu]![cmbMeter]) Is Null)) OR (((tblMeterInventory.[Type of Meter])=[Forms]![mainmenu]![cmbMeter]) AND ((tblMeterInventory.[Service Address])=[Forms]![mainmenu]![cmbAddress]) AND ((tblMeterInventory.Date)=[Forms]![mainmenu]![cmbDate]) AND (([Forms]![mainmenu]![cmbAccount]) Is Null)) OR (((tblMeterInventory.[Service Address])=[Forms]![mainmenu]![cmbAddress]) AND ((tblMeterInventory.Date)=[Forms]![mainmenu]![cmbDate]) AND (([Forms]![mainmenu]![cmbMeter]) Is Null) AND (([Forms]![mainmenu]![cmbAccount]) Is Null)) OR (((tblMeterInventory.[Type of Meter])=[Forms]![mainmenu]![cmbMeter]) AND ((tblMeterInventory.[Account No])=[Forms]![mainmenu]![cmbAccount]) AND ((tblMeterInventory.Date)=[Forms]![mainmenu]![cmbDate]) AND (([Forms]![mainmenu]![cmbAddress]) Is Null)) OR (((tblMeterInventory.[Account No])=[Forms]![mainmenu]![cmbAccount]) AND ((tblMeterInventory.Date)=[Forms]![mainmenu]![cmbDate]) AND (([Forms]![mainmenu]![cmbMeter]) Is Null) AND (([Forms]![mainmenu]![cmbAddress]) Is Null)) OR (((tblMeterInventory.[Type of Meter])=[Forms]![mainmenu]![cmbMeter]) AND ((tblMeterInventory.Date)=[Forms]![mainmenu]![cmbDate]) AND (([Forms]![mainmenu]![cmbAccount]) Is Null) AND (([Forms]![mainmenu]![cmbAddress]) Is Null)) OR (((tblMeterInventory.Date)=[Forms]![mainmenu]![cmbDate]) AND (([Forms]![mainmenu]![cmbMeter]) Is Null) AND (([Forms]![mainmenu]![cmbAccount]) Is Null) AND (([Forms]![mainmenu]![cmbAddress]) Is Null)) OR (((tblMeterInventory.[Type of Meter])=[Forms]![mainmenu]![cmbMeter]) AND ((tblMeterInventory.[Account No])=[Forms]![mainmenu]![cmbAccount]) AND ((tblMeterInventory.[Service Address])=[Forms]![mainmenu]![cmbAddress]) AND (([Forms]![mainmenu]![cmbDate]) Is Null)) OR (((tblMeterInventory.[Account No])=[Forms]![mainmenu]![cmbAccount]) AND ((tblMeterInventory.[Service Address])=[Forms]![mainmenu]![cmbAddress]) AND (([Forms]![mainmenu]![cmbMeter]) Is Null) AND (([Forms]![mainmenu]![cmbDate]) Is Null)) OR (((tblMeterInventory.[Type of Meter])=[Forms]![mainmenu]![cmbMeter]) AND ((tblMeterInventory.[Service Address])=[Forms]![mainmenu]![cmbAddress]) AND (([Forms]![mainmenu]![cmbAccount]) Is Null) AND (([Forms]![mainmenu]![cmbDate]) Is Null)) OR (((tblMeterInventory.[Service Address])=[Forms]![mainmenu]![cmbAddress]) AND (([Forms]![mainmenu]![cmbMeter]) Is Null) AND (([Forms]![mainmenu]![cmbAccount]) Is Null) AND (([Forms]![mainmenu]![cmbDate]) Is Null)) OR (((tblMeterInventory.[Type of Meter])=[Forms]![mainmenu]![cmbMeter]) AND ((tblMeterInventory.[Account No])=[Forms]![mainmenu]![cmbAccount]) AND (([Forms]![mainmenu]![cmbAddress]) Is Null) AND (([Forms]![mainmenu]![cmbDate]) Is Null)) OR (((tblMeterInventory.[Account No])=[Forms]![mainmenu]![cmbAccount]) AND (([Forms]![mainmenu]![cmbMeter]) Is Null) AND (([Forms]![mainmenu]![cmbAddress]) Is Null) AND (([Forms]![mainmenu]![cmbDate]) Is Null)) OR (((tblMeterInventory.[Type of Meter])=[Forms]![mainmenu]![cmbMeter]) AND (([Forms]![mainmenu]![cmbAccount]) Is Null) AND (([Forms]![mainmenu]![cmbAddress]) Is Null) AND (([Forms]![mainmenu]![cmbDate]) Is Null)) OR ((([Forms]![mainmenu]![cmbMeter]) Is Null) AND (([Forms]![mainmenu]![cmbAccount]) Is Null) AND (([Forms]![mainmenu]![cmbAddress]) Is Null) AND (([Forms]![mainmenu]![cmbDate]) Is Null));

After all this, all works very well! I hope that some of you may find this helpful.

Thanks everyone,
Z
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top