Rebecca,
You can set it up so that you only query on one of the listboxes, or so the
selections in multiple listboxes will be used. Lets assume you have
multiple listboxes and want to return records that match criteria in each of
these listboxes.
1. Create a command button (cmd_Filter) or something like that. In the
click event of that button, enter code that looks like:
Private sub cmd_Filter_Click
'Dimension these variables as varaints to take advantage of the way VBA
uses the + and & to concatenate values
Dim varMasterFilter as variant
Dim varListOneFilter as variant
Dim varListTwoFilter as variant
Dim varListThreeFilter as variant
'Call fnMultiSelect( ) - see note below for each of the lists in your
header
varListOneFilter = "[SomeField] " + fnMultiSelect(me.lst1)
varListTwoFilter = "[SomeOtherField] " + fnMultiSelect(me.lst2)
varListThreeFilter = "[ThirdField] " + fnMultiSelect(me.lst3)
'Concatenate the results of the three fucntion calls to the master
filter.
'Using the + " AND " syntax below, within the paranthesis ensures that
if non of the items were selected in the previous lists, " AND " will not
be
'inserted in the filter
varMasterFilter = varListOneFilter
varMasterFilter = (varMasterFilter + " AND " ) & varListTwoFilter
varMasterFilter = (varMasterFilter + " AND " ) & varListThreeFilter
'Set the forms filter and turn it On/Off based on whether
varMasterFilter is null
me.filter = NZ(varMasterFilter, "")
me.FilterOn = NOT isnull(varMasterFilter)
End Sub
The function fnMultiSelect mentioned above accepts a listbox as its
parameter. It determines how many items are selected in the list and
returns values that look like:
NULL (no items selected)
= 3 or = "A" (only a single item is selected)
IN (3, 5, 7) or IN ("A", "B", "C")
Basically, this function loops through the list of selected items and
concatenates the elements from the bound column into a string. I would
recommend that you create a code module (select Modules -> New) for this so
that you can call this function from any form.
Public Function fnMultiSelect(lst As ListBox) As Variant
Dim varSelections As Variant
Dim varItem As Variant
Dim strDelimeter As String
'If there are no items selected in the list, then exit immeciately
If lst.ItemsSelected.Count = 0 Then
fnMultiSelect = Null
Exit Function
End If
varSelections = Null
'Define the delimeter based on the datatype of the bound column
Select Case VarType(lst.Column(lst.BoundColumn - 1, 0))
Case vbLong, vbInteger, vbSingle, vbDouble, vbCurrency, vbBoolean,
vbDecimal, vbByte
strDelimeter = ""
Case vbString
strDelimeter = Chr$(34)
Case Else
strDelimeter = ""
End Select
'Loop through each of the selected items
For Each varItem In lst.ItemsSelected
varSelections = (varSelections + ",") _
& strDelimeter _
& lst.Column(lst.BoundColumn - 1, varItem) _
& strDelimeter
Next varItem
'Determine how to return the results based on whether only one item was
selected,
'or multiple items are selected
If lst.ItemsSelected.Count = 1 Then
fnMultiSelect = " = " & varSelections
Else
fnMultiSelect = " IN (" & varSelections & ")"
End If
End Function
HTH
Dale
gorebeccago said:
Thanks for getting back so quickly. Given I have hundreds of possible
selections (revenue sums, contact detail, contact location by region, by
state, by zip, company info, company relationship, etc), I am assuming I
have
to list each option within its respective parenthesis, right?
How do I set the WHERE clause to reflect choices from multiple selects in
the form header to the record displays on the form?
Finally, will I be able to make various selections within each category,
or
is it only a one shot deal?
Rebecca
Dale Fye said:
Rebecca,
There are a couple of ways to do this.
1. One is to write code to dynamically build a where clause from the
selected items in your multiple listboxes. To do this, you would loop
throught the SelectedItems collection of each listbox and build the IN
( )
clauses for each listbox (you would normally do this in the click event
of a
command button). Then, you would concatenate these criteria together.
The
up side of this is that it is relatively easy to do this. The down side
is
the WHERE clauses with IN ( ) clauses are not the fastest queries
processed
by Jet. This criteria might look like:
"WHERE [somefield] IN (1,3, 5, 7) OR SomeOtherField IN ("CA", "VA", "IN")
2. Generally, if I know I'm going to be using a table for a multiselect
listbox, I add a IsSelected (Yes/No) field to the table. Then, when I
load
the form, I run update queries that set the IsSelected field to false for
all of the records in that table. In the click event of the command
button
mentioned above, I would again loop through the SelectedItems collection
of
each of the listboxes, but instead of building the IN ( ) and WHERE
clauses,
I would run queries that update the IsSelected field in each of the
tables.
Then, I would write a WHERE clause that filters on items where the
IsSelected field is True.
It might take a little longer (miliseconds) to update the tables
(technique
#2) than to build the criteria string (technique #1), but in my
experience
the resulting query will probably run significantly faster for technique
#2.
HTH
Dale
I have been searching for weeks for a solution but to no avail.
My form has multiple-select list boxes (both text and numbers) that
contain
customer contact info, company revenue, and sales relationships.
How do I code it so that the form displays (which will exentually be
exported to Excel) only the records based on the multiple criteria
selectioned within each box?
Any help you can offer is appreciated. I'd rather not resort to a pivot
table, which is the only alternative I can think of.
Rebecca
