Select multiple items from dropdown list (1 Viewer)

[Guy_Philly]

Hi,

I would like to be able to select multiple items from a dropdown list like we often see on web forms. The kind where you hold down the control key to select up to 5 items. Is this possible on an access form? If so, how would do we implement, and how is such data be stored?

Thanks!
~Bruce

 

[RuralGuy]

Access ComboBoxes do *not* have this feature. Only one selection is allowed. You would need to use a ListBox to have a MultiSelect feature.

 

[Guy_Philly]

Thanks RG,

Now that I have implemented the list box as you have suggested, it has a few quirks. The main thing is that after selecting multiple items from the form, I would like to be able to display all the selections made for a given record in a report. How would I do this? The other quirky thing that is happening is that the list seems to be growing each time i select multiple items for a given record. I'm not sure what's happening there. Any thoughts you may have about these issues would be appreciated.

Thanks!
~Bruce

 

[RuralGuy]

Here's a link that may help: Making Sense of List Boxes

 

[Guy_Philly]

RG,

That is an excellent resource. Unfortunately, I think I am still implementing the list box incorrectly.

I have the following objects in my Access 2003 database:

tblContacts:
This holds a lot of different information about people (names, addresses, various contact info, and Expertise). I have set aside one field (AllExpertise) for storing the string of discplines that will be generated by the list box and code (below).

tblDiscipline:
Lists various disciplines that will be shown in the list box. There are 2 fields in this table: DisciplineNum (the primary key), and Discipline (Display Control is List Box).

frmContacts:
has a listbox named:lstExpertise
- Row Source: SELECT tblDiscipline.Discipline FROM tblDiscipline;
- Control Source: ~none~
- Multiselect: Extended
- Column Count: 1
- Bound Column: 1

Command Button associated with lstExpertise list box on form frmContacts
- Name: SelectExpertise
- Caption: OK
- Event: OnClick (see coding blow)

Code Used:

Private Sub SelectExpertise_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
For Each varItem In Me!lstExpertise.ItemsSelected
strCriteria = strCriteria & ",'" & Me!lstExpertise.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM tblContacts " & _
"WHERE tblContacts.AllExpertise IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryMultiSelect"
Set db = Nothing
Set qdf = Nothing
End Sub

Two undesireable things are happening:
1. Query never opens up to display data in a string (as selected in the list box). Actually, what I am really after is having the string entered into one field (AllExpertise) in tblContacts.
2. the selection in my list box keeps growing as I try to troubleshoot #1.

I am probably omiting some useful info here. Please let me know if you spot anything that I am obviously doing wrong, or if I need to provide more info.

Thanks!

 

[RuralGuy]

Try:
strCriteria = strCriteria & "," & Chr(34) & Me!lstExpertise.ItemData(varItem) & Chr(34)
It seems to me that the IN statement does not like the single quotes.

 

[Guy_Philly]

Thank you all for your help. Below is the final code that worked for my situation:

Private Sub SelectExpertise_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
For Each varItem In Me!lstExpertise.ItemsSelected
strCriteria = strCriteria & ",'" & Me!lstExpertise.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
Me![MultiExpertise] = strCriteria

Set db = Nothing
Set qdf = Nothing
End Sub

 

[RuralGuy]

Thanks for posting back with your success.

 

[Hokie]

Hi,

I understand that to have the option of multi-select, I have to make a list box instead of a combo box. However, in my list I have about 200 countries. Therefore, to find out what countries the user has selected in the list box I have to scroll through the whole list. Is there any convenient way to show the countries that the user has selected from the list? For example is it possible to make a text box which automatically populates the countries that has been selected from the list in the list box? Thank you.

 

[jdraw]

Hokie,

You have attached your question to a thread that was last active in 2006.
I recommend you tell us more about your proposed application. There may be some options to achieve the same outcome, but more info is needed.

 

[Hokie]

Hi jdraw,

Thank you for your answer. Here is my question.

I have a form where I have a field named 'Venue' with a listbox with the names of about 200 countries. I have made the listbox multi-select. Therefore, a user is able to pick as many countries they want to from the list. However, the problem is that to find out what countries the user has selected from the list I have to scroll-through the list of the 200 countries which is very inconvenient.
Is there any convenient way to show the countries that the user has selected from the list? For example is it possible to make a text box which automatically populates the countries that has been selected from the list in the listbox? Thank you.

 

[spikepl]

This you do using two listboxes. One holds countries selected this far, the second the countries still available for selection. A doubleclick or a button moves a country from one to the other. Google

 

[jdraw]

There is a free, video example of what spike is suggesting here