Locked lesson.

Upgrade

About this lesson

Use criteria from a Form Control when opening a report with VBA.

Quick reference

Open Report with Criteria, VBA

VBA

Use Criteria from a Form Control 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.

This code is executed on the Click event of specified control and is in the code behind the form object.

The control name that launches this code is called cmd_RptCustomerServicesByYear.

Two variables, vWhere and vFriendly, are dimensioned as variants so that the code can use them and know what kind of data to expect.  Variants are allowed to 'be' Null, which means no value.

vWhere is a variable containing the criteria the user wants to limit the report for.  It will be used in the OpenReport statement.

vFriendly is a variable containing a friendly version of the report criteria and will be passed to the report object in the OpenReport statement.

The two variables are both initialized to have no value, which is referenced as Null.

This code checks to see if a control called CustID is filled out on the form.  If it is filled out, vWhere and vFriendly are assigned values. 

IsNull(expression) is a function that returns True or False by evaluating the expression.

Not IsNull(expression) means to change True to False and False to true. 

If If Not IsNull(Me.CustID) is True then  something is filled out in the CustID control.
For instance: If vWhere is equal to 1, then vWhere will be --> CustID = 1

vWhere is a variable containing the Where Condition parameter for the OpenReport action. Refer to the Quick Reference Guide in Lesson 36 for OpenReport parameters.

Me.CustID is a reference to the combo box on the form that this code is behind.

Me.CustID.Column(1) means the value in the second column of the combo box since column indexing starts with 0.

CustID is also the name of a field in the report RecordSource.

If Me.CustID is 1 and the customer corresponding to this value is Alpha Associates, then vFriendly will be --> Customer = Alpha Associates

Once vWhere and vFriendly are determined, the report is opened.

OpenReport is a macro action, which is why it is prefaced with DoCmd.

The Report Name that will be opened is "r_CustomerServicesByYear".  Quotes are used as delimiters to mark where the text begins and ends.

acViewPreview is a constant for the View parameter and means that the report will open in Print Preview.

There are 2 commas between acViewPreview and vWhere because the Filter Name argument is being skipped.

The filter for the report is set to vWhere.  Data will automatically be filtered without having to do anything else.

vFriendly is being passed to the report in the OpenArgs parameter.  OpenArgs stands for Open Arguments and can be anything you want.  It is up to the report to use this information.

There are also 2 commas between vWhere and vFriendly because the Window Mode argument is also being skipped.

 

The Dim and OpenReport statements use line continuation.  When a statement is continued on the next line, it ends with

Extra space in the code is okay and helps with readability.

A statement beginning with single quote ( ' ) is a comment and will be ignored by the compiler.

The terms Argument and Parameter can be used interchangeably.

 

Process Information in OpenArgs when a Report is Opened

When the report is opened, the OpenArgs property is tested to see if it contains anything.  If OpenArgs has a value, the caption of a label is set to whatever is in the OpenArgs parameter.  Otherwise, the caption of the label is set to a space.

This code is executed on the Open event of the report and is in the code behind the report object.

A statement beginning with single quote ( ' ) is a comment and will be ignored by the compiler.

There is a label control on the report that this code is behind called Label_Criteria

The Caption property of the label will be changed to what is in the OpenArgs parameter. If nothing is there, the label caption will be set to a space.

 

SQL Statement

SQL stands for Structured Query Language.  An SQL Statement can select data from one or more tables, sort and filter.  The basic syntax for an SQL statement is:

SELECT fieldlist

FROM tablename

IN anotherdatabase.mdb

WHERE conditions

GROUP BY fieldlist

HAVING conditions for fields that are grouped

ORDER BY fieldlist;
 

The only necessary phrases to specify are SELECT (what fields to show and calculate) and FROM (what tables or queries the data will come from).

 

Login to download
  • 00:04 This is Lesson 37 of Access 2013.
  • 00:10 In the last lesson,
  • 00:11 we created command buttons to open reports.
  • 00:15 Now we will learn how to use criteria.
  • 00:19 Hi, this is Crystal.
  • 00:21 Let's implement customer criteria for the first report.
  • 00:26 Although the Customer name is showing,
  • 00:28 CustID is being stored.
  • 00:31 Look at the RowSource for the Customer combo box.
  • 00:35 Click in the RowSource property and click the Builder button.
  • 00:39 You see CustID is the first column
  • 00:42 and Customer name is the second column.
  • 00:45 The third column specifies the sort to be Ascending
  • 00:48 by customer name, but it is not showing.
  • 00:52 Here is the SQL statement, which is what Access stores.
  • 00:56 Now don't let this scare you off
  • 00:58 just because it another new thing.
  • 01:02 After the word SELECT are the fields we want.
  • 01:05 After FROM is the table name these fields come from.
  • 01:11 After ORDER BY is how the information will be sorted.
  • 01:16 Each fieldname is preface with tablename.
  • 01:19 in case more than one table is being used.
  • 01:23 There are going to be 2 columns so the Column Count is 2.
  • 01:28 The first column will not show so it will have a width of 0
  • 01:32 in the Column Widths property.
  • 01:35 I like easy numbers
  • 01:37 so I will change the width of the second column,
  • 01:41 the customer name that shows, to 1.5
  • 01:45 The List Width will be the sum of the column widths
  • 01:48 plus 0.2" to allow for a scrollbar
  • 01:52 in case there are many customers.
  • 01:55 Go to the form view
  • 01:56 and pick Alpha Associates in the customer combo.
  • 02:00 Now let's make this work!
  • 02:03 Go to the code for the Click event
  • 02:05 for the Customer Services By Year command button.
  • 02:09 Create a new line under the comment at the top and type:
  • 02:13 dim vWhere [and use uppercase for W, Press ENTER]
  • 02:20 vwhere = Null
  • 02:23 Notice that when you dimension variables and you mix the case,
  • 02:26 Access adjusts the case when the variable name is recognized.
  • 02:31 vWhere is a variable name to hold the criteria for the report,
  • 02:35 and is initialized to be Null, or nothing.
  • 02:38 You can call this whatever you want
  • 02:40 as long as it is not a reserved word.
  • 02:43 A link to reserved words is in the Quick Reference.
  • 02:47 type:
  • 02:48 If Not IsNull(Me.CustID) Then [Enter]
  • 03:00 [Tab] vWhere = "CustID=" & Me.CustID [Enter]
  • 03:13 [Shift-Tab] End If
  • 03:16 This is saying that if the CustID control is filled out on Me,
  • 03:19 which is the form that this code is behind,
  • 03:22 then vWhere will get a value.
  • 03:25 This is the Customers table.
  • 03:27 What is CustID for Alpha Associates?
  • 03:31 If Alpha Associates is picked in the combo,
  • 03:34 what will be the value in vWhere?
  • 03:37 CustID=1
  • 03:40 If you press the F1 key
  • 03:41 while you are on the OpenReport keyword,
  • 03:44 this is what comes up.
  • 03:46 We have specified the ReportName and the View.
  • 03:50 Now we want to also specify the WhereCondition parameter.
  • 03:55 Go to the end of the statement with OpenReport
  • 03:58 and type a comma.
  • 04:00 Skip the FilterName argument,
  • 04:02 so type another comma and then vWhere.
  • 04:05 Debug, Compile and Save.
  • 04:08 Now let's go to the Form View and test the button.
  • 04:11 Alpha is picked.
  • 04:13 The first page of the report shows Alpha
  • 04:16 and the second page has the grand totals, so it worked.
  • 04:20 We know we picked this criteria,
  • 04:22 but what if you are looking at a report you printed last week?
  • 04:27 We need a way for the report to tell us the criteria too.
  • 04:31 Go back to the form,
  • 04:32 and back to the command button code.
  • 04:36 We will modify the Dim statement to specify
  • 04:38 that vWhere is a Variant data type.
  • 04:42 This is the default if you don't specify the data type,
  • 04:46 but it always better to be more clear.
  • 04:49 Variants are allowed to be Null,
  • 04:51 which is why we use it here.
  • 04:54 Now we want to dimension another variable
  • 04:56 to show the criteria in a friendly way
  • 04:58 since CustID=1 works for the database,
  • 05:02 but most people wouldn't like to see the criteria like that.
  • 05:07 At the end of the line with Dim, type
  • 05:09 space underscore and press ENTER to create a new line.
  • 05:13 Tab in then type: , vFriendly As Variant
  • 05:18 and press ENTER.
  • 05:20 We could have put these declarations on the same line,
  • 05:23 but code is easier to read when they are on separate lines.
  • 05:28 When a line ends with space underscore,
  • 05:30 that means the statement is continued on the next line.
  • 05:35 VBA may be a new idea for you,
  • 05:37 so you may have to watch this lesson a few times
  • 05:41 before it is comfortable.
  • 05:43 Copy vFriendly to the clipboard.
  • 05:45 Initialize it to Null.
  • 05:48 If we are going to use criteria, we also want to assign vFriendly.
  • 05:53 Control-drag the vWhere assignment
  • 05:55 in the IF block and change the new statement to:
  • 05:59 vFriendly = "Customer =" & Me.CustID.Column(1)
  • 06:16 You already know that Me.CustID is the combo box.
  • 06:21 The Column Count starts with 0,
  • 06:24 so 1 really means the second column,
  • 06:26 which is the one that shows and has the customer name.
  • 06:30 How do we tell the report about the friendly value?
  • 06:32 Going back to Help,
  • 06:34 we see that the last parameter is called OpenArgs,
  • 06:38 which stands for Open Arguments.
  • 06:41 OpenArgs is anything you want it to be,
  • 06:44 so now it will be vFriendly.
  • 06:47 This means that the report will have to know what to do with it.
  • 06:52 Again, we are skipping a parameter,
  • 06:54 so there are 2 commas in a row.
  • 06:57 Debug, Compile and go to the Design View of
  • 07:00 r_CustomerServicesByYear.
  • 07:04 First, we need to make a place on the report
  • 07:06 to show the criteria.
  • 07:09 Temporarily make the Page Header section
  • 07:11 a little taller so you can copy the line.
  • 07:14 Click in the Page Footer and paste the line.
  • 07:18 On the Design ribbon, click the Label tool and create a label
  • 07:21 with a space in it in the Page Footer.
  • 07:25 Set properties of the new label to:
  • 07:27 Name: Label_Criteria (Other)
  • 07:33 Top: 0 (Format)
  • 07:37 Left: 0 (Format)
  • 07:39 Width: 7" (Format)
  • 07:41 Close the extra space in the Page Footer.
  • 07:45 When the report opens, we will fill out criteria if it applies.
  • 07:50 Select the report object by clicking where the rulers intersect.
  • 07:55 Go to the Event tab of the Property Sheet.
  • 07:58 In the Open event, type the following:
  • 08:01 [Tab] 'date you are writing code [Enter]
  • 08:06 If Not IsNull(Me.OpenArgs) Then [Enter]
  • 08:21 [Tab] Me.Label_Criteria.Caption = Me.OpenArgs [Enter]
  • 08:32 [Shift-Tab] Else [Enter]
  • 08:36 [Tab] Me.Label_Criteria.Caption = " " [Enter]
  • 08:46 [Shift-Tab] End If
  • 08:49 Substitute your date in the comment.
  • 08:52 If the open arguments are filled out,
  • 08:53 the Caption property of the label will be changed
  • 08:56 to be whatever was passed in the OpenArgs.
  • 08:59 Otherwise, the label will be a space.
  • 09:03 Debug, Compile, Save, and close the VBA window.
  • 09:08 Close the report too.
  • 09:10 On the form, go to the form view
  • 09:12 and click the first report button.
  • 09:15 And, my dear Watson, here we have it.
  • 09:18 We need to add a space between the equal sign
  • 09:20 and the customer name,
  • 09:23 which we will do in the next lesson
  • 09:25 when we add more criteria.

Lesson notes are only available for subscribers.

Gift this course