Generate Multiple Reports

This blog came about because of an answer I gave to a particular OP’s question. Therefore the code isn’t generic, it is designed for a specific situation, like most examples! However I hope there’s enough information here for you to follow what’s happening.

Multiple Reports - Slide Run Through

Video 5 (7:32)

Please note this is video number “5” this is a recap of what’s been done. This video comes after the 4 videos below, Video’s “1”, “2”, “3” and “4”!

I’ve put it HERE at the beginning to whet your appetite so to speak!

Generating an invoice for each customer, using a “Main Form” to run a Record Set Loop (RSL) the record set loop loops through the SQL statement. The SQL statement is rendered unique by a value collected from the field. The SQL statement gets passed into the forms Public Custom Property. The code opens the Report. The reports Open Event extracts the SQL Statement from the Forms Public Custom Property. The SQL Statement is then passed into the Report’s Record Source.

File - Generate Multiple Reports - Nifty Access

Download Sample Database HERE:-

Video 5 (7:32)

More Info on Multiple Reports:-

… …

Multiple Copies with different Criteria

Multiple - with different Criteria

Video 1 (3:41)

Generate multiple copies of the same report but with individual criteria for each report provided by an SQL statement. The requirement is for Reports with Ascending numbers in the Report Name.

Video 1 (3:41)

… …

Change the Report's Recordsource

Change Recordsource

Video 2 (8:40)

Generate multiple copies of a Report with individual Criteria for each Report. Demonstrates how to change the Record Source of each Report, on each iteration of the Recordset Loop.

Show how to use a Combo Box in the Report to display text from a related lookup table.

Show that the recordsource SQL statement has a different value.

Add a custom property to the form to hold the SQL Statement, the SQL Statement will be generated by a function in the Form.

The Report will then interrogate the form, grabbing the SQL Statement from the custom property.

Demonstrate how to insert Text straight into the Form Module. A Public Custom Property for holding the SQL Statement.

See how to insert text straight into the Report module, a Public Custom Property for holding the name of the Form that Opened the Report.

Shows how the Report On Open event extracts the Form name from OpenArgs.

Demonstrate how you can use the Reports Open Event to use the DoCmd.Close command to close the Form that Opened the Report.

… …

Build the SQL Statement

SQL Statement "fSQL_ForRpt"

Video 3 (11:54)

You may have spotted that the videos are numbered out of order! The original set of videos was 1, 2, 3, 4, however I realised that the 4th and last video video served as a “Walkthrough” of what was happening.. It would serve this purpose better as the first video, so I put it at the Beginning, Hence the unusual numbering 4, 1, 2, 3,

Video 3 (11:54)

Pass Info In to a Report

Video 4 (3:41)

The open-args method, the method of passing lots of information in a string, separated by delimiters and passing them through with the openargs, it’s quite a challenging method of handling data. The method explained in this video is far superior. This method makes the data available in the “Calling Form” the Form that is Opening the Report. The Report is directed to extract the necessary information from the “Calling Form”. With this method you have a greater degree of control over the information. It’s logical, it’s easy, especially if you need to add or remove information.