Running a report based on selected records

[4656rp]

I have a report that I run using a parameter query. Now I need to run the same report but for selected records. I've tried creating a form with an unbound list box and the multiple select property set to simple so I can select several records. When I click on the print or preview command buttons I get the entire report. What am I missing?
Is using a unbound list box the best way to go?

Thanks in advance....

rp

 

[BillPower]

I think you will need to create a new table to add the records selected in your list for your report. To do this, create a new table, add a Field called RecordNo(Change to whatever your Record ID is) formatted the same as the Field RecordNo in your Main table. Then use the code in the example below:

On Error Resume Next
Dim dbs As Database
Dim rst As Recordset
Dim ctlSource As Control
Dim intCurrentRow As Integer
Dim lngRecordNo As Long 'change variable type if necessary
Dim strSQL As String
Set ctlSource = Me!YourListName 'change YourListName to your list
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("YourTableName", dbOpenDynaset) 'change YourTableName

strSQL = "Delete RecordNo FROM YourTableName;" 'change YourTableName and RecordNo if necessary
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
lngRecordNo = ctlSource.Column(0, intCurrentRow)
rst.AddNew
rst!RecordNo = lngRecordNo 'change RecordNo if necessary
rst.Update
End If
Next intCurrentRow

Add the new table to the report's query, create an Inner Join between the 2 RecordNo's (or whatever, RecordID etc). You should see only the records selected in your list in your report now.

Good Luck

 

[PaulBricker]

Have a look at this solution that Bob has posted. It is the best way I have seen to pass multiple values to a query/report. Read both his solutions because it reduces the number of times you have to pass the function call from the query.

thread701-473531

Paul