Combo box with a check list option? Is it possible?

[Guest]

I would like to make a form that has a combo box with a check list option to
select the desired items that I want from the drop down list.

I would like to have an option to "Select All" or Select Multiple ones by
manually putting a check in a box in front of the item.

I have figured out how to do the combo box and select one item from the
list, but I am just wanting the option to select all or more than one at a
time. Is this possible?

Thanks!
Bryan

 

[Steve]

Can not be done with a combobox but can be done with a multiselect listbox.
You can select multiple items. Add a button that executes code that
iterates through all the items in the listbox and selects them all.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Thanks Steve! I will look into that. Is there any chance you could expand on
how to do that a bit? I am very much a rookie at this...

Thanks again-
Bryan

 

[Steve]

Create a listbox from the Toolbox on your form. Set the rowsource property,
boundcolumn property, column count property and column width property. Go to
the Other tab and change the MultiSelect property from None to Simple.

Create a button beside the list box for selecting all items. Put the
following code in the Click event of the button:
Dim i As Integer
For i = 0 To NameOfYourListBox.ListCount - 1
NameOfYourListBox.Selected(i) = True
Next i

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Yep, that is what I am looking for. Thanks, Steve.

One more thing...is there a way to take it a step farther and have the lists
act like a cascading combo box? So then next list in the form reflects the
selections made in the previous list and only shows the necessary records for
less confusion?

Or would I have to have some sort of "Update Query" button after each list?

Thanks again Steve. I appreciate it.

Bryan

 

[Steve]

Put the following function in the code window behind your form:

Public Function buildList(strString As String, NameOfYourListBox As ListBox)
As String
Dim i As Integer
For i = 0 To NameOfYourListBox.ListCount - 1
If NameOfYourListBox.Selected(i) Then
If strString = "" Then
strString = Chr$(39) & NameOfYourListBox.ItemData(i) & Chr$(39)
'Use when items in listbox are text
strString = NameOfYourListBox.ItemData(i) 'Use when items in
listbox are numeric

Else
strString = strString & "," & Chr$(39) &
NameOfYourListBox.ItemData(i) & Chr$(39) 'Use when items in listbox are text

strString = strString & "," & NameOfYourListBox.ItemData(i) 'Use
when items in listbox are numeric End If
End If
Next i
buildList = strString
End Function

Note: In the above code two assignment statements are included in both the
if and the else -- the ones with Chr$(39) will insert quotes around the
ItemData -- use this version if the listbox contains text. Use the other
version for numeric data.

Create a query based on the same table your listbox is based on. Include the
primary key and the field that is in your list. Put the following expression
in the critria of the list field:
IN "(" & buildList(strT, NameOfYourListBox) & ")"

This is untested - you may need to tweek it a bit.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)