Solved

Open Report based on items in Multiselect listbox

Posted on 1998-08-12
3
288 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
0
Comment
Question by:gitarsteve
3 Comments
 
LVL 17

Accepted Solution

by:
ramrom earned 170 total points
ID: 1958870
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
 
LVL 4

Expert Comment

by:tomook
ID: 1958871
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
 

Author Comment

by:gitarsteve
ID: 1958872
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question