INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...I love the structure of the site. You start at the top, and drill down to what you want. Maybe I've been using Unix too long... :-) "
Geography
Where in the world do Tek-Tips members come from?
|
Microsoft: Access Reports FAQ
|
How To
|
Use multiple combo boxes to generate a report
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
|
|
 |
|
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:
Talk To Other Members- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close
