Solved

Storing values from a multi select list box

Posted on 2007-11-30
10
602 Views
Last Modified: 2008-02-01
I would like to be able to have a list of names in a list box, be able to select multiple names from the list box and then store them for later use on a report or for use in another list box.  I can create the multi select list box ok but then what?
0
Comment
Question by:iglink
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 11

Expert Comment

by:TWBit
ID: 20386095
Permanently store them, or temporarily store them?

If just temporarily, you can just hide the form but still reference it for the report and other list box.  If you want to permanently store them, you will need to write the values to a table.
0
 

Author Comment

by:iglink
ID: 20386119
Yes permanently how do i write values to a table
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 20388155
You would need to have a table to hold the individual choices from the Multi-select list box.  These would be stored one choicve per record, along with the Id of the item to which the multiple choices 'belong'.  Can you give us some for description of precisely what you are trying to do.  What are the names relevant to?

AW
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20389452
Why not have all the names in a table with the fields PName (text) and SelectedYN (YesNo).  When the form loads, all the names are in the listbox.  With multiselect on, the user makes several selections.  On the click of the cmdSaveSelected command button, the on click event runs two queries, one to set all SelectedYN fields to False and then use the ItemsSelected property to update the table.  Seem like a plan?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20389889
iglink,

"Store values"?

Let's say you have a list box that has a RowSource of:
SELECT tblCustomers.CustomerID, tblCustomers.CompanyName FROM tblCustomers
So it lists all your customers.
You have set the "MultiSelect" Property of this listbox to "Extended"

Now, you want to be able to select multiple customers, and generate a Report using only the selected Customers.
Correct?

You don't technically "Store" the values.
You use code to "Accumulate" the selections, then build an SQL string that can be used as the Reports Record Source. (or you can modify it to be the source of anything you like)

I present you with my Sample, I use this as the basis for all my Multi-select List box Report menus (and as a teaching tool).
(It has comments all over the place!)
;)
https://filedb.experts-exchange.com/incoming/ee-stuff/5933-Access--EE-Q22994575--MultiSele.zip

I adopted it oh so many years ago from this seminal link.
http://www.mvps.org/access/reports/rpt0005.htm

Hope this helps!
:)

JeffCoachman
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 44

Expert Comment

by:GRayL
ID: 20390093
I had a look Jeff, nice work, but it doesn't 'store the values'.  As I said before, the easiest way I could think of was to add a field to the table, and check off the boxes like you retreived the 'selected' customers in the form.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 20390355
GRayL,
<nice work>
Why thanks!
:)
Yes, this was kind of a self tutorial on Multiselect Listboxes.

<but it doesn't 'store the values'>
Yes, you have a point.
I just figured that they might not really need to stored the selections, just "Use" them.

My reasoning was, being that this was a multiselect listbox, the Selected values would be constantly changing.
In that case, they would have to keep updating the table,...constantly.
(But my reasoning has been known to be faulty now and then)
:)

Anyway...
They could run the SQL string (strpubRptRecSrc) into another listbox:
Me.ListBox2.RowSource = strpubRptRecSrc
Or run it into a table:
    Dim dbs As DAO.database
    Dim rstTo As DAO.Recordset
    Dim rstFrom As DAO.Recordset
    Set dbs = CurrentDb
    Set rstFrom = dbs.OpenRecordset(strpubRptRecSrc)
    Set rstTo = dbs.OpenRecordset("tblCustNames")
        rstFrom.MoveFirst
        rstTo.MoveFirst
       
        'Delete the previous Values
        Do Until rstTo.EOF
            rstTo.Delete
            rstTo.MoveNext
        Loop
       
        Do Until rstFrom.EOF
            rstTo.AddNew
            rstTo!CustName = rstFrom!CustomerID
            rstTo.Update
            rstFrom.MoveNext
        Loop

But this all might be overkill.

Perhaps I, like Arthur_Wood, should have asked for a little more info.


Jeff
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20392215
Jeff said:  I just figured that they might not really need to stored the selections, just "Use" them.

but iglink said earlier:  Yes permanently how do i write values to a table.

I was just trying to kill two birds with one stone - a single table to populate the listbox, and be able to use the same table to 'store' the selected values until you wanted another set.  

iglink:  If you need to 'store' the selected values for just the life of the form, Jeff's will do it.  If you need to store the set until you generate a new set, mine is a solution.  If you need to store each set permanently, with some other key to be able to distinguish between sets, you do not yet have a solution.  What is it?
0
 

Author Comment

by:iglink
ID: 20392503
well i think its a bit of both - I have a list of all employees in a company which will appear in the first list. I want to be able to select on employees in that list  that say come to a meeting ie attendiees or who appologise for not coming so there would be three columns one for name, on for attendance one for appology ie where the last two are check boxes.  Then the names that are selected with attendiese checked are transfereed to another list for use during the meeting to assign names to comments.  But at any time in the future if you want to print reports then a list of attendies and apploogies ie from the check boxes would be printed so thats the stored bit. - I havent had a good look through the code yet but what do you recommend now?  Also is it possible to get the check box thing working in a multi select list?
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 250 total points
ID: 20392620
Then it sounds like you need two separate tables from your employee list, Meetings and MtgSelections

Meetings
======
MtgID - primary key - autonumber
MtgDate
MtgDesc
etc.

MtgSelections
==========
MtgSelID - pk - autonumber
MtgID - foreign key from Meetings
EmpID - foreign key from Employees
Attended - YesNo
Applolgy - YesNo

Something like this is what I see as necessary.  MtgSelections is what is populated in part from the multiSelect listbox.  You form would also need to populate the Meetings table with MtgDate, MtgDesc, etc. Getting the picture?
0

Featured Post

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!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

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

19 Experts available now in Live!

Get 1:1 Help Now