?
Solved

Open Report based on items in Multiselect listbox

Posted on 1998-08-12
3
Medium Priority
?
318 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 680 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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

569 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