Create a user interface to select records to print on a report.

[etknalb]

I created a report that uses a parameter query to select records from related tables into a report. It works fine to create a single report at a time. The parameter query prompts for a value when I open the report. It creates the report and I print using Access pulldown menu. I close the report and open it again to create the next report...etc.

I would like to have the user enter as many as 10-15 values to select multiple records to create/print seperate reports for each value.

I have 4 related tables with a key value of CYCLE. One table has revision information, another table has cycle description information, and another table contains detail steps for each cycle. Each cycle could have as many as 15 detail steps. There must be only 1 cycle on each report.

I'm not sure where to start.
Thanks in advance for any suggestions.

 

[randysmid]

Hi,
There is a surprisingly simple solution to this. First, this can be a single report, where everytime the "cycle" changes, it causes a form feed. You can do this with "Sorting and Grouping" (found under the View menu). You would set the "cycle" to be the top level grouping. Then, click on the gray tab to the left of the cycle header, click on View, then Properties. On the format tab, you will see "Force New Page". That is how you can get a new page for each "cycle".

Now, instead of using a parameter query, create a form specifically designed so that the user can select multiple cycle records to retrieve. This can be done with a "listbox" control. Under the Properties view, click on the Other tab and make sure the "Multi Select" is "simple".

You should have a command button on the form to "Run Report(s)". Underneath this button you will need to have code similar to this:

Dim varCycle As Variant, strFilter As String

' loop through each item selected, and append to
' the filter string for use by the report
For Each varCycle In Me!lstCycle.ItemsSelected
strFilter = strFilter & "[CYCLE] = '" & _
Me![lstCycle].ItemData(varCycle) & "' OR "
Next

' Remove "OR" string at end of selected records
If strFilter <> &quot;&quot; Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If

' now, run the report using the strFilter string
DoCmd.OpenReport &quot;rptCycle&quot;, acPreview, , strFilter

By using the &quot;filter&quot;, you are passing the values TO THE REPORT, and the report will then use these to grab the necessary recordset.
If this tip has been helpful, be sure to click on the &quot;Mark this post as a helpful/expert post!&quot;.

Randy Smith
California Teachers Association