View selection from multi list box as report

I have a form with a multi select list box.  The user selects multiple rows in the list and clicks a button to add those records to a table.

Using the following code:
If Me.ListClass.Selected(x) = True Then
        DoCmd.RunSQL ("Insert Into ShowTime1 ([Meeting Date],[Rider], Horse, BackNo, Agroup, Class) VALUES (cboDate,cboRider,cboHorse,txtBack,cboAgeGroup,'" & Me.ListClass.ItemData(x) & "')")

However, I need to print a list of what has been selected and add info from a couple of other fields on the form to provide to the user.

For example:
Date (from text box)
Name (from text box)

Selected Items (rows selected in list):
1
2
14
23

Any help is greatly appreciated!
wlreimerAsked:
Who is Participating?
 
Simon BallConnect With a Mentor Commented:
yes.

for each item in the list, you can check to see if its Nth column = List0.ItemData(item)

And if so - output the columns of your choice

notice my example uses basic control names - list0, text3.

see form1 in attached mdb as an example.
Dim item As Variant
Dim i As Integer
Text3.Value = ""

For Each item In List0.ItemsSelected
    'Text3.Value = Text3.Value & List0.ItemData(item) & vbCrLf
    
    For i = 0 To List0.ListCount
     If List0.Column(0, i) = List0.ItemData(item) Then
     Text3.Value = Text3.Value & "================" & vbCrLf
     Text3.Value = Text3.Value & List0.Column(1, i) & " " & List0.Column(2, i) & vbCrLf
     End If
    Next i
    
Next item

Open in new window

wlreimer.mdb
0
 
Simon BallCommented:
Think this can be done in a loop with a variant...

Dim item As Variant
Dim SelectedValues As String

For Each item In List0.ItemsSelected
    SelectedValues = SelectedValues & List0.ItemData(item) & ","
   
   
Next item

text0.value = text0.value & vbcrlf & SelectedValues
0
 
Simon BallCommented:
forgot to change my sample code from list0 to listclass.

Assuming also that you already know how to pull in your Date (from text box)
Name (from text box) etc...


text0.value = "Date:" & me.txtDate.value & vbcrlf
text0.value = text0.value & "Name:" & me.txtName.value & vbcrlf
text0.value = text0.value & "Selected Items:" & vbcrlf

Dim item As Variant

For Each item In ListClass.ItemsSelected
    text0.value = text0.value & List0.ItemData(item) & vbcrlf
    
Next item

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Simon BallCommented:
forgot to change list0 in line 8....
0
 
wlreimerAuthor Commented:
Thanks Sudonim,

I'm feeling a little stupid here.  I normally use queries a the record source for my reports (sometimes with code for unique reasons), and would probably have used a dlookup to get date, name, etc as headers on the report.

Do I put the code into the report?  Add an unbound control of some kind to the detail section of the report and reference it in the code?

I'll try it.

0
 
Simon BallCommented:
you said you were using a form?  when you said "However, I need to print a list of what has been selected and add info from a couple of other fields on the form to provide to the user."

I assumed you meant in a text box on the form.  you didn't mention a report.

I'd suggest you have a table with date, name and selected items, and an ID....(and any fields you will need to link it with)
and then after your usual docmd.runsql,

you could append the values into a seperate table and use that in a query for the report.

0
 
Simon BallCommented:
I am off for the weekend now.  Good luck with your project.
0
 
wlreimerAuthor Commented:
The primary reason for the list box is to append selected records into a table, however, there may already be items existing in that table for this particular date/name and I need to be able to list just the ones that are being inserted at this moment.  Is there any way to grab a list of what will be entered before it gets into the table so I could use it in a report?
0
 
Simon BallCommented:
yes. shoul be quite simple.my solution above write the selected data as lines in a new text box on your for (which could be set hidden with text0.visible = false). you can bring this out on a report my placing a text box contrl on you report and (as long as the source form is open) set the report textbox equal to =(forms)!(formname)!(text0).value     you need square brackets for that not ( or ) but i don't have those on my phone keyboard.
0
 
Simon BallCommented:

=[forms]![formname]![text0].value

Open in new window

0
 
wlreimerAuthor Commented:
It's working great Sudonim, however, I now see the need to add another column to the list of what prints.

The list contains multiple columns.

Column 0 is the bound colunm, so I'm getting the right info there, but it would look much better to get column 1 (description) as well, so it would like more like this:

1 description
2 description
5 description

I tried playing with how to get it, but just can't.  I think it'll need to loop again, but I don't know where to fit it or how to add it in.

Any suggestions?
0
 
wlreimerAuthor Commented:
Thank you Thank you.  It's absolutely perfect for what I need.  Thanks for all of the help.
0
 
Simon BallCommented:
Nice one.

there is probably a better way to sweep through the listbox for selected items and get the value from various columns, but if the above works, its all good... I'm saving a copy for when i need to do this myself :)
0
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.

All Courses

From novice to tech pro — start learning today.