Solved

View selection from multi list box as report

Posted on 2011-02-24
13
456 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…

911 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

20 Experts available now in Live!

Get 1:1 Help Now