Mastering Access 2002 Premium Edition

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Chapter 35: Working with Groups of Records Using Macros

Celeste Robinson and Alan Simpson

December 2002

Applies to:
    Microsoft Access 2002 Premium Edition

Buy this book

Summary: This article presents an excerpt from the book Mastering Access 2002 Premium Edition by Celeste Robinson and Alan Simpson. Learn how to work with groups of records using macros. (36 printed pages)

Contents

Sorting Records
Using Query By Form to Find a Group of Records
Using Multiple Criteria to Select a Group of Records
Finding Records to Print
Using a Custom Dialog to Select Records
Modifying a Group of Records

• Using the OrderBy and OrderByOn properties
• Using Query By Form
• Using a union query to show a null row
• Using a custom dialog box to collect input
• Saving values for another data entry session

In the previous two chapters, you've learned techniques for working with a single record, including navigating among controls, records, and forms; finding a specific record; and automating data entry operations.

This chapter focuses on working with groups of records. You'll learn how to automate two standard database operations: sorting and selecting groups of records. You'll learn how to sort records by using a macro to set a form's sorting properties. You'll also learn how to use a form to collect your selection criteria, pass the values to a filter query (called Query By Form), and then use the filter query to retrieve the specific records. You'll learn to use the Query By Form technique to synchronize two combo boxes on a form.

This chapter will also show you how to create a custom dialog box to collect user input and pass the collected values to a filter query that selects records from a report's record source. The last section will show you a technique for carrying values in a data entry form forward to the next data entry session. This technique uses a data entry table to hold the new record and action queries to append the new record to the data storage table and update the data entry table to hold only the values you want to carry forward.

Note:   This chapter assumes you have customized the Form view, Macro Design, and Database menu bars and toolbars to include the Macro command that displays the Run Macro dialog. If you haven't customized the command bars, you can do so now. This chapter also assumes that you've created the NwindMacros2002 (or NorthwindMacros) database as a copy of the Northwind database. Create a copy now if necessary.

We'll begin by creating a new form for the NwindMacros2002 database that we'll use to illustrate the macros. You can create the new form, named frmOrderStatus, by using the Form Wizard and then modifying the results. Figure 35.1 shows the new frmOrderStatus form. Here are the steps:

1. Use the Form Wizard to create a new tabular form. Select OrderID, OrderDate, RequiredDate, and ShippedDate from the Orders table; CompanyName from the Shippers table; CompanyName from the Customers table; and LastName from the Employees table. Name the form frmOrderStatus.

2. Set the form's Caption property to Order Status and then set the AllowAdditions property to No. The Order Status form is a review form and not intended for data entry. Setting the AllowAdditions property to No hides the blank record at the end of the recordset.

3. Select all of the data controls, set their Locked property to Yes, and set their formatting properties as shown below. Because the form is not intended for data entry, lock the control to prevent inadvertent changes.

   BackColor	12632256
   SpecialEffect	Flat
   BorderStyle	Transparent

   Note:   The reason you set the Locked property to individual controls rather than setting the form's AllowEdits property is as follows: If you set the form's AllowEdits property to No, you can't use a combo box on the form to choose records because you can't change (edit) the search value in the combo box.

4. Rearrange the controls, change the Caption properties for the labels as shown in Figure 35.1, and set the FontWeight property for the labels to Bold.

   

   Figure 35.1. Create an Order Status form for sorting and selecting records (click picture for larger image).

Sorting Records

When working interactively, you can easily sort a group of records displayed in a form by the values in a single field. To sort by the values in a single field in ascending or descending order, right-click in the control and select the Sort Ascending or Sort Descending command in the shortcut menu. To remove the sort, right-click in a control and select Remove Filter/Sort in the shortcut menu.

Sorting Interactively

For practice, sort the frmOrderStatus form by any one of its columns:

1. Click a control in the Customer column and then click the Sort Descending button. The records are sorted in descending order by the values in the column.
2. Switch to Design view, and observe that the form's OrderBy property is set to frmOrderStatus.Customers_CompanyName DESC.
3. Switch back to Form view, right-click in a control, and choose the Remove Filter/Sort command in the shortcut menu. The sort is removed and the records are displayed in their original order. If you switch to Design view, you will see that the OrderBy property is still set to frmOrderStatus.Customers_CompanyName DESC.
4. Switch back to Form view again. Choose the Apply Filter/Sort command in the Records menu. The sort is applied.
5. Save, close, and reopen the form. Access applies the OrderBy setting.
6. Remove the sort; then close and reopen the form. Access does not apply the OrderBy setting.

The OrderBy property

You can use a form's OrderBy property to sort by a single field, or you can create a complex sort of several fields with some fields in ascending and others in descending order. The OrderBy property is a string expression that consists of the name of the field or fields you want to sort, arranged in the order of the sort and separated by commas; to sort a field in descending order, you type DESC after the name of the field. For example, to sort by customer and then by order date in descending order, you set the OrderBy property to

frmOrderStatus.Customers_CompanyName DESC, OrderDate DESC

You can set the OrderBy property in the form's property sheet. The setting is saved when you close the form. Interactively, you apply and remove the OrderBy setting using the ApplyFilter/Sort and RemoveFilter/Sort menu commands.

Automate the Sort Process

You can automate the sort process using the OrderBy and OrderByOn properties.

Using the OrderByOn property

You use the OrderByOn property to apply or remove the sort specified in the OrderBy property by setting the OrderByOn property to Yes or No—or True or False in VB. For a form, you can set the OrderBy property in the form's property sheet in a macro or VB procedure and then use a macro or VB procedure to apply and remove the sort using the OrderByOn property. For a report, you can also set the OrderByOn property in the report's property sheet.

Using a triple-state toggle button

As an example, we'll create a button and a macro to sort the Order Status records by customer. We are going to use a toggle button instead of a command button because a toggle button has a value that can be used by the macro. Normally a toggle button has two values: True and False. Setting the TripleState property to Yes gives a third value: Null.

• When the toggle button has the value False, it looks like a command button.
• Clicking the button changes it to the Null state in which the button flattens but still appears raised.
• Clicking the button again changes it to the True state in which the flattened button appears sunken.

Figure 35.2. Click picture for larger image.

We'll create a macro to test the button's state and remove the sort if the button is in the False state, apply an ascending sort if the button is in the Null state, and apply a descending sort if the button is in the True state (see Table 35.1). Figure 35.3 shows the three states of the toggle button and the resulting sorts by customer.

1. Switch to Design view. Select all of the controls in the detail section, set their Enabled properties to No, and then switch back to Form view. The controls don't need to be enabled when you use the OrderBy property for the sort.

2. Create a new macro sheet named mfrmOrderStatus, enter the macro shown in Table 35.1, and save the macro sheet. The macro usesScreen.ActiveControlto refer to the toggle button's value. When the toggle button has the value False, theNot Screen.ActiveControlis True, and the macro sets the OrderBy property to "" (that is, the zero-length string) which is equivalent to removing the sort.

   

   Figure 35.3. Use a triple-state toggle button to remove a sort when the toggle button is False (a), apply an ascending sort when the button is Null (b), and apply a descending sort when the button is True (c).

3. Delete the Customer label in the header section, replace it with a toggle button, and set the properties as follows:

   Caption	Customer
   TripleState property	Yes
   Default Value	False
   ControlTip Text	Click to toggle ascending, descending, and no sort

4. Assign the mfrmOrderStatus.tglCustomer_Click macro to the OnClick property of the toggle button.

5. Save the form, switch to Form view, and click the toggle button (see Figure 35.4). As you cycle through the states, the records are sorted by customer name in ascending order, and then in descending order. The sort is then removed.

   Although Access saves the OrderBy and OrderByOn settings that are in effect when you close the form, it does not save the state of the toggle button. As a result, the property settings and the toggle button can get out of synch with one another when you first open the form. We'll use the macro shown in Table 35.2 to initialize the OrderBy and OrderByOn settings when the form opens. You have to refer to the form explicitly in this macro and can't use the Screen object because the form is not the active object when the Open event occurs. A form's Activate event occurs after the form's Open, Load, andResizeevents.

   Table 35.1. A Macro for the Three Sort Orders

   Macro Name	Condition	Action	Action Arguments
   tglCustomer_Click
   	Not Screen.ActiveControl	SetValue	Item: Screen.ActiveForm .OrderByExpression: ""
   	IsNull(Screen .ActiveControl)	SetValue	Item: Screen.ActiveForm .OrderByExpression: "Customers.CompanyName"
   	Screen.ActiveControl	SetValue	Item: Screen.ActiveForm .OrderByExpression: "Customers.CompanyName DESC"
   		RunCommand	ApplyFilterSort

   Table 35.2. A Macro to Initialize the Sort Settings

   Macro name	Action	Action Arguments
   Form_Open
   	SetValue	Item: Forms!frmOrderStatus.OrderByOn
   		Expression: True
   	SetValue	Item: Forms!frmOrderStatus.OrderBy
   		Expression: ""

6. Click the mfrmOrderStatus macro sheet, enter the macro in Table 35.2, and save the macro sheet.

7. Assign the macro to the OnOpen event property of the form.

8. Save and close the form.

9. Open the form. The two sort property settings are initialized and in synch with the toggle button.

Sort by any column

When records are displayed in a tabular form, you can automate sorting the records by specific columns: Replace the label of each column you want to sort by with a triple-state toggle button, and create a macro to sort the records by a field in the column, such as the one in Table 35.1. Using this technique, each of the sorts is independent, and the last button clicked determines the sort. This means that if you toggle the Customers button to sort ascending by customer and then toggle the Employees button to sort ascending by employee, the result is that the records are sorted in ascending order by employee. Figure 35.4 shows the result; notice that the state of the Customers toggle button is out of synch because the toggle button is in the flattened raised state (to indicate an ascending sort by customer), while the records are sorted by employee.

Figure 35.4. The state of the Customer toggle button is out of synch: The button indicates an ascending sort by customer, but the records do not reflect the customer sort (click picture for larger image).

Macros for a complex sort

It is possible to write macros to keep the toggle buttons in synch with the current sort. Keeping the toggle buttons in synch with the current sort involves the same kind of macro programming that is required for a complex sort. In a complex sort, each subsequent sort on any field takes previous sorts into account. For example, in a complex sort, toggling a sort by customer and then a sort by employee would produce records sorted by customer, and then the records for each customer would be sorted by employee.

We'll do a complex sort for the Customer and Employee triple-state toggle buttons. When you click the Customer toggle button, the macro sets the sort depending on the current state of the Employee button. For example, if your click changes the Customer toggle button to Null to sort ascending by customer, the macro determines the state of the Employee button and then sets the form's OrderBy property as follows:

If the State of the Employee Button Is	The Macro Sets the OrderBy Property To
False (no sort by employee)	"Customers.CompanyName"
Null (ascending sort by employee)	"LastName, Customers.CompanyName"
True (descending sort by employee)	"LastName DESC, Customers.CompanyName"

Similarly, when you click the Customer Toggle button and change it to True (to sort descending by customer), there are three alternative sets of actions; when you toggle the Customer button to False (for no sort by customer), there are three more alternatives, depending on the current state of the Employee button for each of the three states for the Customer button. The macro for the Customer toggle button has the nine alternatives shown in Table 35.3.

Table 35.3. The Macro for the Customer Toggle Button Has Nine Alternatives

Macro Name	Condition	Action	Action Arguments
tglCustomer_Click
	Not tglEmployee And Not Screen .ActiveControl	SetValue	Item: Screen.ActiveForm.OrderBy
			Expression: ""
	IsNull(tglEmployee) And Not Screen .ActiveControl	SetValue	Item: Screen.ActiveForm.OrderBy
			Expression: "LastName"
	tglEmployee And Not Screen .ActiveControl	SetValue	Item: Screen.Active Form.OrderBy
			Expression: "LastName DESC"
	Not (tglEmployee) And IsNull(Screen .ActiveControl)	SetValue	Item: Screen.Active Form.OrderBy
			Expression: "Customers.CompanyName"
	IsNull(tglEmployee) And IsNull(Screen .ActiveControl)	SetValue	Item: Screen.Active Form.OrderBy
			Expression: "LastName, Customers_CompanyName"
	TglEmployee And IsNull( Screen .ActiveControl)	SetValue	Item: Screen.Active Form.OrderBy
			Expression: "LastName DESC,Customers .CompanyName"
	Not (tglEmployee) And (Screen .ActiveControl)	SetValue	Item: Screen.Active Form.OrderBy
			Expression: "Customers .CompanyName DESC"
	IsNull(tglEmployee) And (Screen .ActiveControl)	SetValue	Item: Screen.Active Form.OrderBy
			Expression: "LastName, Customers.CompanyName DESC"
	(tglEmployee) And (Screen .ActiveControl)	SetValue	Item: Screen .ActiveForm.OrderBy
			Expression: "LastName DESC, Customers .CompanyName DESC"

1. Open the frmOrderStatus form in Design view.

2. Following the steps for the Customer toggle button, replace the Employee label with an Employee toggle button named tglEmployee, and set the properties as described for the Customer toggle button.

3. In the mfrmOrderStatus macro sheet, create the tglEmployee_Click macro, modifying the macro in Table 35.1 to replace Customers.CompanyName with LastName. Save the macro sheet, and assign the tglEmployee_Click macro to the OnClick property of the tglEmployee toggle button.

4. Click the mfrmOrderStatus macro sheet and then modify the tglCustomer_Click macro, as shown in Table 35.3. Save the macro sheet.

5. Save the form and then switch to Form view.

6. Click the Employee toggle button. The records are now sorted by Employee (see Figure 35.5a).

7. Click the Customer toggle button. The records are now sorted first by Employee and then by Customer (see Figure 35.5b).

   

   Figure 35.5. The records are sorted by employee (a); then the records for each employee are sorted by customer (b).

At this point, the macro for the Customer toggle button is able to detect the state of the Employee button before setting the sort order; however, to complete the complex sort, we would need to modify the macro for the Employee button to detect the state of the Customer button. The tglEmployee_Click button can be modified to include nine alternatives similar to those given in the macro in Table 35.3.

If there are three toggle buttons, there are three alternative states that need to be considered for each button, or a total twenty-seven (3 × 3 × 3 = 27 ) for each button. While it is possible to use macro programming for three or more toggle buttons, a much better solution is to create on-the-fly a sort order that includes the sort fields in the order that the user selects them. This solution can be programmed much more efficiently using VB, so we won't continue with the macro solution.

Using Query By Form to Find a Group of Records

An important database operation is selecting a group of records that meet one or more selection criteria. For example, in the Order Status form, you may want to display a list of orders for a particular customer, shipper, or employee, or you may want to review all orders taken by an employee after a specific date. You may also want to see all orders to be shipped by a specific shipper before a certain required date.

When working interactively, you can select a specific group of records by creating and applying a filter. Microsoft Access provides several ways to create filters interactively, including Filter By Form and Filter By Selection. These techniques give the user powerful ad hoc querying abilities. You can also customize these techniques with programming. This section describes another technique called Query By Form that you can use to provide a simple interface for selecting a group of records.

Selecting a Group of Records Interactively

In this section, you'll select the orders for a specific customer. First, modify the record source to include the CustomerID from the Orders table so the primary key can be used to select records.

1. Open the frmOrderStatus form in Design view. Click the form's RecordSource property and then click the Build button at the right of the property box. The Query Builder opens (see Figure 35.6).

2. Select CustomerID, EmployeeID, and ShipVia from the Orders table, and drag them to the grid. You'll be ready to sort by employee or shipper later by including their primary key fields now.

3. Choose File . . . Save As and then save the query as qryOrderStatus. Close the Query Builder window, click Yes to save the query, and click Yes in the next dialog to update the form's RecordSource property.

   

   Figure 35.6. The record source for the frmOrderStatus form (click picture for larger image).

4. Switch to Form view and then choose Records . . . Filter . . . Advanced Filter/Sort. The Filter design window opens.

5. From the field list, drag the CustomerID field to the first Field cell in the filter grid and then type alfki in the Criteria cell (see Figure 35.7).

6. Click the Apply Filter button on the toolbar. The filter window closes, and the selected records are displayed. Access indicates the number of filtered records in the lower-left corner of the form (see Figure 35.8).

7. Click the Remove Filter button on the toolbar to remove the filter.

   

   Figure 35.7. Using the Filter design window to create a filter (click picture for larger image).

   

   Figure 35.8. The filtered records (click picture for larger image).

Creating a filter query

A filter is temporary and is destroyed when you close the form. Rather than use a filter, you can create a query and use it to select records. A filter query must be based on the same query that the form is based on and must include all the same fields.

1. In the Database window, select the qryOrderStatus query; then copy and paste the query as qfltOrderStatus.
2. Open the qfltOrderStatus query in Design view, right-click the upper pane, and choose the Properties command in the shortcut menu. Set the Output All Fields property to Yes. With this property setting, you can delete all fields from the query design grid except the fields you are using to select or sort records.
3. Delete all columns in the query design grid except the CustomerID column. In the next section, you enter selection criteria for the CustomerID field.
4. Save and close the query.

Automate the Selection Process

To automate the selection process, we'll use a search technique similar to the technique we used to find a single record in Chapter 33. The search technique uses a combo box variable, to hold the search value, and a macro triggered by the control'sAfterUpdateevent, to apply a filter and select the records. Specifically, we'll do the following:

• Place a combo box named cboCustomer that displays a list of customer IDs and company names in the header of the form.
• Modify the qfltOrderStatus query to use the value selected in the combo box as the criteria for the CustomerID field.
• Create a macro that uses the ApplyFilter action to run the filter query and then display the filtered records when the combo box recognizes theAfterUpdateevent.

Placing a selection combo box on the form

Creating the combo box is easy because the CustomerID field in the Orders table is a lookup field. When you drag a lookup field created as a combo box (or list box) to a form, its lookup properties are copied, and the field is displayed as a combo box (or a list box) on the form. Although we do not want the search combo box to be a bound control, we can save the work of creating a combo box by dragging the lookup field to the form and changing the control's properties.

1. Switch to Design view and then increase the height of the header section. Select all the labels, and drag them down to the bottom of the header section. Select CustomerID from the field list and drag to the header section above the Customer toggle button. The field is displayed as a combo box.

2. Change the combo box Name property to cboCustomer and then delete the setting for the ControlSource property. The ControlSource must be blank so that the combo box can be used to hold the search value as a variable. Note that the BoundColumn property is set to 1, so the combo box holds the value of the CustomerID for the combo box row that you select.

3. Change the label's Caption property to Select Customer and then move the label above the combo box. Point to the upper-left corner of the label box and wait for the mouse pointer to appear as a hand with a finger pointing. You can then drag the label without moving its text box at the same time. Use the Bold button to make the label appear in bold text so it is easier to see.

4. Save the form, and switch to Form view (see Figure 35.9).

   

   Figure 35.9. Create the Lookup combo box by dragging the Lookup field and deleting the ControlSource property. The Lookup combo box must be unbound in order to set a variable (click picture for larger image).

Setting the filter query criteria

Modify the filter query to use the value selected in the combo box as the criteria for the query's prefix field.

1. Open the qfltOrderStatus query in Design view.

2. Click the Criteria cell below CustomerID. In the next step, you will enter an expression in the Criteria cell so that the cell gets its value from the combo box on the form. When you design a parameter query to take its criteria values from a form, you are using Query By Form (also called QBF).

3. Press Shift+F2 (or Ctrl+Z, the custom key combination created in Chapter 31) to open the Zoom box and enter the full identifier of the combo box shown below. Alternately, you can click the toolbar Build button and use the Expression Builder to construct the expression. You don't have to type the square brackets yourself when the names don't include spaces or symbols; Access supplies the square brackets for you.

   [Forms]![frmOrderStatus]![cboCustomer]
   

4. Save the query. If you select a customer in the combo box and then click into the query and run it, the query selects the orders for the customer you chose.

Creating a macro to apply the filter

Use the ApplyFilter action to restrict or sort the records in a table or in the record source of a form or report. Chapter 31 looked at examples of using theFilter Nameand theWhere Conditionarguments to specify the filter. Here we use theApplyFilteraction to apply the qfltOrderStatus filter saved as a query. Table 35.4 shows the macro.

Table 35.4: A Macro to Filter Records

Macro name	Action	Action Arguments
cboCustomer_AfterUpdate
	ApplyFilter	Filter Name: qfltOrderStatus

1. Click the mfrmOrderStatus macro sheet, enter the macro in Table 35.4, and save the macro sheet.

2. Open the Order Status form in Design view and then assign the mfrmOrderStatus.cboCustomer_AfterUpdate macro to the AfterUpdate event property of the cboCustomer combo box.

3. Save the form and then switch to Form view.

4. Select a customer from the combo box. The records are filtered and displayed; Access displays the number of filtered records in the lower-left corner of the form (see Figure 35.10).

5. Click the Employee toggle button. The selection is sorted in ascending order by employee.

6. Choose the Remove Filter/Sort command in the Records menu, or right-click the form title bar, and select Remove Filter/Sort from the context menu. The filter and the sort are removed, and all records are displayed.

   

   Figure 35.10. After you select a customer, the macro applies the filter query that uses the combo box value to select records (Query By Form) (click picture for larger image).

Automate the removal of the filter

You can automate the removal of the filter by placing a command button in the form header and creating a macro to remove the filter.

1. Switch to Design view, place a command button in the header section, and set the Name property to cmdShowAll and the Caption property to &Show All.

2. Click in the window for the macro mfrmOrderStatus (or open the macro if it's not already on the desktop) and then add a new macro at the end of the sheet called cmdShowAll_Click.

3. Click in the next row, and select the ShowAllRecords action. The ShowAllRecords action removes the filter and sort and requeries the records.

4. Save the macro.

5. Assign the mfrmOrderStatus.cmdShowAll_Click macro to the command button.

6. Save the form, and switch to Form view (see Figure 35.11).

7. Click the Show All button. The filter and sort are removed, but the combo box continues to display the value selected.

   

   Figure 35.11. The macro for the Show All button removes the filter and displays all of the records (click picture for larger image).

   After you click the Show All button to remove the filter, the combo box should be null to indicate all records are displayed. Modify the macro that removes the filter so that it also sets the value of the combo box to Null. Table 35.5 shows the new macro.

   Table 35.5. A Macro to Remove the Filter and Sort and Initialize the Combo Box

   Macro Name	Action	Action Arguments
   CmdShowAll_Click
   	ShowAllRecords
   	SetValue	Item: cboCustomer
   		Expression: Null

8. Click the mfrmOrderStatus macro sheet, modify the cmdShowAll_Click macro as shown in Table 35.5, and save the macro sheet.

9. Click frmOrderStatus, and click the Show All button. The combo box is null, and all the records are displayed.

Using Multiple Criteria to Select a Group of Records

Often you want to use more than one criterion for selecting records. For example, you may want to find all orders that are handled by a specific employee for a certain customer. You can place a selection combo box in the form header for each field you want to use in selecting records, modify the filter query to include the additional criteria, create a macro to apply the filter for each new combo box, and, finally, modify the macro that removes the filter so that it also sets the combo boxes to null.

Creating a Second Selection Combo Box

As an example, let's create a second process to select records for an employee.

1. Switch to Design view, and drag the EmployeeID lookup field to the header section above the Employee toggle button.
2. Change the Name property to cboEmployee and then delete the setting for the ControlSource property.
3. Change the label's Caption property to Select Employee and then move the label above the combo box. The next step is to modify the filter query to include the value in the cboEmployee combo box as a selection criterion.
4. Open the query qfltOrderStatus in Design view, drag EmployeeID from the field list for the Employees table to the second Field cell, click the Criteria cell, and enter the expression Forms!frmOrderStatus!cboEmployee. You can also cut and paste the similar expression from the CustomerID Criteria cell and edit the field name.
5. Save and close the query. The next step is to create the macro to apply the filter after the user selects an employee using the new combo box.
6. Click the mfrmOrderStatus macro sheet, select and copy the cboCustomer_ AfterUpdate macro, and click in a new row and paste the copied rows.
7. Change the name of the pasted macro to cboEmployee_AfterUpdate. The next step is to modify the macro that removes the filter and clears the combo boxes.
8. Insert a new empty row below the last row of the macro cmdShowAll_Click; then select the SetValue action, and set its arguments as follows:

   Item:	cboEmployee
   Expression:	Null

9. Save the macro sheet. The final step is to assign the new cboEmployee_AfterUpdate macro to the cboEmployee combo box on the Order Status form.
10. In Design view for the Order Status form, assign the new cboEmployee_ AfterUpdate macro to theAfterUpdateevent property of the cboEmployee combo box. Save and close the form.

Testing the multi-criteria selection process

Let's test the process:

1. Open the frmOrderStatus form.
2. Click the Customer combo box and then select Alfreds Futterkiste. Surprisingly, no records are displayed.
3. Click the Employee combo box and then select the employee, Margaret Peacock. The two orders handled by Ms. Peacock for Alfreds Futterkiste are displayed.

Modify the query criteria to return records for all values of an empty combo box

As currently designed, the filter query requires that you make selections for both combo boxes (see Figure 35.12).

Figure 35.12. A filter query that returns records only when both combo boxes have a non-null value (click picture for larger image).

If the combo box for a field is null, the filter query looks for records with a null value in that field; finding no such records, the query displays none. Instead, the query returns records for all values in a field with a null combo box value. You can return all records for a combo box whose value is null by changing the criteria to include a test for null. For example, for the Customer combo box, use the expression

Forms!frmOrderStatus!cboCustomer Or Forms!frmOrderStatus!cboCustomer Is Null

If the value in cboCustomer is Null, this expression evaluates to True, and the query returns records with all values of CustomerID.

1. Go to Design view for the qfltOrderStatus query. In the Criteria cell for the CustomerID field, replace the criteria with the expression

   Forms!frmOrderStatus!cboCustomer Or Forms!frmOrderStatus!cboCustomer Is Null
   

2. In the Criteria cell for the EmployeeID field, replace the criteria with the expression

   Forms!frmOrderStatus!cboEmployee Or Forms!frmOrderStatus!cboEmployee Is Null
   

3. Save and close the query.

Retest the multi-criteria selection process as follows:

1. Click the Show All button. All records are displayed, and all combo boxes are blank.
2. Select Alfreds Futterkiste from the Customer combo box. The six orders for the customer are displayed (see Figure 35.13a).
3. Select Peacock, Margaret from the Employee combo box. The two orders for the customer that are handled by this employee are displayed (see Figure 35.13b).

Figure 35.13. You can select records using one combo box (a), and then narrow the selection with the second combo box (b).

Adding a Null Row to a Selection Combo List

The multiple criteria selection process works, but there is an additional refinement you may want to make. After you select a value for one of the combo boxes, you should be able to reset the value of this combo box to null and remove part of the filter. For example, after you select orders for a specific customer and employee, you should be able either to view all of the orders for the customer by simply selecting a null row in the Employee combo box, or to view all the orders handled by an employee by selecting a null row in the Customer combo box.

Adding a null row by using a union query

Displaying a null row in a combo box list is more difficult than you might expect. One solution is to create a second query consisting of rows with all null values and to then combine the two queries using a special query called a union query. You can use a union query to combine two select queries into a single recordset that contains the rows of both the first query and the second query and eliminates duplicate rows. A union query is an SQL specific query and must therefore be created in SQL view. In this example, we'll create each of the two select queries in query Design view, paste their equivalent SQL statements together, and modify the result into a union query.

Union queries

Use a union query when you want to create a single query that contains rows from two select queries. The two select queries must have the same number of fields, and the fields must be in the same order. Corresponding fields don't need to have the same names, but they must have compatible data types.

You can create a union query in SQL view by using the UNION operator to combine the SQL statements for each select query. The operator uses the field names from the firstSELECTstatement.

The UNION operator eliminates duplicate rows; use the UNION ALL operator if you want to return duplicate records. If you want to sort the rows, use a single ORDER BY at the end of the lastSELECTstatement. The field names that you use in the sort must come from the firstSELECTstatement.

You can learn more about creating SQL statements by searching for SQL or SQL statements with the Office Assistant.

Here are the steps for adding a null row to the cboCustomers combo list:

1. Switch to Design view for the Order Status form, select the cboCustomer combo box, click into its RowSource property, and click the Build button at the right of the property box. Figure 35.14a shows the query in Design view, and Figure 35.14b shows the equivalent SQL statement.

2. Choose View . . . SQL View to show the SQL statement for the query. Make sure the entire SQL statement is selected, and copy it to the clipboard. You'll need this statement later when you combine the two SQL statements.

3. Modify the SQL expression by removing the reference to the table name and the ORDER BY clause, as shown below. The modified SQL statement displays two null fields in each row (see Figure 35.15). To see this yourself, click the View button while you're in the SQL Statement window to see the datasheet view.

   SELECT Null AS CustomerID, Null AS CompanyName FROM Customers;
   

   

   Figure 35.14. The row source for the customers combo box in Design view (a), and in SQL view (b).

4. Close the SQL Statement window, and save your changes; then switch to Form view, and click the arrow for the Customer combo box. An empty list is displayed (see Figure 35.15). If you select a row in the empty list, all customers are returned.

You now have two SQL statements. The first SQL statement (currently saved on the clipboard) produces a list with values in the CustomerID and CompanyName fields, while the second SQL statement produces a list with null values in these fields. You can combine the two lists into a single list and eliminate the duplicate null rows, so that the combined list has a single null row followed by the values.

Figure 35.15. The Datasheet view for a query that returns null rows.

Figure 35.16. Using null values to display an empty list (click picture for larger image).

Creating the combined list as a union query

You construct a union query by entering the expression for the SQL statement (without the final semicolon) that you want first in the combined list,entering the UNION operator, and then entering the expression for the SQL statement that you want second in the combined list. The final statement must end with a semicolon.

1. Switch to Design view for the Order Status form, and select the Customer combo box. Click in the RowSource property, click the Build button, and choose View . . . SQL View. To display the null row first in the list, use the displayed expression as the first SQL statement.
2. Delete the semicolon at the end of the SQL statement, type UNION, and press Ctrl+V to paste the SQL statement that you copied earlier. Delete the ORDER BY clause, including the Customers.CompanyName that follows ORDER BY. The final expression for the RowSource property is shown in Figure 35.17a. The Datasheet view for this union query is shown in Figure 35.17b.
3. Close the SQL Statement window, and save your changes.
4. Switch back to Form view, and click the drop-down arrow for the Customer combo box. The first row is the null row.

Figure 35.17. The SQL statement for the union query to start the customer list with a null row (a), and the Datasheet view for the union query (b).

Adding a Null Row to Another Combo List

Use the same procedure to modify the SQL statement for the row source for the Employees combo box. This time the ORDER BY clause is needed to ensure that the final list is in ascending order by name. Figure 35.18 shows the final result.

Figure 35.18. The SQL statement for the union query to start the employee list with a null row (click picture for larger image)

Testing the modification

With each combo box displaying a null row, the multiple criteria selection process is much more powerful.

1. Select a customer from the Customer combo list. All orders for the customer are displayed.
2. Select an employee from the Employee combo list. Only orders for the customer that are handled by this employee are displayed.
3. Select the null row in the Customer combo list. All orders handled by the selected employee are displayed.
4. Select the null row in the Employee combo list. All orders are displayed.

You can continue to modify the frmOrderStatus form with combo boxes for each of the other fields you want to use to select records.

Finding Records to Print

The written reports you generate are important products of your application. In an order entry database, for example, you need to print invoices for individual orders, and the shipping department needs a printed order status for review.

This section shows you how to automate the selection of records for reports and the printing of reports. In Chapter 33, you learned how to generate a report for a single record. Now you'll learn how to generate a report for a group of records using two different methods to select the records:

• Select and display the records on a form and print a report synchronized to the form.
• Use a custom dialog to collect selection criteria from the user when the report isn't based on records displayed in a form.

Printing a Group of Records Based on the Current Form

After reviewing the status of orders using the frmOrderStatus form and selecting orders using the selection combo boxes, you can print out a report for the selected orders. First, create a simple tabular report, named rptOrderStatus, using the Report Wizard. We'll base the report on the same query that the frmOrderStatus form is based on so we can use the same filter query for both the form and the report.

1. Start the Report Wizard and then choose the qryOrderStatus query in the first screen. Select the OrderID, OrderDate, RequiredDate, ShippedDate, Shippers_ CompanyName, Customers_CompanyName, and LastName fields. Click Next to accept the defaults in the next four wizard screens and then choose the Formal style. In the last screen, set the report title to rptOrderStatus, select the Modify the report's design option button, and click Finish.
2. Set the Caption property of both the report and the label in the report header to Order Status
3. Rearrange the controls and change the Caption properties for the labels as shown in Figure 35.19.
4. Save and close the report.

Even though the frmOrderStatus form and the Order Status report have the same record source, when you open each object, Access creates a separate recordset for each.

1. Open the frmOrderStatus form in Form view, and select orders using the selection combo boxes.
2. In the Database window, double-click the rptOrderStatus report to open it in Print Preview. The report displays all of the orders, not just those selected using frmOrderStatus. The form and the report have the save RecordSource property setting, but each object opens with its own independent recordset.

Figure 35.19. An Order Status report created using the Report Wizard (click picture for larger image).

Design the macro to synchronize the report to the form

Because the frmOrderStatus form and the rptOrderStatus report are based on the same record source, you can synchronize the report to the form by applying the same filter query to the report that you used to select records for the form qfltOrderStatus. You can automate the process by placing a command button named cmdPrint in the form header and creating a macro that applies the filter when the report is opened (see Table 35.6).

Table 35.6. A Macro to Open and Synchronize a Report

Macro Name	Action	Action Arguments
cmdPrint_Click
	OpenReport	Report Name: rptOrderStatus
		View: Print Preview
		Filter Name: qfltOrderStatus

Before printing a report, however, we want to determine if there are any records to print. There are a few ways to determine whether there are any records in the selection. One way is to use the report'sNoDataevent to determine if there are any orders for the customer handled by the specified employee. TheNoDataevent occurs when Access recognizes that the record source for the report has no records. If the recordset is empty, the report recognizes theNoDataevent immediately following its Open event and before its Activate event. You'll use theNoDataevent to trigger another macro, one that displays a message and then executes the CancelEvent action to cancel the subsequent steps that Access would take to format and print the report with no records. The macro determines whether one or both combo boxes have null values and displays different messages, depending on which combo box combination you've selected:

• Both a customer and an employee
• A customer but not an employee
• An employee but not a customer
• Neither a customer nor an employee

You can create messages that refer to the selected customer and employee by concatenating the combo box values with message text.

The flow diagram for the cmdPrint_Click macro (which runs when you click the Print button) and the Report_NoData macro (which is triggered when the recordset is empty) is shown in Figure 35.20. Table 35.7 shows the Report_NoData macro. The combo boxes on the frmOrderStatus form hold the values of the CustomerID and the EmployeeID and display the values of the CompanyName and the employee's name. We want the macro's messages to use the values displayed by the combo boxes. The displayed values are in the second column of each combo list, so we use the column property to specify the second column. Because the column property is zero-based, we use Column(1) to specify the second column.

Figure 35.20. A flow diagram for two macros: a macro that selects records for a report by applying a filter and a macro that stops the print process and displays separate messages when there are no orders for the selected customer or employee.

Table 35.7. The Macro Triggered by the Report'sNoDataEvent

Macro Name	Condition	Action	Action Arguments
Report_NoData
	Not IsNull(Forms! frmOrderStatus!cboCustomer) And Not IsNull(Forms!frmOrderStatus!cboEmployee)	MsgBox	Message: ="There are no orders for the customer named " & Forms!frmOrderStatus!cboCustomer.Column(1) & " handled by the employee named " & Forms!frmOrderStatus! cboEmployee.Column(1)
		MsgBox	Message: ="There are no orders for the customer named " & Forms!frmOrderStatus!cboCustomer.Column(1)
	IsNull(Forms!frmOrderStatus!cboCustomer) And Not IsNull(Forms! frmOrderStatus! cboEmployee)	MsgBox	Message: ="There are no orders handled by the employee named " & Forms!frmOrderStatus! cboEmployee.Column(1)
	IsNull(Forms!frmOrderStatus!cboCustomer) And IsNull(Forms! frmOrderStatus! cboEmployee)	MsgBox	Message: There are no orders.
		CancelEvent

To place a Print button on frmOrderStatus and then create a macro to open and synchronize the Order Status report to the form by applying the form's filter to the report, follow these steps:

1. Click the mfrmOrderStatus macro sheet, enter the macro in Table 35.6, and save the macro sheet. Next, you'll create the macro from Table 35.7 that displays a message and then cancels the printing operation when there are no records.
2. Open a new macro sheet named mrptOrderStatus, enter the macro shown in Table 35.7, and save the macro sheet.
3. Open the frmOrderStatus form in Design view. Place a command button in the form's header section; set the Name property to cmdPrint and the Caption property to &Print.
4. Assign the mfrmOrderStatus.cmdPrint_Click macro to theOnClickevent property of the command button. Save the form and then switch to Form view.
5. Select the first customer and the employee named Margaret Peacock; then click the Print button. The report for these orders is displayed.
6. Switch to Design view of the report, click in theOnNoDataevent for the report, and select the mrptOrderStatus.Report_NoData macro.
7. Save and close the report.
8. Select the customer named Bottom Dollar Market and the employee named Laura Callahan; then click the Print button. Access displays a message telling the user that there are no orders (see Figure 35.21). Click OK and then make several other selections to display the messages.
9. Close the rptOrderStatus report.
10. Close the frmOrderStatus form.

Figure 35.21. The message displayed when there are no orders for the selected customers handled by the selected employee.

Using a Custom Dialog to Select Records

Instead of using a form to select and display the records that you want to print, you can create a custom dialog to collect selection criteria from the user and use these values as criteria for a filter query that selects the records for the report.

Tip:   The custom dialog technique is useful for routine reports when there is no need to view the records in a form before printing the reports; the performance of your application improves because using the custom dialog eliminates the time used to display the form.

Tip:   The Customer Labels dialog form enables you to select customer labels for all countries or for one specific country (see Figure 35.22a). The form is powered by macros in the Customer Labels dialog macro sheet. These macros use the Where Condition argument of the OpenReport action to select records. The Customer Labels report can be opened independently to list labels for all customers.

The NwindMacros2002 application uses customer dialog forms to collect user input as follows:

• The Sales by Year Dialog form enables you to specify a date range, and whether the report should be a detail or summary report (see Figure 35.22b). The Sales by Year report is based on the Sales by Year query, which uses Query By Form to obtain its criteria from the Sales by Year Dialog form; this means that you can't run the Sales by Year report independently and you must specify a date range using the Sales by Year Dialog form. (The report is powered by VB procedures that format the report depending on whether you checked the Show Details check box on the dialog form.)
• The Sales Reports Dialog form lets you specify any of three reports (see Figure 35.22c). The Employee Sales by Country report is based on a parameter query and displays default input boxes to collect user input for the date interval for the report, the Sales Totals by Amount report requires no user input, and the Sales by Category report uses the Where Condition argument of the OpenReport method to select records.

These custom dialog forms use two basic techniques to select records:

Parameter query as the record source

In this technique the report's record source is a parameter query that requires input either from a default dialog or custom dialog. In this technique the report cannot be run without entering input into the dialog.

OpenReport macro action

In this technique the OpenReport macro action (or theOpenReportmethod) uses theWhere Conditionargument to select records based on user input in the custom dialog. In this technique, the report can be run independently without displaying a dialog because the report's underlying record source is not used to specify selection criteria.

Figure 35.22. The NwindMacros2002 database uses custom dialogs to select customer labels (a), to specify a date range for a sales report (b), and to specify one of three sales reports (c).

The second technique is more flexible because it allows the report to be used with or without a custom form. Recall that there are two action arguments for the OpenReport action that you can use to restrict records: theWhere Conditionand theFilter Namearguments. When the selection criterion is based on a single expression, such as

Country = Screen.ActiveForm.[Select Country]

theWhere Conditionworks well; however, when the selection criteria become more complicated, it is easier to create a filter query and use theFilter Nameargument to apply the filter instead. The filter query obtains its selection criteria from the custom dialog form using Query By Form.

These custom dialog forms use two basic techniques to select records:

Parameter query as the record source

In this technique the report's record source is a parameter query that requires input either from a default dialog or custom dialog. In this technique the report cannot be run without entering input into the dialog.

OpenReport macro action

In this technique the OpenReport macro action (or theOpenReportmethod) uses theWhere Conditionargument to select records based on user input in the custom dialog. In this technique, the report can be run independently without displaying a dialog because the report's underlying record source is not used to specify selection criteria.

The second technique is more flexible because it allows the report to be used with or without a custom form. Recall that there are two action arguments for the OpenReport action that you can use to restrict records: theWhere Conditionand theFilter Namearguments. When the selection criterion is based on a single expression, such as

Country = Screen.ActiveForm.[Select Country]

theWhere Conditionworks well; however, when the selection criteria become more complicated, it is easier to create a filter query and use theFilter Nameargument to apply the filter instead. The filter query obtains its selection criteria from the custom dialog form using Query By Form.

As an example, we'll create a custom form to select records for a new report that displays sales information by customer and by employee. The form provides choices for the customer and employee, and then prints only the orders corresponding to the selection. In this section, you'll create a custom form to select orders for all customers and a specific employee, one customer and all employees, one customer and one employee, and all customers and all employees.

Create the new report as follows:

1. Open the Sales by Category query in Design view and then drag the CustomerID and EmployeeID fields from the Orders table to the design grid. CustomerID and EmployeeID are lookup fields; when included in the report, the CustomerID field looks up and displays the CompanyName, and the EmployeeID field looks up and displays the employee's LastName.

2. Check the criteria in the OrderDate column of the query to make sure that the date range there will pick up records in your copy of NwindMacros2002. The Sales by Category query in the Northwind database that comes with Access looks for records in the 1990's. The database includes orders in that time frame. The same query in the NwindMacros2002 database on the CD in the back of this book looks for dates in 2001. The dates for the order records in the CD database have been changed so the earliest records fall in 1999 and extend into 2001.

3. Save the new query as qrySales.

4. Use the AutoReport: Tabular Wizard to create a new report based on the qrySales query and then save the report as rptSales. Switch to Design view. Delete the CategoryID control and its label. Change the report's Caption property and the Caption property of the title label in the report header to Sales by Customer and Employee. Rearrange the controls as shown in Figure 35.23.

   

   Figure 35.23. The rptSales report

Create a Filter Query for the Report

When you are working with a form, you can create a filter while the form is in Form view because the filter commands (Advanced Filter/Sort, Apply Filter/Sort, and Remove Filter/Sort) are available. However, the filter commands are not available for a report, so you work with the report's underlying record source to create the filter.

A filter query must include all of the tables that contain fields in the report or form that you are applying the filter to and all of the fields in the report or form. When you create the filter query, either drag all of the fields in the report's (or form's) field list to the design grid, or set the query's OutputAllFields property to Yes to show all of the fields in the query's data source. With the OutputAllFields property set to Yes, the only fields that must be shown in the filter query's design grid are the ones you are using to sort by or specify criteria for. (When you save a filter as a query, the OutputAllFields property is automatically set to Yes.)

Create a filter query based on the report's record source

After determining the report's record source, you create a filter query to specify criteria for the Customer and Employee fields.

1. In the Database window, select the query qrySales, and click the arrow on the New Object button in the toolbar; then select the Query button, and click OK. The query design window opens with the field list for qrySales in the upper pane.

2. Choose View . . . Properties, right-click in the upper pane and select the Properties command, or click the Properties button on the toolbar. Click in the OutputAllFields property and then choose Yes. With the OutputAllFields property set to Yes, the datasheet includes all of the query fields, regardless of which fields are displayed in the design grid. In this example, you'll use the Customer and Employee fields to collect the user's choices.

3. Select the CustomerID and EmployeeID fields and then drag them to the design grid. The selection criteria for these fields will come from the custom dialog that you create next.

4. Save the query as qfltSales (see Figure 35.24).

   

   Figure 35.24. When you create a filter query, set the OutputAllFields property to display all the query fields.

Creating a Custom Dialog

We'll create a custom dialog form and set the form properties so that the form has the look and feel of a standard Windows dialog box, with one exception: While you are developing the application, let the PopUp property remain at the default No value so you can switch between Design and Form view, and use the menus and toolbars. When you are finished with the development stage, you can set the PopUp property to Yes to prevent access to the menus and toolbars.

1. Create a new blank form named fdlgSales, and set the Caption property to Select Sales by Customer or Employee.
2. Set the following custom dialog form properties:

   Property	Setting	Property	Setting
   Default View	Single Form	Border Style	Dialog
   Views Allowed	Form	Modal	Yes
   Scroll Bars	Neither	Control Box	No
   Record Selectors	No	Min Max Buttons	None
   Navigation Buttons	No	Close Button	No
   Auto Center	Yes	Shortcut Menu	No

You'll want to place unbound list boxes for customer and employee selection on the form. Design both list boxes to display an empty first row using the techniques you learned earlier, in the section, "Using Multiple Criteria to Select a Group of Records." When you choose the null value for a list box, the filter query returns records with all values for that list box. In fact, we can reuse the combo boxes for the frmOrderStatus form; we'll paste them to the form, change them from combo boxes to list boxes, and delete the macros that are pasted along with the combo boxes. (If you didn't create the combo boxes earlier, you can create them now.)

1. Open the frmOrderStatus form in Design view, select the Customer and Employee combo boxes and their labels, and copy them to the clipboard. Close the form.
2. Click into the dialog form and then paste the clipboard contents.
3. Select the Customer combo box and then choose Format . . . Change To . . . List Box. Change the Name property to lstCustomer, and delete theAfterUpdateevent property setting.
4. Similarly, change the Employee combo box to a list box, change the Name property to lstEmployee, and delete theAfterUpdateevent property setting.

Placing command buttons on the dialog

Place command buttons to display the report in Print Preview, print the report directly, and close the form.

1. Place three command buttons on the fdlgSales form, and set the following properties for the two buttons:

   Name	Caption
   cmPreview	Print Preview
   cmdPrint	&Print
   cmdClose	&Close

2. Switch to Form view, choose Window . . . Size to Fit Form, and choose File . . . Save (see Figure 35.25).

   

   Figure 35.25. The custom form to select customers or employees

Setting Criteria for the Filter Query

The selection criteria expressions for the qfltSales filter query are based on the values you select in the list boxes. If you don't select a customer, or if you select the null value, the filter query returns sales for all customers. If you don't select an employee, or if you select the null value, the filter query returns sales for all employees. The criteria expressions are similar to those you created earlier to select records for the frmOrderStatus form depending on the values in the two combo boxes.

1. Open the qfltSales query in Design view, click the Criteria cell for CustomerID, and type the expression

   Forms!fdlgSales!lstCustomer Or Forms!fdlgSales!lstCustomer Is Null
   

2. Click in the Criteria cell for EmployeeID and then type the expression

   Forms!fdlgSales!lstEmployee Or Forms!fdlgSales!lstEmployee Is Null
   

3. Save the query.

Creating the macros for the buttons on the dialog

The fdlgSales dialog collects criteria for selecting one set of sales records to print. When you click the Print Preview button, a macro hides the form, opens the rptSales report, and applies the filter query to the report's underlying query. Because the form must remain open to provide the selection criteria to the filter query, you hide rather than close it. When you close the report window, another macro triggered by the report's Close event unhides the form. Table 35.8 shows the macro for the Print Preview button, which opens the report, and the macro for the report, which unhides the form.

Table 35.8. The Macros for the Print Preview Button and to Unhide the Form

Macro Name	Action	Action Arguments
CmdPreview_Click
	SetValue	Item: Screen.ActiveForm.Visible
		Expression: No
	OpenReport:	Report Name: rptSales
		View: Print Preview
		Filter Name: qfltSales
Report_Close
	SetValue	Item: Forms!fdlgSales.Visible
		Expression: Yes

The macro for the Print button doesn't display the report, so the macro opens the rptSales report without hiding the form and then applies the filter query to the report's underlying query. The macro for the Close button uses theCloseaction to close the form. Table 35.9 shows the macros for the Print and Close buttons.

Table 35.9. The Macro to Print the Report Without Displaying It

Macro Name	Action	Action Arguments
cmdPrint_Click
	OpenReport:	Report Name: rptSales
		View: Print
		Filter Name: qfltSales
CmdClose_Click
	Close

1. Open a new macro sheet, and save it as mrptSales. You store the macro triggered by the report's Close event in this macro sheet. Enter the Report_Close macro from Table 35.8.
2. Save and close the macro sheet.
3. Open a new macro sheet, and save it as mfdlgSales. You store the macros for the cmdPreview and cmdPrint buttons in this macro sheet.
4. Enter the cmdPreview_Click macro from Table 35.8 and both macros from Table 35.9; then save the macro sheet.
5. Assign the cmdPreview_Click, cmdPrint_Click, and cmdClose_Click macros to the OnClick event property of the cmdPreview, cmdPrint, and cmdClose command buttons respectively.
6. Save the dialog form and then switch to Form view.

Attach and test the macros

1. Select a Customer from the first list box, and click the Print Preview button. The report opens in Print Preview with the selected sales information.
2. When the Print Preview window opens, switch to Design view and then set the report's OnClose property to mrptSales.Report_Close.
3. Save the report, and switch to Report Preview.
4. Close the report. The fdlgSales form is unhidden.
5. Click the Print Preview button without making any list box selections. The report opens in Print Preview with all sales.
6. Close the report.
7. Select the Alfreds Futterkiste customer and the employee named Fuller; then click the Print Preview button. There are no sales for this combination, so a report with no data is generated.

Creating a macro for theNoDataevent

You can use theNoDataevent, recognized by the rptSales report when there are no sales records, to trigger a macro that displays a message, and cancels the printing process (see Table 35.10).

Table 35.10: A Macro to Cancel the Opening of a Report

Macro Name	Action	Action Arguments
Report_NoData
	MsgBox	Message: There are no sales records for this customer handled by the selected employee.
	CancelEvent

1. Open the mrptSales macro sheet and enter the macro in Table 35.10. Close and save the macro sheet.

2. Click the rptSales report and then switch to Design view. Click the NoData event and select the mrptSales.Report_NoData macro.

3. Save and close the report.

4. Select the Alfreds Futterkiste customer and the employee named Fuller; then click the Print Preview button. Access displays the message (see Figure 35.26), cancels the printing process, and unhides the form.

   

   Figure 35.26. The message displayed when the report has no data

Modifying a Group of Records

You can automate the modification of groups of records in two ways:

Action queries

You can create an action query to add, delete, or update groups of records; then use the OpenQuery macro action to run the query.

SQL Statements

You can create an SQL statement to change a group of records, and use the RunSQL macro action to run the SQL statement.

Normally you get better performance by creating the action query in query Design view, saving it, and using the OpenQuery macro action to run the query. When you save a query, Access analyzes the query and then saves an optimized execution plan, along with the query, so it can run the query in the most efficient manner later. By contrast, when you use the RunSQL macro action to run an SQL statement, the SQL statement is optimized on-the-fly each time you run it.

Using the OpenQuery Action to Run an Action Query

When you are creating a new record that has fields with the same values as those in previous records, you can speed data entry by having the fields filled in automatically. In Chapter 34, you used a macro to set the DefaultValue property for controls whose values you want to carry to the new record. Those settings are temporary and cease to exist when you close the form. Sometimes you want the most recent values entered in a data entry session to be saved when you close the form or the database, and then displayed in the data entry form the next time you open the form. In this case, the values that you want to carry to the new record must be stored in a table so they can be stored when you close the database.

Carrying values forward to the next data entry session

To save values between data entry sessions and carry the values to the new record in the next data entry session, we'll use a table called the new record table. We'll also use a macro to run action queries that move the values from the new record table to the Products table, and blank out any values in the new record table that don't need to be carried forward in the next data entry session.

As an example, we'll set up the technique for new products in the NwindMacros2002 database. We'll create a new form for data entry and use it to carry forward values in the SupplierID, CategoryID, and QuantityPerUnit fields. You could modify the Products form, but it is easier to create a new form.

Creating the new record table

The tblNewProduct table stores only a single record and doesn't need a primary key. Because we don't want to carry the ProductName forward to the new record, the ProductName field in tblNewProduct will be null after the macro runs. This means that we must change the Required property to No so we can save the record to tblNewProduct table. We can make sure that the record that we save to the Products table has a non-null value for the ProductName field by validating the field using a macro instead of validating the field using the Required property.

1. In the Database window, copy the Products table and then paste it (structure only) as tblNewProduct. The new record table is designed to contain only a single record—either the new record you have entered but not yet saved, or a record with values you are carrying forward.
2. Open the table in Design view. Remove the primary key index for the ProductID field, and change the field type to Number. Click the ProductName field and then set the Required property to No. Save the table.

Creating the data entry form

Next, we'll create a new form for entering data into the tblNewProduct table.

1. In the Database window, copy the Products form and then paste it as frmNewProduct. Open this new form in Design view, delete the BeforeUpdate property setting you added in Chapter 34, and set the following properties:

   RecordSource	TblNewProduct
   Caption	NewProduct
   NavigationButtons	No

2. Place a command button in the header of frmNewProduct, set the Name property to cmdSave, and the Caption property to &Save.

3. Select and delete the ProductID control and its label. The macro takes care of setting the value of the ProductID control, so this control doesn't need to appear on the data entry form.

4. Save the form, and switch to Form view (see Figure 35.27).

   

   Figure 35.27. The new data entry form for products has the temporary table for its record source.

Creating the action queries

Next you need to create two queries: an append query to append the new data record to the Products table, and an update query that sets to null those data fields that you don't want to carry forward.

1. Create a new query based on tblNewProduct. Use the asterisk method to select and drag all the fields to the first Field cell of the query. Click the Query type button in the toolbar and then choose Append. Enter Products as the name of the table to append the data to, and save the query as qappProduct (see Figure 35.28a).

2. Create a second query based on tblNewProduct. Drag all fields except SupplierID, CategoryID, and QuantityPerUnit to the design grid. (You should drag to the design grid all of the fields for which you are not carrying values to the new record.)

3. Click the Query type button in the toolbar and then choose Update. Enter Null in the Update To cell of each field. Save the query as qupdProduct (see Figure 35.28b).

   

   Figure 35.28. The append query appends data to the Products table (a), and the update query sets to Null the fields whose values are not carried forward (b).

Creating the macro

Next, you'll create a macro for the cmdSave button on the frmNewProduct form. The macro validates the data, sets the primary key value, saves the record to the tblNewProduct table, and then runs the action queries (see Table 35.11). The macro validates the data by testing for an entry in the ProductName field. If the field is null, the macro displays a message, places the insertion point in the ProductName control, and terminates. If the field is not null, the macro sets the ProductID value to a number that is one greater than the largest ProductID value in the Products table. (The SetValue action uses theDMax()domain aggregate function to determine the largest value in the table). The macro uses the SetWarnings action to turn off the default confirmation messages that Access displays before running an action query, and then runs the queries.

Table 35.11. A Macro to Fill a New Record with Data from a Previous Record

Macro Name	Condition	Action	Action Arguments
cmdSave_Click
	IsNull(ProductName)	MsgBox	Message: You must enter a product name before saving the record.
	…	GoToControl	ProductName
	…	StopMacro
		SetValue	Item:ProductID
			Expression: DMax("Product ID","Products") + 1
		SetWarnings	Warnings On: No
		RunCommand	Command: SaveRecord
		OpenQuery	Query Name: qappProduct
		OpenQuery	Query name: qupdProduct

1. Open a new macro sheet named mfrmNewProduct, and create the macro shown in Table 35.11.
2. Assign the macro to the Click event of the cmdSave button, save the frmNewProduct form, and switch to Form view.

Testing the technique

To test the technique:

1. Open the frmNewProduct form, enter data for a new product (see Figure 35.29a), and click the Save button. If you entered a value in the ProductName control, the record is saved to the Products table, and frmNewProduct displays a new record with the specified fields filled in with values from the previous record (see Figure 35.29b). If you didn't enter a value in the ProductName control, the custom message is displayed and the macro terminates (see Figure 35.30).
2. Open the Products table, and verify that the new record has been added.
3. Open the tblNewProduct table, and observe the single record storing the values that we want to carry forward.

Figure 35.29. Use the new data entry form to enter a new record into the temporary table (a). After you save the new record, the three values are carried forward (b).

Figure 35.30. This message appears if you don't enter a required value.

You have now completed the chapters that cover macro programming. These chapters have covered the most important macro techniques and have demonstrated the fundamental approach to macro programming:

You can design most macros by observing the interactive steps you take to accomplish a task and then translate your steps to macro instructions.

You now have the skills to modify the techniques presented in this book and to create your own techniques. If you are new to programming, you should spend several weeks, or maybe even months, using macros to automate your databases. When you are thoroughly comfortable with macros and have a good understanding of the events that objects recognize, you'll be ready to continue on with VB programming. See Access 2000 VBA Handbook, by Susann Novalis and Dana Jones (Sybex, 1999) to expand your knowledge in this area. Good luck with your Access programming endeavors!