We help IT Professionals succeed at work.

Open Report based on items in Multiselect listbox

gitarsteve
gitarsteve asked
on
327 Views
Last Modified: 2006-11-17
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

'set counter:

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

DoCmd.Maximize
End If
End Select
Next intCount
Comment
Watch Question

consultant
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
ramrom's answer will probably work, but IN can be a little slow at times. This one is a little more general purpose:

Add one new variable:
Dim strWhere As String

Change your For loop to:
For intCount = 0 To Me.BoringNumber.ListCount - 1    
    If Me.BoringNumber.Selected(intCount+1) = True Then
        strWhere = strWhere & " OR (BoringNumber = " & incCount + 1 & ")"
    End If
Next intCnt

If Len(strWhere) > 0
    strWhere = Mid$(strWhere, 5) ' Chop off the leading 4 chars since they say " OR "
End If

' Remember to group all the OR clauses in parens.
strWhere = "(" & strWhere  & ") AND ([CustomerID] =" & Me.CustomerID & ")"
strDocName = "Soils Report"
DoCmd.OpenReport strDocName, acViewPreview, strWhere

Note that the DoCmd.OpenReport is not in the For loop, but the resulting report should show all the IDs selected.

Author

Commented:
Superb!! Thank you both.  It could have taken me weeks to figure that out.  Not a whole lot of info on sql out there.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.