?
Solved

View selection from multi list box as report

Posted on 2011-02-24
13
Medium Priority
?
511 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

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
 

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 1000 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

598 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