How to use Multiple select list box for reports

[PMrider]

I'm trying to print in a report only the records that I selected from a multiple select list box. I got it to work if the list box has the multiple select set to none. But its not working if its set to SIMPLE or EXTENDED

Here is the query i'm wanting to use

SELECT T_Name.NameID, T_Name.Name, T_Name.SummerFillEstDueDate, T_Name.Classification, T_Name.DueDate, T_Name.[Account #], T_Name.Address, T_Name.City, T_Name.State, T_Name.PhoneNumber, T_Name.RouteZone, T_Name.[Code/KeyLocation], T_Name.Directions, T_Name.TankSerialNumber, T_Name.InstallDate, T_Name.PressureTestDueDate, T_Name.TankSize, T_Name.TankGallons, T_Name.WinterDailyAvgUsage, T_Name.TankOwnership, T_Name.RouteChronically, T_Name.PressureTestCompletedDate, T_Name.TankMfg, T_Name.RateStatus
FROM T_Name
WHERE (((T_Name.NameID)=[Forms]![F_Name]![List184]));

What do I need to do next?

Alan

 

[930driver]

When using multiselect for a list box the value of the list box is always Null, even if you've selected something. You use the ItemsSelected collection to see what has been selected. You can loop through the collection and perform actions for each selection.

Since you want to print, and you somehow need to get your selected records into the sql statement that your report is based on, I suspect the 'action' that you would perform as you loop through the collection would be to set a 'PrintMe' flag in the appropriate table and then base the sql on that flag. Don't forget to reset the 'PrintMe' flag at some point. Perhaps the easiest place to do this would be at the very beginning of the code that sets the flag in the first place.

There are other ways to do this, but examining the ItemsSelected collection is the only way to see what has been selected in a multiselect list box.

"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[PMrider]

I've looked at the ItemsSelected help in access and am not sure how to apply it to what i'm doing. Can anyone send me a model on how this works. akolln@srt.com

Any help will be greatly appreciated

Alan

 

[930driver]

You should be able to adapt the follow to do what you need. After selecting a few records in your list box run this code and take a peek at the immediate window.

Dim varItem As Variant
For Each varItem In List0.ItemsSelected
'next line returns 'bound' column
Debug.Print List0.ItemData(varItem)
'next line returns specified column
Debug.Print List0.Column(1, varItem)
Next varItem

Replace List0 with the name of your list box. Also, look up Help on .ItemsSelected for more info on why this works.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[PMrider]

I read the .ItemsSelected and tried to use the code above, but I'm not really sure where to put the code. Do I create a command button and put it there? Also how does the items selected on list get to the report without some kind of query for the report to pull the info from? I'm not real good with VB but if anyone has an example database as to how this works then I can get a clue as to what and where things are supposed to go. Thank You

Alan

 

[930driver]

Where to put the code depends on when you want it to run. Once you have the code that you need for your report, the obvious place to put it would be in the button (or whatever event) that calls your report.

For a sample like the above I'd just drop a button on your form and put the code in the click event. Then open the form in Form View and click the button. Delete the button later.

Like I said earlier you will need to set some kind of flag in the records so that you can use that flag as a criteria in the query for your report. You might consider adding a field called PrintMe or something to that effect. Then when you are looping through the .ItemsSelected you can set PrintMe = True. Then for the criteria in your report query you specify WHERE PrintMe = True so that you only return the records that you want.

The very first thing you would do when you are printing the report (before you even loop through your .ItemsSelected collection) would be to run an update query to set all those PrintMe flags to false. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[PMrider]

Thanks 930Driver for your help. I got it to work and this is what I did

I put this in the GENERAL part of the code behind the form

Public Function ListBoxWhereClause(lst As ListBox, strBoundFieldName As String, _
Optional strDelimiter As String = "&quot As String

Dim varItem As Variant
Dim strWhere As String

If lst.MultiSelect = 0 Then
'single-select Listbox
If IsNull(lst.Value) Then
'show all items by using criterion that's always true
ListBoxWhereClause = "1=1"
Else
ListBoxWhereClause = strBoundFieldName & "=" & _
strDelimiter & lst.Value & strDelimiter
End If
Else 'Multi-Select
If lst.ItemsSelected.Count = 0 Then
'REturn all items if nothing is selected
ListBoxWhereClause = "1=1"
Else
'Build IN clause:Cycle thruItems _ Selected to build a
'comma-delimited list of the values in the bound column
With lst
For Each varItem In .ItemsSelected
strWhere = strWhere & ", " & strDelimiter & _
.ItemData(varItem) & strDelimiter
Next varItem
End With
'strip leading ", ".
strWhere = Mid(strWhere, 3)
'complete the syntax
strWhere = strBoundFieldName & " IN(" & strWhere & &quot"
ListBoxWhereClause = strWhere
End If
End If
End Function


Then I made a command button on the form to do this

DoCmd.OpenReport "ReportName", View:=acViewPreview, _
WhereCondition:=ListBoxWhereClause( _
Me.ListBoxName, "FieldName", """&quot

I works like a champ.....Thanks everyone for all the help

 

[930driver]

Always one more way to skin a cat "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"