Our menu form contains buttons for both detail and summary reports. These reports are based on the qSalesCrosstab query, which summarizes data to the last day of each month and, therefore, doesn’t include individual dates. To implement date criteria that the user inputs on the menu form, criteria need to be in the query that creates the crosstab. We will use VBA to construct the Where clause (to limit records in the query) and then modify the SQL statement that defines the query.
Create a copy of the qSalesCrosstab query and name it “qSalesCrosstabOrig.” Each time a report button is clicked, this query will be used to create the qSalesCrosstab query.
CHANGE MACROS TO VBA
Open the fMENU form in Design view. In the Design ribbon tab, click Convert Form’s Macros to Visual Basic. The “Convert form menus” dialog window will open. The settings will default to add error handling and to include comments. Click Convert. When the conversion is finished, open the Property Sheet and select either of the buttons (btn_Open-MonthSalesDetail or btn_OpenMonthSalesSummary). Note that the On Click property for the button now says [Event Procedure]. To see the VBA code, click on the Builder button next to [Event Procedure]. This will open Microsoft Visual Basic for Applications. You’ll see a collection of procedures—this is called a module, and in this case it’s attached to the fMENU form.
The code begins with a procedure declaration that specifies whether the procedure is a Sub or Function. Subs are procedures that don’t return a value, while Functions do return a value. The first statement that follows sets up the error handler. For example, for the btn_OpenMonthSalesDetail button, the error handler follows the line label btn_OpenMonthSalesDetail_Click_Err: On Error GoTo btn_OpenMonthSalesDetail_Click_Err.
If there’s an error, Access will execute the code following that label. In this instance, the code tells Access to display a message box indicating to the user what’s wrong and then to exit.
The second statement in the VBA code is DoCmd.OpenReport “r_SalesCrosstab_Percentages”, acViewReport, “”, “”, acNormal. In VBA, anything that appears after “DoCmd.” is a macro action. So this statement issues the OpenReport macro action. The first parameter is the name of the report to open, which is the only required argument. (The terms “argument” and “parameter” can be used interchangeably.) In this instance, it’s the r_SalesCrosstab_Percentages report.
The second parameter, acViewReport, tells Access to use the report view that has no page breaks. Other views are acViewPreview (show on screen with page breaks), and acViewNormal (print). If the view isn’t specified, the report will automatically print. The third parameter is the filter name, which is rarely used. The fourth parameter is the WhereCondition. This is often used to limit the report for criteria when the report record source can be used for the conditions. The last parameter is the Window mode. The default mode is Normal, but it can also be Hidden or other choices.
The next lines of code include a line label (which can be used as a reference point when you want the code to go to that point), followed by a command to exit the subroutine. Then comes the error-handling line label and code, followed by the End Sub statement:
btn_OpenMonthSalesDetail_Click_Exit:
Exit Sub
btn_OpenMonthSalesDetail_Click_Err:
MsgBox Error$
Resume btn_OpenMonthSalesDetail_Click_Exit
End Sub
CONSTRUCT CRITERIA AND MODIFY THE CROSSTAB QUERY
Go to the end of the module and enter the code in Figure 1. This new subroutine replaces the existing SQL statement for the crosstab query used by the reports and processes the criteria entered on the fMENU form.
As you type VBA code, IntelliSense prompts you with choices. This is a feature that provides suggestions based on the context. As you begin to type, IntelliSense presents you with a list of options to choose from. You can either press Tab when the option you want is highlighted or double-click the option using the mouse.
Once the procedure to replace the SQL for the crosstab query is done, it will need to be called before each of the reports. To do that, add the following statement on a new line above the OpenReport statement in each subroutine: Call MakeSalesCrosstabQuery.
After you write code, it needs to be compiled. From the menu, choose Debug, Compile Sales. Then click the Save icon. If there are any errors in the code, they will be highlighted so you can fix them.
To test the code, go to the fMENU form and enter a start date of 7/1/2016 with no end date. Click on one of the report buttons. This will show information for the current quarter. If you type both a beginning and end date, only records in that range are displayed in the report. If only an end date is specified, all records before and including that date will be displayed. (Download this month’s database: SF_Sept_2016_VBA_CrosstabQuery.)
September 2016
