Solved

View selection from multi list box as report

Posted on 2011-02-24
13
445 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:wlreimer
  • 9
  • 4
13 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34977779
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
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34977797
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
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34977800
forgot to change list0 in line 8....
0
 

Author Comment

by:wlreimer
ID: 34978673
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
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34978701
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
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34980893
I am off for the weekend now.  Good luck with your project.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:wlreimer
ID: 34982297
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
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34987017
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
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34995118

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

Open in new window

0
 

Author Comment

by:wlreimer
ID: 34998040
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
 
LVL 15

Accepted Solution

by:
Simon Ball earned 250 total points
ID: 34998197
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
 

Author Closing Comment

by:wlreimer
ID: 35036222
Thank you Thank you.  It's absolutely perfect for what I need.  Thanks for all of the help.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35036538
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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