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

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?

Is basically people's names.

Do you have a table of these names?

Are you new to building Access databases?

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

He is not new to building dbs :-)

Thank you everyone for the responses.  I'm going to take a look at your responses tomorrow, and get back to everyone.

Thanks,

gdunn59

Here is a simple working sample that I believe fits with your requirements.
Database130.mdb

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

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

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

@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

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

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

great, glad you got it :) ... you're welcome and thank you

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

:)

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.

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

Select allOpen in new window

then on the form CURRENT event:

CALL ClearList(Me.Listbox_controlname)

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

Crystal (strive4peace):

When I run the debug I get the following error:

    Invalid use of me keyword

Nick67,

Sorry I overlooked yours.  Thanks though!

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

> "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 :)

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

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

the module name doesn't matter -- just has to be different than any of your procedure names

crystal (strive4peace),

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

Thanks,

gdunn59

will you post the code that is calling it?

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

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.

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

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

Select allOpen in new window

to call it, use:

CALL ClearListF(me, me.controlname_listbox)

Select allOpen in new window

perhaps you can write to the moderators and ask them if you can change the points -- I am more than happy to share ~

crystal (strive4peace),

Ok.  Thanks!

I will reach out to the moderators.

Thanks to both of you crystal (strive4peace) and Nick67!

you're welcome ~ happy to help

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:

Sometimes the benefits of breaking up code make sense.
Sometimes not.

I requested attention to the moderators to get the points redistributed.

Thanks!

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 ~

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.

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 ~

"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

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

Like I was sayin' ... I don't use Subs :-)

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.

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

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 ~