About this lesson
Use multiple criteria from Form Controls when opening a report.
Download this lesson’s related exercise files.Services_38_Start Open Report with Multiple Criteria that is Optional, VBA.accdb
1.8 MB Services_38_Open Report with Multiple Criteria that is Optional, VBA.accdb
Open Report with Multiple Criteria that is Optional, VBA
Use Multiple Criteria from Form Controls when Opening a Report
This code opens a report and filters it for criteria, if any is specified. It also passes a friendly string with criteria written in a way that someone looking at the report can understand.
For understanding of the statements that are not discussed here, refer to the reference file for lesson 37. The new statements are highlighted in yellow.
This code processes criteria for three (3) controls. If the CustID control is filled, vWhere and vFriendly are assigned. This is the same as the last lesson.
If Me.ServTyID (Service Type) is filled, vWhere and vFriendly will be what they were before and more.
When multiple criteria are combined, the word AND has to be between them.
(vWhere + " AND ")
If vWhere has a value then (space) AND (space) will be added to the end.
If vWhere is Null, nothing will be added.
When the + operator is used to combine expressions in parentheses, and something in the parentheses is Null, then everything inside the parentheses will be Null.
When the & (ampersand) is used to concatenate, Null is ignored.
The only way this can work is if vWhere is a variant, as opposed to be a specific data type.
(vFriendly + ", ")
If vFriendly has a value then (comma)(space) will be added to the end.
If vFriendly is Null, nothing will be added.
In this manner, multiple criteria can be tested and added! The code for ServiceID is just like the code for ServTyID except the control name and information inside the quotes is different.
Just as with the CustID control, the ServTyID and ServiceID controls are combo boxes that store the first column, which is hidden, and show the value ion the second column.
On Error Resume Next
means that if there is an error with subsequent statements (opening the report), errors will be skipped. For instance, if the OpenReport action is cancelled, Access will not give the user an error message because the OpenReport action could not execute.
VBA may be a new idea for you, so you may have to watch these lessons with VBA a few times before it is comfortable. The good news is that VBA is logical and makes sense! If you can build an Access database (not everybody can), then you can also learn VBA.
I started writing a book on programming with VBA but only got 3 chapters written. Perhaps reading it will help you understand it better.
Report NoData Event
What if the report has no data for the specified criteria?
Go to the Design View of the report.
On the property sheet, select the report object (click where rulers intersect) and click in the No Data property to create an [Event Procedure].
This code is executed when a report has no data and is in the code behind the report object.
Cancel = True means that the report will not be opened
MsgBox is a VBA command to give the user a message.
MsgBox "Report has no data", , "Pick Different Criteria"
The first argument for MsgBox is what will be displayed inside the message box ("Report has no data").
The second argument is the buttons we want to show. If nothing is picked, the OK button will show by default.
The third argument is the Title of the message box ("Pick Different Criteria").
Lesson notes are only available for subscribers.