Open Report with Multiple Criteria that is Optional

Locked lesson.

Upgrade

About this lesson

Use multiple criteria from Form Controls when opening a report.

Quick reference

Open Report with Multiple Criteria that is Optional, VBA

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 ")

means that

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 + ", ")

means that

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.

http://www.AccessMVP.com/strive4peace/VBA.htm

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").

Login to download
  • 00:04 This is lesson 38 of Access 2013.
  • 00:09 We are going to learn how to implement multiple criteria
  • 00:13 when opening reports and make it optional.
  • 00:16 Hi, this is Crystal.
  • 00:18 Go to the Design view of f_MAINMENU
  • 00:22 and copy the Customer combo box.
  • 00:25 The RowSource is what determines what is displayed
  • 00:28 so we need to change it.
  • 00:30 Click in the RowSource property for the copied combo,
  • 00:34 and then click the Builder button
  • 00:35 to change where the data comes from.
  • 00:38 Right-click in a blank area of the query
  • 00:40 and choose Show Table.
  • 00:43 Double-click on the Services table,
  • 00:45 then close the dialog box.
  • 00:48 Double-click ServiceID and ServName
  • 00:51 Click on the Customers fieldlist
  • 00:53 and press the Delete key.
  • 00:56 Sort by ServName and look at the Datasheet View.
  • 01:00 Close the Query Builder
  • 01:01 and then click Yes to save the changes.
  • 01:05 Zoom the RowSource with Shift-F2
  • 01:08 and copy the ServiceID field.
  • 01:11 Name the combo ServiceID
  • 01:13 and name its label Label_ServiceID.
  • 01:17 Change the label Caption to Service:
  • 01:21 Save the form and look at the form view
  • 01:23 to test the Service combo.
  • 01:26 I am collapsing my ribbon to get more space.
  • 01:29 Copy and paste the ServiceID combo
  • 01:32 and change the RowSource
  • 01:34 to show ServTyID and ServType from the ServTypes table.
  • 01:40 Delete the Services fieldlist.
  • 01:43 Sort in Ascending order by ServType.
  • 01:46 Look at the Datasheet View,
  • 01:48 close the Builder, and Save the changes.
  • 01:52 Name the combo box ServTyID
  • 01:55 and Name the label Label_ServTyID.
  • 02:00 Change the Caption of the label to Service Type:
  • 02:04 Save the form and look at the Form View.
  • 02:07 The combos work okay
  • 02:09 but the label for Service Type is truncated.
  • 02:12 Go back to Design View and best-fit the labels.
  • 02:17 Now let's modify the code for the first report
  • 02:20 to get it to process more criteria.
  • 02:23 Go to the builder for the Click event.
  • 02:26 Create blank lines under the IF block of code.
  • 02:30 Select the IF statements
  • 02:32 and Ctrl-Drag them to create a copy.
  • 02:35 Change CustID to ServTyID,
  • 02:38 and Customer to Service Type.
  • 02:41 When both criteria are filled,
  • 02:44 Access strings them together with AND.
  • 02:48 Modify the code to say:
  • 02:51 vWhere = (vWhere + " AND ") & "ServTyID=" & Me.ServTyID
  • 03:19 vFriendly = (vFriendly + ", ")
  • 03:34 & "Service Type = " & Me.ServTyID.Column(1)
  • 03:53 When the + operator is used to combine,
  • 03:56 and something in the parentheses is Null,
  • 03:59 then everything inside the parentheses will be Null.
  • 04:03 When the ampersand is used to concatenate, it ignores Nulls.
  • 04:08 Notice, I did NOT use + inside the parentheses
  • 04:12 for vFriendly because I want you to see what happens.
  • 04:18 Copy the service type statements
  • 04:20 to create the code for service.
  • 04:23 Change ServTyID to ServiceID,
  • 04:26 and Service Type to Service.
  • 04:30 Debug, Compile the code and Save.
  • 04:33 I wouldn't have to save on the form too, but I do it out of habit
  • 04:37 before I look at a form or report.
  • 04:40 Choose Alpha Associates for the Customer,
  • 04:43 Trim Bush for the Service,
  • 04:45 and look at the first report by clicking the command button.
  • 04:49 The report is filtered for the criteria,
  • 04:51 and the friendly string looks good at the bottom,
  • 04:54 except we still need a space after the equal sign.
  • 04:57 Remember, anything inside double quotes
  • 05:00 will be printed literally,
  • 05:02 so add a space after the equal sign
  • 05:04 inside the quotes for each condition.
  • 05:07 Debug, Compile, Save, and look at the report again.
  • 05:11 Aah, that's better.
  • 05:14 Close the Print Preview.
  • 05:16 If you don't want criteria to be considered,
  • 05:19 select it and delete it.
  • 05:22 Get a report showing all work for any customer
  • 05:25 for service is Mow Big Lawn.
  • 05:28 Alpha and Echo are the only 2 customers with big lawns.
  • 05:33 Look at the criteria.
  • 05:34 Now you see why we need + instead of &.
  • 05:38 We do not want a comma to be displayed before the service
  • 05:42 if no other criteria is filled.
  • 05:45 Go back to the code and fix it,
  • 05:47 if you made the same mistake that I did.
  • 05:50 If you changed code, then Debug, Compile, and Save.
  • 05:54 There, that is much better.
  • 05:58 We still have 2 pages for Mow Big Lawn.
  • 06:02 Let's see what out friendly string looks like
  • 06:04 with all 3 criteria filled.
  • 06:07 Choose Betty Barkley, Edge Garden Beds, and Lawn Care.
  • 06:13 The friendly string works
  • 06:14 and so does the filter and the calculations.
  • 06:17 Betty is the only customer with garden beds to edge,
  • 06:21 so her percentages are 100%.
  • 06:25 What if we pick a different customer?
  • 06:28 The reason we get an eror is because there are no pages.
  • 06:32 Choose Run, Reset to stop the debugger.
  • 06:36 We need to add code to handle this issue.
  • 06:39 Go to the Design View of the report
  • 06:41 and click in the No Data event.
  • 06:44 Notice there is a Cancel argument
  • 06:47 and guess what we are going to do?
  • 06:50 We are going to set Cancel to be True
  • 06:52 because if the report doesn't have any data,
  • 06:55 there is nothing to see.
  • 06:57 Debug, Compile, Save, and click the report button.
  • 07:01 What? We still get an error message.
  • 07:04 Look at what it says though.
  • 07:06 The message is telling us that OpenReport was canceled.
  • 07:10 We don't want to see that.
  • 07:12 In the code above the OpenReport statement,
  • 07:15 add the following line:
  • 07:17 On Error Resume Next
  • 07:23 Right-click on the statement you just added, choose
  • 07:27 Set Next Statement from the shortcut menu,
  • 07:30 and press F5 to continue.
  • 07:33 Nothing appears to happen but the yellow goes away.
  • 07:37 The code is already compiled so Compile is not enabled.
  • 07:42 Save and test.
  • 07:44 Now just nothing happens.
  • 07:46 Did I even click the button?
  • 07:48 Let's give the user a message when there is no data.
  • 07:53 under the Cancel statement, type:
  • 07:55 MsgBox "Report has no data", , "Pick Different Criteria"
  • 08:08 The first argument for MsgBox is
  • 08:11 what will be displayed inside the message box.
  • 08:15 The second argument is the buttons we want to show.
  • 08:18 If nothing is picked, the OK button will show by default.
  • 08:22 The third argument is the Title of the message box.
  • 08:26 Debug, Compile, Save, and Test.
  • 08:30 Now we see our friendly message
  • 08:32 instead of popping into the code.
  • 08:35 What happens when there is no criteria?
  • 08:38 The bottom of the report shows our space,
  • 08:41 which looks like nothing, just as expected.
  • 08:44 And we are back to having 7 pages.
  • 08:47 In the next lesson,
  • 08:49 we will add a few more buttons to our menu form,
  • 08:52 and set it to be the Startup form
  • 08:54 so it opens automatically when the database is opened.

Lesson notes are only available for subscribers.

Gift this course