Using Forms with multiple criteria queries

N

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

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

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

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
 
N

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!!!
 
N

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top