WORKING WITH QUERIES TO FEED REPORTS AND FORMS

Reports and forms can use QueryDefs or SQL statements as a record source. If the record source will be changed based on complex criteria, it can be helpful to have the report pointing to a QueryDef that contains the SQL statement for the report. VBA code can change the QueryDef before opening the form or report rather than change it during the Form OnOpen or Report OnOpen event.

Creating the QueryDef ahead of time is advantageous because even before the VBA code is written to generate the real QueryDef, you can create a query with the fields that will be used on the final report and begin the report design. This allows you to develop the report and view data from existing tables until the VBA code is written. When the code is written, it simply changes the SQL property of the existing QueryDef and opens the report.

Another advantage of using a QueryDef to drive reports and forms this way is evident if it becomes necessary to debug the record source. Set a breakpoint in the VBA code after closing the QueryDef and before opening the report. When the code is run, it will stop at the breakpoint; pressing F11 will bring up the database container. At this point, open the query or design it to see whether the VBA code generated the expected results.