INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How To

Use multiple combo boxes to generate a report by Zuzia
Posted: 7 Jul 05

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

Back to Microsoft: Access Reports FAQ Index
Back to Microsoft: Access Reports Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close