Beginning Access™ 2007 VBA

9.1. Working with Reports from VBA

Reports can be manipulated in numerous ways from VBA code. For example, you can change the data source on which the report is based from within VBA code. You can also create reports from scratch or modify existing reports from within VBA code. Let's look at a few examples to further illustrate these concepts.

Many of the examples in this chapter that deal with manipulating existing reports use the Northwind database. If you want to create those particular examples yourself, open the Northwind database and add a new module. Only the code for the Try It Out examples is included in the sample code available on Wrox.com for this chapter. If you do not have the Northwind database installed, you can install it and the other sample databases from the Microsoft Access setup program, or you can download it from Microsoft's web site in the Microsoft Office section that contains Access sample databases.

9.1.1. Setting the Report Data Source

The DataSource property of a Report object can be specified as a SQL statement. Suppose that you have an existing report called Alphabetical List of Products. The following procedure illustrates one way you can modify the DataSource of the report:

Sub ModifyExistingReport() 'declare a variable to store the SQL string Dim strSQL As String 'set the SQL statement value to show only the top 5 orders strSQL = "SELECT DISTINCTROW TOP 5 Orders.[Order ID], Orders.[Order Date]," & _ "[Order Subtotals].Subtotal AS SaleAmount, ...