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 are included in the sample code available on Wrox.com for this chapter.

Setting the Report Data Source

The DataSource property of a Report object can be specified as a SQL statement. Suppose 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 discontinued products strSQL = "SELECT Products.*, Categories.CategoryName " & _ "FROM Categories INNER JOIN Products ON " & _ "Categories.CategoryID = Products.CategoryID " & _ "WHERE Products.Discontinued=Yes;"

'access the report's design (hidden from the user) DoCmd.Echo False

DoCmd.OpenReport "Alphabetical List of Products", acViewDesign

'set the source of the report to the SQL statement Reports("Alphabetical List of Products").RecordSource = strSQL

'close the report's design and save changes DoCmd.Close , , acSaveYes

'now open the report for the user in preview mode DoCmd.OpenReport "Alphabetical List of Products", acViewPreview DoCmd.Echo True

End Sub

Notice first how a SQL statement is created to specify which fields the report should be based upon. The report's design view is then accessed in hidden mode (echo false) so that the user does not see the modifications. The RecordSource is then assigned to the strSQL variable. The report is then closed and the changes saved. Finally, the report is opened in preview mode so you can see the changes.

When you make changes to report properties (such as the RecordSource property), you actually permanently change the report to those settings, just as if you had done so in the designer. You don't just make a temporary change that is undone after the code executes.

You can also specify an ADO recordset as a report's data source. You could modify the prior example as follows:

Sub ModifyExistingReport()

'declare a variable to store the SQL string Dim strSQL As String

'set the SQL statement value to show only discontinued products strSQL = "SELECT Products.*, Categories.CategoryName " & _ "FROM Categories INNER JOIN Products ON " & _ "Categories.CategoryID = Products.CategoryID " & _

'declare and instantiate a new recordset Dim rsDiscontinued As ADODB.Recordset Set rsDiscontinued = New ADODB.Recordset

'open the recordset based on the SQL statement rsDiscontinued.Open strSQL, CurrentProject.Connection

'access the report's design (hidden from the user) DoCmd.Echo False

DoCmd.OpenReport "Alphabetical List of Products", acViewDesign 'set the source of the report to the recordset source

Reports("Alphabetical List of Products").RecordSource = rsDiscontinued.Source

'close the report's design and save changes DoCmd.Close , , acSaveYes

'now open the report for the user in preview mode DoCmd.OpenReport "Alphabetical List of Products", acViewPreview DoCmd.Echo True

'close the recordset and free the memory rsDiscontinued.Close

Set rsDiscontinued = Nothing

End Sub

Notice how this example uses the SQL statement to create the ADO recordset, and then assigns the RecordSource property of the report to the recordset's Source property.

Creating Reports Programmatically

Just as you can work with forms and other Access objects programmatically, you can also work with reports programmatically. You can create reports from scratch and modify existing reports. Let's look at how to create a report from scratch.

0 0

Responses

  • FRE-SWERA
    Can u specify where to save a report using vba in access 2003?
    8 years ago

Post a comment