Solved

Open Report based on items in Multiselect listbox

Posted on 1998-08-12
3
281 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now