Open Report based on items in Multiselect listbox
Posted on 1998-08-12
I want to open records (on one report) based on items selected in a multiselect listbox.
The listbox contains the numbers 1-20, representing the boring (as in digging a hole) number
One condition is the primarykey (CustomerID) and the other is a user defined unique index (BoringNumber).
The code below works fine for opening all records on a report if criteria 'BoringNumber' is omitted from sql statement,
With BoringNumber in the sql statement, however, it only shows the first record in the recordset even though it cycles through all selected.
Obviously, only one copy of report can be opened at a time and this code opens that one copy.
I need to open the report displaying all the records matching the selections in the listbox.
Example: I select borings 1, 3, 4, 7 from the multi select listbox
the report only brings up boring 1, or the first selection
Here is what I want:
As the report is opening, I need it to test each value in the listbox; and if selected, add the record represented by the selection to the report.
I also like compact code, and would prefer not to have 20 case select statements.
I am probably asking to much.
Set db = CurrentDb
For intCount = 0 To Me.BoringNumber.ListCount - 1
Select Case intCount
'condition or action is same for all items:
Case 0 To 20
'Adjusts counter to actual listbox values (BoringNumber), the numbers 1 - 20:
intBoringNo = intCount + 1
'Check to see if item in multiselect listbox is selected:
If Me.BoringNumber.Selected(intCount) = True Then
'search for records based on two criteria, customerID & BoringNumber:
strSQL = "SELECT ALL * FROM [Soil Details] WHERE [CustomerID] =" & Me.CustomerID & " And [BoringNumber] =" & intBoringNo
Set rst = db.OpenRecordset(strSQL)
strDocName = "Soils Report"
DoCmd.OpenReport strDocName, acViewPreview, strSQL