Run a report for a selected record

[Jackie]

What is the VBA command for running a report for a selected record or group of selected records?

I am writing an Application in which the user would like to run a report for the currently selected record(s) in a continuous form.

Thank you in advance for your help.

-Jackie

 

[ajdesalvo]

Use the Where clause of the DoCmd.OpenReport method:


DoCmd.OpenReport "YourReport", , , "[ReportFieldName] = '" & Me![FieldName] & "'"

Good Luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[Jackie]

This works well for printing the report for only 1 record. What if the user has selected multiple records?

Thanks for responding.

-jackie

 

[Norris68]

The WhereClause parameter is just that - a SQL WHERE clause without the WHERE keyword. So, we will need to build the clause to pass to the report. When selecting multiple records, it is usually best to use an IN clause, especially when you are selecting records by an integer primary key (e.g. autonumber or Long PK).
The syntax would be {WHERE} "PrimaryKeyID IN (1,2,3,4,5)"

So within your VBA code that opens the report, loop through your selected records and build a string. I'm not sure how to loop through selected records in a subform - it's usually MUCH easier to use a ListBox (which is designed for the job).
Code sample using ListBox that has the PrimaryKeyID in the first (hidden) column:

Dim A As Long
Dim SQL As String

SQL = "PrimaryKeyID IN ("
For A = 0 To lstItems.ItemsSelected.Count - 1
    SQL = SQL & lstItems.ItemsSelected(A)
    If A < lstItems.ItemsSelected.Count - 1 Then
        SQL = SQL & &quot;, &quot;
    End If
Next A
SQL = SQL & &quot;)&quot;

MsgBox SQL
'DoCmd.OpenReport &quot;MyReport&quot;, acViewPreview, , SQL

 

[Jackie]

Thank you. itemsSelected was what I was looking for. You have been most helpful.

-jackie