Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

Open Report based on items in Multiselect listbox

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
0
gitarsteve
Asked:
gitarsteve
1 Solution
 
ramromconsultant Commented:
If the listbox really contains the numbers 1-20 then you can use For Each ...In ItemsSelected to construct a comma separated list of boring numbers, e.g. 1,3,5, then apply that list to the sql e.g.And [BoringNumber] in (1,3,5). I see no need for the select case.

Dim s as string, intBoringNo as variant
For Each intBoringNo in BoringNumber.ItemsSelected
  s = s & intBoringNo & ","
Next
If len(s) > 0 Then ' at least one item was selected
  s = left(s, len(s) - 1) ' remove trailing comma
  strSQL = "SELECT * FROM [Soil Details] WHERE [CustomerID] =" & Me.CustomerID & " And [BoringNumber] in (" & s & ")"  
  strDocName = "Soils Report"
  DoCmd.OpenReport strDocName, acViewPreview, strSQL
  DoCmd.Maximize
0
 
tomookCommented:
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.
0
 
gitarsteveAuthor 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.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now