Print Report based on Multi-select ListBox

[aecarpin]

I have been struggling for days to achieve this simple procedure.
I want to be able to print/preview a report based on the selection made from a multiselect List Box. I've tried using some of the code sample from this forum
but can't quite manage to get it to work. The varItem seems to represent the row number of the ListBox items rather than the CID value in the query.
What do I need to change or add for the report to pickup the selected company.


Private Sub Form_Load()

'Fill the listbox using a saved query.
Me.LstFindings.RowSource = "AllCompany"

End Sub

Private Sub Select_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Forms!frmcomp
Set ctl = frm!LstFindings
strSQL = "Select * from AllCompany where [CID]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [CID]="
Next varItem

'Trim the end of strSQL
strSQL = Left(strSQL, Len(strSQL) - 10)

'This statement doesn't work. Report Runtime error
DoCmd.RunSQL strSQL
End Sub

 

[Exodus]

Try using a query with criteria

base the criteria of the fileds of your report

Like IIf(IsNull([Forms]![FrmReportCriteria]![Serial]),"*",[Forms]![FrmReportCriteria]![Serial])

 

[WayneRyan]

ae,

strSQL (after evaluation) is equal to something
like this:

Select * 
from   AllCompany 
where  [CID] = 12345 Or Or [CID] = 45678

But, you can't do --> DoCmd.RunSQL strSQL
RunSQL performs action queries (Insert/Update/Delete).
You can't do a Select query because there's no place to
"put" the resulting data. This is a RowSource or
RecordSource and is incompatible with DoCmd.RunSQL.

Since it includes the SELECT, you need to make it a query.
Since it changes, you need a QueryDef. You need to do
something like:

Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("SomeQuery")
qdf.sql = strSQL <-- This is the strSQL that you build in your code
qdf.close

Then just base your report on "SomeQuery" and that should do it.

Another way to accomplish this is to remove the "Select ..." from
strSQL and just use the part after "Where ...". Then when you
launch your report:

DoCmd.OpenReport "YourReport",,,strSQL

A last note, since you initially set strSQL to "Select ... Where [CID] = ".
That means that there must be at least one item selected in the listbox
or you'll get an error.

One last note, instead of "[CID] = 12345 Or [CID] = 45678 Or ..."
This might be better: "[CID] In (12345, 45678..."


Wayne