Solved

View selection from multi list box as report

Posted on 2011-02-24
13
486 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

710 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