Access™ 2007 VBA Programmer's Reference

11.7. Common Report Requests

There's a lot that you can do with some imagination and a little VBA code behind your reports. Let's take a look at some of the more common types of reports you can create by adding VBA.

11.7.1. Gathering Information from a Form

There may be times when you want to retrieve some information from a form to provide data to a report. For example, you may want to provide the user with a form to create a filter for the report. You can use the OpenForm method with the acDialog argument to open the form in the report's Open event. acDialog opens the form in a modal view and stops subsequent code from executing. This also prevents the report from opening until the form is closed.

The following is an example of this technique. The code opens a form that contains a unique list of company names in a combo box. When a company is selected, the form is hidden, which runs the remainder of the code in the report. This example also shows how you can change the record source for a report at runtime.

Private Sub Report_Open(Cancel As Integer) Dim strCompany As String ' open the form DoCmd.OpenForm "frmFilterReport", acNormal, , , , acDialog ' get the company strCompany = Nz(Forms("frmFilterReport")!cboCompanies, "") ' set the recordsource for the report If (Len(strCompany) > 0) Then Me.RecordSource = "SELECT [Last Name], [First Name], Company " & _ "FROM tblAttendees " & _ "WHERE Company = '" & strCompany & "'" Else Me.RecordSource = "SELECT [Last Name], [First Name], ...