How to Access VBA Reports
"VBA reports" refer to the reports in a Microsoft Access database that a Visual Basic for Applications (VBA) program manipulates. One operation VBA can perform on Access reports is to create a row summarizing detailed data rows. For example, VBA code in a report can tally a salesperson's individual sales for the month to determine that month's total sales. An essential step in customizing Access reports with VBA is first designing the report with Access's standard (non-programming) interface. Managing Access's reports with VBA allows you to create reports tailored to your work team's exact specifications.
Other People Are Reading
Instructions
-
-
1
Open Access, then click the "Create" menu heading, followed by pressing the "Table" item. This action makes a table of sample data that you'll produce a VBA report from shortly.
-
2
Type "Title" in the new table's top cell of its single column, then type "Cost" in the cell to the right of that cell.
-
-
3
Type a list of two or three names of books in the rows under the "title" column, and type any valid number as sample prices for the books under the "Cost" column.
-
4
Press "Control-S," then type "BookPrices" for the table's name.
-
5
Click the "BookPrices" item in the navigation pane at screen left, then click the "Create" menu heading's "Report" button to create a new report based on the BookPrices table.
-
6
Right-click the report's tab, then click the "Design view" item. This action enables you to attach a VBA program to the report.
-
7
Right-click any blank portion of the new report and select "Properties" to display the "Properties" pane for the report.
-
8
Click the "Event" tab of the "Properties" pane, then click the small downward-pointing arrow to the right of the "On Load" item. This action tells Access that you want to run a VBA program when Access loads the report into memory from your hard drive.
-
9
Click the "..." button to the right of the downward-pointing arrow to enter the VBA integrated development environment.
-
10
Type or paste the following program code above the "Exit sub" statement of the "Report_load" subroutine. The program defines and applies a filter for the report, which sifts through all records to find those meeting the criteria you specify for the filter.
Me.Filter = "cost = 1.23"
Me.FilterOn = True
-
11
Type over the text "1.23" with the cost of one of the records you entered in step 3.
-
12
Press "Alt-F11" to return to the standard Access interface, then right-click the report's tab and select "Report view" to run the report. Access will display only the record whose cost you specified in step 11.
-
1
References
- Photo Credit ballyscanlon/Stockbyte/Getty Images
