Using Forms with multiple criteria queries

[NickC]

From a previously posted email, I learned that I could address multiple
criteria in a query by using "OR" statements. I currently use drop-down boxes
in a Form format to input my criteria selection. However, the drop-down box
only allowes me to select one criteria for a given field (i.e. I can't hold
down the shift or control button to select multiple choices). What should I
use in my form that will allow me to select multiple choices.

 

[Klatuu]

A multi select List Box (basically a list box with the Multi Select property
set to either simple or extended - I prefer extended). But, now it gets
complicated. If you have a multi select list box and try to get the value of
it by using
=Me.MyListBox
you will not get anything. You have to use the ItemsSelected collection to
determine which items were selected, then you need to use those items to
contruct a valid Where Clause.
If you are not familiar with VBA, it will be a serious challenge. But, it
will be a great learning exercise.

 

[NickC]

Thanks. I'm fairly to access and even more so to VB. But I will give it a
stab. One more question, and don't laugh. Are multi-select List Box
available in Access 2002? I do not see the option.

 

[Klatuu]

I believe so, I went from 2000 to 2003, but I don't think there are any
significant differences between 2002 and 2003.
Open your form in design mode
Put a list box control on it.
Open the properties dialog for the list box
Look for a property in the Other tab named Multi Select
It should have 3 options None, Simple, and Extended.
I prefer extended because it give the user more ways to control the
selections.

Now let's see if I can scare you off
Here is the code that builds a string that can be used in the Where clause
of a query:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

 

[NickC]

You are awesome!!! I was able to get the multi-select list box. I am going
to have to take the code back as homework for tonight. I let you know how it
went on tomorrow. Thanks again!!!

 

[NickC]

Okay, As predicted, I am clueless with the code. My Fortran skills from
college are not coming in handy So here are my questions.

1) I copied and saved the code you wrote to retrieve selected choices from a
multi-select list box. Now that I have this code as a module within my
database. How do I use this module in the Criteria field of my query? . .
..Do I use expression builder to find the module or do I actually select this
module from the listbox?

2) I copied the code word from word. What words do I need to change so that
the module will know from which listbox it should retrieve the values? Do I
substitute the word "controls" with the name of my listbox [i.e.
Me.Controls() --> Me.Certification(strCertification)]?

Sorry for the confusion and the bother.