How can I create a List Box or Combo Box that allows the user to make more than one selection

How can I create a List Box or Combo Box in Access that allows the user to make more than one selection and then have the multiple selections saved to a specific field in a specific table?

Thanks,

gdunn59
LVL 1
gdunn59Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
You can use a Multi-Select listbox to let users pick more than one choice.

> "have the multiple selections saved to a specific field in a specific table?"

Do you have a multi-value (MV) field?  I don't want to tell you how to do this ... the reason is because it is MUCH better to set up a related table than to use an MV field.  They are hard to query and report from -- not to mention, convert.

I will be happy to help you with design suggestions if you tell me more about your data and I will also tell you how you can choose multiple values and make multiple records ;) ~

What kind of data do you have?
0
gdunn59Author Commented:
Is basically people's names.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Do you have a table of these names?

Are you new to building Access databases?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
What is the purpose of your application?

The more information you give, the better we can help ~

Please do not be dismayed by my desire to help you build a better database.  The more time you put in up front, planning and designing, the more time you will save down the road.  I do want to help, as do we all here ~ help you build something good that will be flexible and powerful
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
He is not new to building dbs :-)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thanks, Joe ~

to answer your question, though ...

.1. create a listbox control and set its Multi-Select Property (OTHER tab of Property Sheet) to Simple or Extended

Simple means you click on a value to toggle selection status

Extended means you can click, Ctrl-Click, and Shift-Click and behaves like Windows Explorer for selecting files

.2. on the DATA tab, set:
RowSource

.3. on the FORMAT tab, set, as a minimum:
ColumnHeads (Yes if you want them; No is Default)
ColumnWidths
ColumnCount
Width

you also may want to set Status Bar Text (OTHER tab) and other properties.  Definitely choose a good NAME!

.4. On the AfterUpdate event, here is code to update another control with the choices.  This assumes what you want to concatenate is in the first column of the listbox.

   'build string from selections in Multi-Select Listbox

   Dim vIndex As Variant _
      , vAllSelections As Variant
      
   vAllSelections = Null

   For Each vIndex In Me.Listbox_controlname.ItemsSelected
       With Me.Listbox_controlname
         vAllSelections = (vAllSelections + ", ") & .ItemData(vIndex)
      End With
   Next vIndex
   If Not IsNull(vAllSelections) Then
      Me.txt_controlnameAllSelections = vAllSelections
   else
      Me.txt_controlnameAllSelections = Null
   End If

Open in new window


WHERE
txt_controlnameAllSelections is the Name property of the control where you want the values to go.  This could, of course, be bound.
Listbox_controlname is the Name property of the listbox
0
gdunn59Author Commented:
Thank you everyone for the responses.  I'm going to take a look at your responses tomorrow, and get back to everyone.

Thanks,

gdunn59
0
Jeffrey CoachmanMIS LiasonCommented:
Here is a simple working sample that I believe fits with your requirements.
Database130.mdb
1
Nick67Commented:
How can I create a List Box or Combo Box in Access that allows the user to make more than one selection and then have the multiple selections saved to a specific field in a specific table?

Combo Box is out.  Those only permit a single selection.
As noted, you can make a ListBox multi-select.
From there, it's deciding what you want to do with the selection(s)

Usually, you do a For Each


Dim varitem As Variant
For Each varitem In Me.lstMyListControlName.ItemsSelected
    'code block to do stuff with the selected row
Next varitem


Now, each row has a number of columns
Me.lstMyListControlName.Column(0, varitem)
This is the 0th column of the selected row in the For Each
The syntax is Me.lstMyListControlName.Column(ColumnIndexNumber, RowIndexNumber)
So you can work not only with the bound value, but with all the columns of your listbox.

When you are done with the user's selections, and you want to deselect everything then you run another For Each


    For Each varitem In Me.lstMyListControlName.ItemsSelected
        Me.lstMyListControlName.Selected(varitem) = False
    Next



have the multiple selections saved to a specific field in a specific table
The phrasing of that is a bit ambiguous.
Taken literally, it sounds like your asking for a de-normalized save.
"Bob,Joe,Jane,Sue" saved in tblNames.SelectedPeople
I suspect you meant to save some IDs in multiple rows of the same field, because this isn't your first rodeo.

There's code ways to pre-select items as defaults in a ListBox as well.
That's a bit more rigamarole, and I don't have a code sample handy.
Basically, for each thing you want selected, you identify the row its in and issue
Me.lstMyListControlName.Selected(RowIndexNumber) = False
Figuring out the row is the hard part.
One way is to walk the list and compare the .ItemData or .Column(Row,Col) to some value and issue .Selected = True when you get a match, and then rinse-and-repeat for each criteria you have.
You can do it with the query underlying the listbox, instead of the listbox itself, but it amounts to the same thing.

Hopefully, you've gotten the gist of working with multi-select listboxes.

Nick67
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
if you can tell us what you want the list to show and the fieldnames and the table name, we can use YOUR names.

Since you haven't mentioned that information, whatever we give you will need to be substituted.  That is why I did not give you any names in my example, just said
.2. on the DATA tab, set:
RowSource

to do this:
turn on the Property Sheet
click on the listbox control you added
click on the DATA tab of the Property Sheet
Click in the RowSource property and then on the Builder Button ... to the right

this takes you to what looks like a query
choose your table and then choose the field(s) you want

If you choose more than one field, count them -- this will be the ColumnCount property on the FORMAT tab of the Property Sheet

Also, set the ColumnWidths property and ListWidth properties

rather than guessing what those might be, if you give us more information about your data, we can help you better
0
gdunn59Author Commented:
crystal (strive4peace),

I tried your selection, and it worked.  I was just wondering is there a way instead of having to hold down the Ctrl key to make the multiple selections, to just double click on the first name and have it populate the field, and then continue to double click for more and each time have it append instead of overwriting the first selection?

Thanks,

gdunn59
0
Jeffrey CoachmanMIS LiasonCommented:
@gdunn59
I can't see the line of code you have there because the error message is convening it up...
But the listbox needs to have it's column count set to: 2
(The Primary key field, and the Name field)
...but you can set the field widths to: 0,1
...to hide the Primary key column

Basically the listbox will only "see" the first (Bound) column (The primary key)
Then the Primary key value is used to "lookup" the name
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you can set the Multi-Select Property (OTHER tab of Property Sheet) to Simple to click each value to toggle the selection status (add or remove it from the selection)

Extended is the other choice and means you can click, Ctrl-Click, and Shift-Click and behaves like Windows Explorer for selecting files
0
gdunn59Author Commented:
Great that worked.  

This is what I have:

Private Sub List5_AfterUpdate()
   Dim vIndex As Variant, vAllSelections As Variant
     
   vAllSelections = Null

   For Each vIndex In Me.List5.ItemsSelected
       With Me.List5
         vAllSelections = (vAllSelections + ", ") & .ItemData(vIndex)
      End With
   Next vIndex
   If Not IsNull(vAllSelections) Then
      Me.Names = vAllSelections
   Else
      Me.Names = Null
   End If

End Sub
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
great, glad you got it :) ... you're welcome and thank you
0
gdunn59Author Commented:
Hey crystal (strive4peace):

I closed out a little to quick.  Everything works great the only thing is when I go to the next record it still has the previous selections selected and as soon as I click on the ListBox field it automatically populates with the previous selections.

What do I need to do?

Thanks,
gdunn59
0
Nick67Commented:
:)

I gave that to you in my comment
To deselect them all

Dim varitem As Variant
For Each varitem In Me.List5.ItemsSelected
      Me.List5.Selected(varitem) = False
Next varitem


You'll need to perhaps have that in OnCurrent if the form is navigable to make sure that any selections made but then not used get tossed as you move between records.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you can put code on the form CURRENT event.

add this function to a general module so you can use it anywhere:
Private Function ClearList(ByVal pControlname As String)
   Dim varItem As Variant
   For Each varItem In Me(pControlname).ItemsSelected
       Me(pControlname).Selected(varItem) = False
   Next varItem
End Function

Open in new window


then on the form CURRENT event:
CALL ClearList(Me.Listbox_controlname)

Open in new window

*** How to Create a Standard (General) Module ***

Press Alt-F11 to go to the VBE (Visual Basic Editor)

From the menu in a the Microsoft Visual Basic window:
Insert --> Module

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it.  You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc
0
gdunn59Author Commented:
Crystal (strive4peace):

When I run the debug I get the following error:

    Invalid use of me keyword
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
oops! Sorry about that! I started to change the procedure for you to run behind your form ... then I decided it would be better for you to build your library of general functions

Function ClearList(pControl As Control)
   'Crystal (strive4peace)
   Dim varItem As Variant
   With pControl
   For Each varItem In .ItemsSelected
       .Selected(varItem) = False
   Next varItem
   End With
End Function

Open in new window

to call:
Call ClearList(Me.controlname_Listbox)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gdunn59Author Commented:
Nick67,

Sorry I overlooked yours.  Thanks though!
0
gdunn59Author Commented:
crystal (strive4peace),

I'm getting an error that it can't find the object Call ClearList.

I entered the the following in the Current event of the Form:

CALL ClearList(Me.List5)

List5 is the name of my ListBox.

Here is the function/module:

Function ClearList(pControl As Control)
   Dim varItem As Variant
   With pControl
   For Each varItem In .ItemsSelected
       .Selected(varItem) = False
   Next varItem
   End With
End Function
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> "List5 is the name of my ListBox"

it is good idea to change the NAME property of a control to something logical before you reference it

this is not the problem though

From the Visual Basic Editor (VBE), from the menu, choose:
Insert, Module

paste the ClearList code and when prompted to save, give the module a good name ... something like mod_General

this will allow you to call ClearList from anywhere!

before you run, choose Debug, Compile from the menu -- and then Save


and also help you to start building a library of general procedures that you can import into other projects :)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> "I entered the the following in the Current event of the Form:
CALL ClearList(Me.List5) "

paste the code you entered if you still have a problem after doing the steps in the last message
0
gdunn59Author Commented:
crystal (strive4peace),
 
I already had did everything you mentioned in your last post.  The only thing is I named the module "modClearList".

Does the name matter?

Thanks,
gdunn59
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
the module name doesn't matter -- just has to be different than any of your procedure names
0
gdunn59Author Commented:
crystal (strive4peace),

I also did include all of the code (in the ClearList module), in my previous post.

Thanks,

gdunn59
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
will you post the code that is calling it?
0
gdunn59Author Commented:
It's the code you posted in:

Expert Comment 2015-11-18 at 13:17:52ID: 41270828

Here it is:

Function ClearList(pControl As Control)
   'Crystal (strive4peace)
   Dim varItem As Variant
   With pControl
   For Each varItem In .ItemsSelected
       .Selected(varItem) = False
   Next varItem
   End With
End Function
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Sorry to not be clear, I meant can you please post the code that is calling it?

Also be sure that ClearList is not in your code twice ;)

To see a list of all the procedures in your code, press F2 when you are in the code.  This will take you to the Object Browser.

At the top, you will see <All Libraries> -- change this to choose just your project.

In the <globals> classes, you will see your standard procedures.  If you click on a specific form or report, you will see the procedures and properties/methods that apply to them.  Procedures are bolded and built-in properties/methods are not.
0
Nick67Commented:
Passing a control out to another module can be less fun than a person would like.
Sometimes explicitness helps

On the form with List5 give it
Dim pControl As Control
Then set it
Set pControl = Me.Controls("List5")
Then pass out pControl
Call ClearList(pControl)

This way, you explicitly passed out a control

It may help.
1
gdunn59Author Commented:
Nick67,

I used your solution, and that worked:

Private Sub Form_Current()
Dim pControl As Control

Set pControl = Me.Controls("List5")
Call ClearList(pControl)

End Sub


I apologize, I wished I could award you points for that.  Is there I way that I can award you points?

Thanks,

gdunn59
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
interesting point.  Here is another version of ClearList that takes the form and the control name:

Function ClearListF(pF As Form _
   , pControlname As String) As Byte

   'Crystal (strive4peace)

   Dim varItem As Variant
   For Each varItem In pF(pControlname).ItemsSelected
       pF(pControlname).Selected(varItem) = False
   Next varItem
End Function

Open in new window


to call it, use:
CALL ClearListF(me, me.controlname_listbox)

Open in new window

0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
perhaps you can write to the moderators and ask them if you can change the points -- I am more than happy to share ~
0
gdunn59Author Commented:
crystal (strive4peace),

Ok.  Thanks!

I will reach out to the moderators.

Thanks to both of you crystal (strive4peace) and Nick67!
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
0
Nick67Commented:
No need to bother the mods.
You win some, you lose some.
As long as there are good solutions, that's reward enough for me.

Some things worth saying:
Function ClearListF(pF As Form, pControlname As String) As Byte

If you aren't going to return a value, then create a Sub
If you are returning a value, then create a Function
Either one can take arguments, but only one can return stuff.

As you've seen, passing objects in Access works -- but it can be tricky.
Generally within an object module works well, between modules can be hit or miss.
Passing top-level objects (Forms and Reports) generally works well
Secondary objects (controls etc) can be problematic.

Passing data (string, number, Boolean) is bulletproof.

If you only have one listbox to clear, I'd view putting the code to so into a code module as obfuscation.
I personally think it makes things harder to maintain, because you break the flow and have to look externally for possible errors -- and you create dependencies.
So, you look at the trade-offs:
how often can I reuse this code block?
how generic is it so that changes to it will be resilient?
how complex is what I am going to extract?
will the total sum of code be more readable/maintainable by doing this?

Sometimes the benefits of breaking up code make sense.
Sometimes not.
0
gdunn59Author Commented:
I requested attention to the moderators to get the points redistributed.

Thanks!
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Nick,

I appreciate what you are saying about Function vs Sub ... the reason, however, that I use function is to assign directly on the property sheet so I can create a command button to clear and copy, along with its functionality.

Byte is the smallest data type, which is why I specify it to be a return -- I never actually use the return value though!

warm regards,
crystal

~ have an awesome day ~
0
Nick67Commented:
I appreciate what you are saying about Function vs Sub ... the reason, however, that I use function is to assign directly on the property sheet so I can create a command button to clear and copy, along with its functionality.

I am not sure what you mean!

Byte is the smallest data type
A Boolean is a single bit.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Nick,

on the property sheet, I can create a button and assign a function to its CLICK event, but not a Sub
=ClearList(parameters)

In tables, Boolean actually takes 2 bytes (but its extra capabilities are crippled) ... not sure if VBA stores it in less so I use Byte also to indicate (to me, anyway) that I am NOT using the return value

warm regards,
crystal

~ have an awesome day ~
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"If you aren't going to return a value, then create a Sub"
I never use Subs. I just don't see the point. Back in the early days of Access and much slower PCs, there *might* have been a *slight* advantage of using a Sub - *maybe* less overhead, but certainly not in today's world. And as noted, you can call Functions directly from a property sheet of a control - a very useful concept.

mx
0
Nick67Commented:
:)
Why have the system reserve the memory for a return value -- which you have to declare -- and then not use it?  Tacking on an arbitrary data value to a function -- or leaving it off and getting a Variant -- is something for which I don't see the point.

A Sub is for doing something.
A function is for computing and returning something.
In the Access world, at least.

With WinAPI, your function may do something, and the return is a message about how those actions played out -- or the purpose may be to compute and return something.  I find that messier.
I like the clear division of function that Access affords.

you can call Functions directly from a property sheet of a control
That's just evil.  Pure obfuscation.  And it makes maintainability harder

[Event Procedure]
Click the ... icon
Go to the code and see what the event does.
If you call a code module procedure from the event procedure, you can do a Ctrl-F and go find all the places in code where you've used that code module procedure -- and what your dependencies are if you decide to refactor that code module procedure.

Do it in the property sheet and you lose that very valuable ability to track those dependencies.
Hell on wheels.

But hey, that's me.
Coding styles can and do vary.
I keep SQL in saved queries, control function in VBA, and frequently used generic function/subs in modules.

I've come across examples of code abstraction where you look in one sub, to find a class, that calls another class, that instantiates two more class and passes them out to yet another sub that then executes a single line of code.

No thanks.

But each to their own.
You weigh the benefits and pitfalls and get busy :)

Nick67
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Like I was sayin' ... I don't use Subs :-)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
and why the return value IS declared and is small ;) To me "as byte" means it acts as a sub ~

Because Access cannot assign a sub for a property (opposite of Excel), functions have much greater flexibility.
1
Nick67Commented:
<grin>
To me
Sub means it acts as a sub and Function means it acts as a function.
And since I consider dropping functions into the property sheet to be the height of obfuscatory evil, the fact that you can do that with a Function and not a Sub is irrelevant.
</grin>

Insoluble differences of opinion.
I find the distinction useful.
Others do not--and that's ok.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Nick,

I get what you are saying -- and you are not alone.  Many feel as you do.  For others, like me and Joe, we prefer to be able to copy "standard" things like like navigation buttons and buttons to clear a list, with their logic.  For instance, I also have a function called DropMe, which I assign on the property sheet for MouseUp of combos. And another called DropMeIfNull, which I assign on the GotFocus event on the property sheet for most combos. To each their own.

warm regards,
crystal

~ have an awesome day ~
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.