473,729 Members | 2,063 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Code for Filtering from Mult-select List Box Criteria

Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) based
on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need to
make the second list box (lstAcadPlan) a multi-select list box and pass the
values to the filter. I have no idea how to include that in my code and was
wondering if anyone had any ideas on what I should do. Here is the code I
have so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_ Click()
Me.sfrmSearchRe sults.Visible = True
Me.lblSubformIn structions.Visi ble = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstColle ge & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadP lan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = '" & Me.lstAcadPlan &
"'"
Else
strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
End If
End If
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt er = strWhere
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt erOn = True
End Sub

Thank you,

William
Nov 12 '05 #1
19 3563
On my website (see sig below) is a small sample database called
"CreateQueries2 .mdb". Form 6 in this database illustrates how to create a
Where condition in code with a multi-select listbox. See the code behind
the form for details. In broad outline, it will work something like this:

For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

This code loops through the values in the listbox. When it reaches one that
is selected, it adds to the Where clause. When it reaches the end, it lops
the last comma off of the Where clause.

(The actual syntax of the strWhere clause will vary depending on details of
your listbox control.)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org
"William Wisnieski" <ww********@adm issions.umass.e du> wrote in message
news:3f******** @news-1.oit.umass.edu ...
Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) based
on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need to make the second list box (lstAcadPlan) a multi-select list box and pass the values to the filter. I have no idea how to include that in my code and was wondering if anyone had any ideas on what I should do. Here is the code I
have so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_ Click()
Me.sfrmSearchRe sults.Visible = True
Me.lblSubformIn structions.Visi ble = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstColle ge & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadP lan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = '" & Me.lstAcadPlan &
"'"
Else
strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
End If
End If
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt er = strWhere
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt erOn = True
End Sub

Thank you,

William

Nov 12 '05 #2
On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
<ww********@adm issions.umass.e du> wrote:

Check the SelectedItems collection. Then build an IN clause as part of
your WHERE statement:
.... and AcadPlan in ('aaa', 'bbb', 'ccc')

-Tom.

Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResul ts button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege) , it returns values in the second list box (lstAcadPlan) based
on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need to
make the second list box (lstAcadPlan) a multi-select list box and pass the
values to the filter. I have no idea how to include that in my code and was
wondering if anyone had any ideas on what I should do. Here is the code I
have so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_ Click()
Me.sfrmSearchR esults.Visible = True
Me.lblSubformI nstructions.Vis ible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstColle ge & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadP lan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = '" & Me.lstAcadPlan &
"'"
Else
strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
End If
End If
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt er = strWhere
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt erOn = True
End Sub

Thank you,

William


Nov 12 '05 #3
Thank you for your reply Tom.

I'm not familiar with the IN clause. What do aaa, bbb, ccc represent? The
values of second list box will change depending on what is selected in the
first list box. For example, there can be up to 50 distinct Academic Plans
for each College chosen in the first list box. Also, what are the
"SelectedIt ems" collection?

William
"Tom van Stiphout" <to*****@no.spa m.cox.net> wrote in message
news:7n******** *************** *********@4ax.c om...
On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
<ww********@adm issions.umass.e du> wrote:

Check the SelectedItems collection. Then build an IN clause as part of
your WHERE statement:
... and AcadPlan in ('aaa', 'bbb', 'ccc')

-Tom.

Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResul ts button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege) , it returns values in the second list box (lstAcadPlan) basedon the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need tomake the second list box (lstAcadPlan) a multi-select list box and pass thevalues to the filter. I have no idea how to include that in my code and waswondering if anyone had any ideas on what I should do. Here is the code Ihave so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_ Click()
Me.sfrmSearchR esults.Visible = True
Me.lblSubformI nstructions.Vis ible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstColle ge & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadP lan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = '" & Me.lstAcadPlan &
"'"
Else
strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
End If
End If
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt er = strWhere
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt erOn = True
End Sub

Thank you,

William

Nov 12 '05 #4
Thanks Roger,

I tried your code. I get a run time error and when I go to debug, it seems
to have combined the college and academic plan. For example, strWhere
filter should show "[College] = CAS and [AcadPlan] = Art, Or Philosophy, Or
History". But it is showing [College]=CAS, Art, Philosophy, History."

William
"Roger Carlson" <NO************ **@hotmail.com> wrote in message
news:O7******** ********@tk2msf tngp13.phx.gbl. ..
On my website (see sig below) is a small sample database called
"CreateQueries2 .mdb". Form 6 in this database illustrates how to create a
Where condition in code with a multi-select listbox. See the code behind
the form for details. In broad outline, it will work something like this:

For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

This code loops through the values in the listbox. When it reaches one that is selected, it adds to the Where clause. When it reaches the end, it lops the last comma off of the Where clause.

(The actual syntax of the strWhere clause will vary depending on details of your listbox control.)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org
"William Wisnieski" <ww********@adm issions.umass.e du> wrote in message
news:3f******** @news-1.oit.umass.edu ...
Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) based on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need
to
make the second list box (lstAcadPlan) a multi-select list box and pass

the
values to the filter. I have no idea how to include that in my code and

was
wondering if anyone had any ideas on what I should do. Here is the code

I have so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_ Click()
Me.sfrmSearchRe sults.Visible = True
Me.lblSubformIn structions.Visi ble = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstColle ge & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadP lan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = '" & Me.lstAcadPlan & "'"
Else
strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
End If
End If
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt er = strWhere
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt erOn = True
End Sub

Thank you,

William


Nov 12 '05 #5
OK. Sorry, I missed a bit. Try this:

strWhere = "Where [College] = '" & Me.lstCollege & "' and "
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@adm issions.umass.e du> wrote in message
news:3f******** @news-1.oit.umass.edu ...
Thanks Roger,

I tried your code. I get a run time error and when I go to debug, it seems to have combined the college and academic plan. For example, strWhere
filter should show "[College] = CAS and [AcadPlan] = Art, Or Philosophy, Or History". But it is showing [College]=CAS, Art, Philosophy, History."

William
"Roger Carlson" <NO************ **@hotmail.com> wrote in message
news:O7******** ********@tk2msf tngp13.phx.gbl. ..
On my website (see sig below) is a small sample database called
"CreateQueries2 .mdb". Form 6 in this database illustrates how to create a
Where condition in code with a multi-select listbox. See the code behind the form for details. In broad outline, it will work something like this:
For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

This code loops through the values in the listbox. When it reaches one that
is selected, it adds to the Where clause. When it reaches the end, it

lops
the last comma off of the Where clause.

(The actual syntax of the strWhere clause will vary depending on details

of
your listbox control.)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org
"William Wisnieski" <ww********@adm issions.umass.e du> wrote in message
news:3f******** @news-1.oit.umass.edu ...
Hello Everyone,

I have a main form with a datasheet subform that I use to query by form. After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records based on the two criteria. The criteria used on the main form are values selected in two list boxes. When the user clicks on the first list box (lstCollege), it returns values in the second list box (lstAcadPlan) based on the first. The user then clicks on the cmdShowResults to filter and return records in the datasheet subform. This works fine except for one problem. Both list boxes are set up for single select values--I now need
to
make the second list box (lstAcadPlan) a multi-select list box and pass the
values to the filter. I have no idea how to include that in my code
and was
wondering if anyone had any ideas on what I should do. Here is the

code I have so far that works fine as long as only one value is selected in
the second list box:

Private Sub cmdShowResults_ Click()
Me.sfrmSearchRe sults.Visible = True
Me.lblSubformIn structions.Visi ble = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstColle ge & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadP lan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = '" &
Me.lstAcadPlan & "'"
Else
strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
End If
End If
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt er = strWhere
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt erOn = True
End Sub

Thank you,

William



Nov 12 '05 #6
On Mon, 22 Dec 2003 14:11:52 -0500, "William Wisnieski"
<ww********@adm issions.umass.e du> wrote:

IN clause: that's a learning opportunity.

aaa, bbb etc represent possible text values of the selected items.

SelectedItems: slip of the pen. I meant: selected items. The Selected
property of a multiselect listbox contains the selected items. See
help file for more details.

-Tom.

Thank you for your reply Tom.

I'm not familiar with the IN clause. What do aaa, bbb, ccc represent? The
values of second list box will change depending on what is selected in the
first list box. For example, there can be up to 50 distinct Academic Plans
for each College chosen in the first list box. Also, what are the
"SelectedItems " collection?

William
"Tom van Stiphout" <to*****@no.spa m.cox.net> wrote in message
news:7n******* *************** **********@4ax. com...
On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
<ww********@adm issions.umass.e du> wrote:

Check the SelectedItems collection. Then build an IN clause as part of
your WHERE statement:
... and AcadPlan in ('aaa', 'bbb', 'ccc')

-Tom.

>Hello Everyone,
>
>I have a main form with a datasheet subform that I use to query by form.
>After the user selects two criteria on the main form and clicks the
>cmdShowResul ts button on the main form, the subform returns the records
>based on the two criteria. The criteria used on the main form are values
>selected in two list boxes. When the user clicks on the first list box
>(lstCollege) , it returns values in the second list box (lstAcadPlan)based >on the first. The user then clicks on the cmdShowResults to filter and
>return records in the datasheet subform. This works fine except for one
>problem. Both list boxes are set up for single select values--I now needto >make the second list box (lstAcadPlan) a multi-select list box and passthe >values to the filter. I have no idea how to include that in my code andwas >wondering if anyone had any ideas on what I should do. Here is the codeI >have so far that works fine as long as only one value is selected in the
>second list box:
>
>Private Sub cmdShowResults_ Click()
>Me.sfrmSearchR esults.Visible = True
>Me.lblSubformI nstructions.Vis ible = True
> Dim strWhere As String
> Dim rst As Recordset
>
> If Len(Me.lstColle ge & "") > 0 Then
> strWhere = "[College] = '" & Me.lstCollege & "'"
> End If
>
> If Len(Me.lstAcadP lan & "") > 0 Then
> If Len(strWhere) > 0 Then
> strWhere = strWhere & "And [AcadPlan] = '" & Me.lstAcadPlan &
>"'"
> Else
> strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
> End If
> End If
> Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt er = strWhere
> Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt erOn = True
>End Sub
>
>Thank you,
>
>William
>


Nov 12 '05 #7
William Wisnieski wrote:
Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) based
on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need to
make the second list box (lstAcadPlan) a multi-select list box and pass the
values to the filter. I have no idea how to include that in my code and was
wondering if anyone had any ideas on what I should do. Here is the code I
have so far that works fine as long as only one value is selected in the
second list box:


This is aircode and not tested but close enough for you to play with
If Me.lstCollege.I temsSelected.Co unt = 0 or Me.lstPlans.Cou nt = 0 then
msgbox "Please select the college and then the academic plans."
else
Dim strWhere as string
Dim strHold As String
Dim varID as Variant
strWhere = "[College] = '" & Me.lstCollege & "' And "
For each varID in Me.lstPlans.Ite msSelected 'loop through all items selected

strHold = strHold & Me.lstPlans.Ite mdata(varID) & ", "
Next
strHold = :(" & Left(strHold,Le n(strHold)-2) & ")" 'remove space and comma,
surround in parantheses
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt er = strWhere & " And AcadPlan
In " & strHold
Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt erOn = True
end if

Check out the IN predicate for SQL
Nov 12 '05 #8
Thanks Roger. I used your code and it works great. I'm trying to modify it
now for two reasons. Now, I have to add some more search criteria (State,
HonorsCollege, EMPLID, LastName) on the main form. Second, I need the user
to be able to select from the college list without being required to select
from the AcadPlan list.

Here's what I have that works so far:

Private Sub cmdShowResults_ Click()
Me.sfrmRoundUpS earchResults.Vi sible = True
Me.lblSubformIn structions.Visi ble = True
Dim strWhere As String
Dim rst As Recordset

strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

Forms(Me.Name)( "sfrmRoundUpSea rchResults").Fo rm.Filter = strWhere
Forms(Me.Name)( "sfrmRoundUpSea rchResults").Fo rm.FilterOn = True

=============== =============== ====

Now here is what I have added. The good news is it doesn't return any
errors. The bad news is it doesn't filter any records:

Private Sub cmdShowResults_ Click()
Me.sfrmRoundUpS earchResults.Vi sible = True
Me.lblSubformIn structions.Visi ble = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstColle ge & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadP lan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If

If Len(Me.lstHonor s & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [HonorsCollege] = '" & Me.lstHonors &
"'"
Else
strWhere = "[HonorsCollege] = '" & Me.lstHonors & "'"
End If

End If
If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If

If Len(Me.txtEMPLI D & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID & "'"
Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If
End If

If Len(Me.txtLast & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
End If

Forms(Me.Name)( "sfrmRoundUpSea rchResults").Fo rm.Filter = strWhere
Forms(Me.Name)( "sfrmRoundUpSea rchResults").Fo rm.FilterOn = True

Thanks again for your help. It is much appreciated.

William



"Roger Carlson" <NO************ **@hotmail.com> wrote in message
news:uc******** ******@TK2MSFTN GP09.phx.gbl...
OK. Sorry, I missed a bit. Try this:

strWhere = "Where [College] = '" & Me.lstCollege & "' and "
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@adm issions.umass.e du> wrote in message
news:3f******** @news-1.oit.umass.edu ...
Thanks Roger,

I tried your code. I get a run time error and when I go to debug, it seems
to have combined the college and academic plan. For example, strWhere
filter should show "[College] = CAS and [AcadPlan] = Art, Or Philosophy,

Or
History". But it is showing [College]=CAS, Art, Philosophy, History."

William
"Roger Carlson" <NO************ **@hotmail.com> wrote in message
news:O7******** ********@tk2msf tngp13.phx.gbl. ..
On my website (see sig below) is a small sample database called
"CreateQueries2 .mdb". Form 6 in this database illustrates how to
create a Where condition in code with a multi-select listbox. See the code behind the form for details. In broad outline, it will work something like this:
For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

This code loops through the values in the listbox. When it reaches
one
that
is selected, it adds to the Where clause. When it reaches the end, it

lops
the last comma off of the Where clause.

(The actual syntax of the strWhere clause will vary depending on
details of
your listbox control.)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org
"William Wisnieski" <ww********@adm issions.umass.e du> wrote in message
news:3f******** @news-1.oit.umass.edu ...
> Hello Everyone,
>
> I have a main form with a datasheet subform that I use to query by

form. > After the user selects two criteria on the main form and clicks the
> cmdShowResults button on the main form, the subform returns the records > based on the two criteria. The criteria used on the main form are

values
> selected in two list boxes. When the user clicks on the first list box > (lstCollege), it returns values in the second list box (lstAcadPlan)

based
> on the first. The user then clicks on the cmdShowResults to filter and > return records in the datasheet subform. This works fine except for one > problem. Both list boxes are set up for single select values--I now

need
to
> make the second list box (lstAcadPlan) a multi-select list box and pass the
> values to the filter. I have no idea how to include that in my code and was
> wondering if anyone had any ideas on what I should do. Here is the code
I
> have so far that works fine as long as only one value is selected in

the > second list box:
>
> Private Sub cmdShowResults_ Click()
> Me.sfrmSearchRe sults.Visible = True
> Me.lblSubformIn structions.Visi ble = True
> Dim strWhere As String
> Dim rst As Recordset
>
> If Len(Me.lstColle ge & "") > 0 Then
> strWhere = "[College] = '" & Me.lstCollege & "'"
> End If
>
> If Len(Me.lstAcadP lan & "") > 0 Then
> If Len(strWhere) > 0 Then
> strWhere = strWhere & "And [AcadPlan] = '" &

Me.lstAcadPlan
&
> "'"
> Else
> strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
> End If
> End If
> Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt er = strWhere
> Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt erOn = True
> End Sub
>
> Thank you,
>
> William
>
>



Nov 12 '05 #9
When you say it does not filter, do you mean All records are returned or No
records are returned?

Well, I see some obvious problems, none of which may be the major cause.

1) This section:
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
should have an AND like so:
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND [AcadPlan] IN ("
2) This section: If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID & "'"
Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If is using two different datatypes. If EMPLID is text, the first should be
used, if it is numeric, the second should be used

3) This is not causing a problem yet, but may in the future. If you have a
person with a Last name of O'Brien, this code will fail: If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
To fix, replace each apostrophe (') with TWO quotes ("")
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = """ & Me.txtLast & """"
Else
strWhere = "[Last] = """ & Me.txtLast & """"
End If
Now, as I said, these may not be the cause of the problem at hand. The best
thing to do in a situation like this is to put the following line:

Debug.Print strWhere

just before the filtering line. Then put a break point on the first filter
line. The code will stop before the filter executes and if you look in the
Debug (Immediate) window, you will see exactly what your Where clause looks
like. You can even cut and paste it into a query and see what it is
acutally returning or what error messages it produces.

HTH

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@adm issions.umass.e du> wrote in message
news:3f******** @news-1.oit.umass.edu ... Thanks Roger. I used your code and it works great. I'm trying to modify it now for two reasons. Now, I have to add some more search criteria (State,
HonorsCollege, EMPLID, LastName) on the main form. Second, I need the user to be able to select from the college list without being required to select from the AcadPlan list.

Here's what I have that works so far:

Private Sub cmdShowResults_ Click()
Me.sfrmRoundUpS earchResults.Vi sible = True
Me.lblSubformIn structions.Visi ble = True
Dim strWhere As String
Dim rst As Recordset

strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

Forms(Me.Name)( "sfrmRoundUpSea rchResults").Fo rm.Filter = strWhere
Forms(Me.Name)( "sfrmRoundUpSea rchResults").Fo rm.FilterOn = True

=============== =============== ====

Now here is what I have added. The good news is it doesn't return any
errors. The bad news is it doesn't filter any records:

Private Sub cmdShowResults_ Click()
Me.sfrmRoundUpS earchResults.Vi sible = True
Me.lblSubformIn structions.Visi ble = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstColle ge & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadP lan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If

If Len(Me.lstHonor s & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [HonorsCollege] = '" & Me.lstHonors & "'"
Else
strWhere = "[HonorsCollege] = '" & Me.lstHonors & "'"
End If

End If
If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If

If Len(Me.txtEMPLI D & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID & "'"
Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If
End If

If Len(Me.txtLast & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
End If

Forms(Me.Name)( "sfrmRoundUpSea rchResults").Fo rm.Filter = strWhere
Forms(Me.Name)( "sfrmRoundUpSea rchResults").Fo rm.FilterOn = True

Thanks again for your help. It is much appreciated.

William



"Roger Carlson" <NO************ **@hotmail.com> wrote in message
news:uc******** ******@TK2MSFTN GP09.phx.gbl...
OK. Sorry, I missed a bit. Try this:

strWhere = "Where [College] = '" & Me.lstCollege & "' and "
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.Lis tCount - 1
If lstAcadPlan.Sel ected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Col umn(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@adm issions.umass.e du> wrote in message
news:3f******** @news-1.oit.umass.edu ...
Thanks Roger,

I tried your code. I get a run time error and when I go to debug, it

seems
to have combined the college and academic plan. For example, strWhere
filter should show "[College] = CAS and [AcadPlan] = Art, Or Philosophy,
Or
History". But it is showing [College]=CAS, Art, Philosophy, History."

William
"Roger Carlson" <NO************ **@hotmail.com> wrote in message
news:O7******** ********@tk2msf tngp13.phx.gbl. ..
> On my website (see sig below) is a small sample database called
> "CreateQueries2 .mdb". Form 6 in this database illustrates how to create
a
> Where condition in code with a multi-select listbox. See the code

behind
> the form for details. In broad outline, it will work something like

this:
>
> For i = 0 To lstAcadPlan.Lis tCount - 1
> If lstAcadPlan.Sel ected(i) Then
> strWhere = strWhere & "'" & lstAcadPlan.Col umn(0, i) & "', "
> End If
> Next i
> strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
>
> This code loops through the values in the listbox. When it reaches one that
> is selected, it adds to the Where clause. When it reaches the end,
it lops
> the last comma off of the Where clause.
>
> (The actual syntax of the strWhere clause will vary depending on

details of
> your listbox control.)
>
> --
> --Roger Carlson
> www.rogersaccesslibrary.com
> Reply to: Roger dot Carlson at Spectrum-Health dot Org
>
>
> "William Wisnieski" <ww********@adm issions.umass.e du> wrote in message > news:3f******** @news-1.oit.umass.edu ...
> > Hello Everyone,
> >
> > I have a main form with a datasheet subform that I use to query by

form.
> > After the user selects two criteria on the main form and clicks the > > cmdShowResults button on the main form, the subform returns the

records
> > based on the two criteria. The criteria used on the main form are
values
> > selected in two list boxes. When the user clicks on the first list box
> > (lstCollege), it returns values in the second list box
(lstAcadPlan) based
> > on the first. The user then clicks on the cmdShowResults to filter and
> > return records in the datasheet subform. This works fine except
for one
> > problem. Both list boxes are set up for single select values--I
now need
> to
> > make the second list box (lstAcadPlan) a multi-select list box and

pass
> the
> > values to the filter. I have no idea how to include that in my

code and
> was
> > wondering if anyone had any ideas on what I should do. Here is
the code
I
> > have so far that works fine as long as only one value is selected
in the
> > second list box:
> >
> > Private Sub cmdShowResults_ Click()
> > Me.sfrmSearchRe sults.Visible = True
> > Me.lblSubformIn structions.Visi ble = True
> > Dim strWhere As String
> > Dim rst As Recordset
> >
> > If Len(Me.lstColle ge & "") > 0 Then
> > strWhere = "[College] = '" & Me.lstCollege & "'"
> > End If
> >
> > If Len(Me.lstAcadP lan & "") > 0 Then
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [AcadPlan] = '" &

Me.lstAcadPlan
&
> > "'"
> > Else
> > strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
> > End If
> > End If
> > Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt er = strWhere
> > Forms(Me.Name)( "sfrmSearchResu lts").Form.Filt erOn = True
> > End Sub
> >
> > Thank you,
> >
> > William
> >
> >
>
>



Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2039
by: N.K. | last post by:
Hello, I'm trying to find a way to flexibly filter a column in a row of data read from a file. I will have data type of that column, the operation to be performed on it (like >, <, <= etc.) and the value to compare it to stored in a database. If I'm to write it in a procedural language it would be a big case statement like this: case int case <= return (value <= compare value) Can you think of a better way of doing it? I was thinking...
0
1402
by: Freehand313 | last post by:
Hi All, Any body help me in this problem? I want source code for generation very fast possible random combinations for "n,k" but filtering collection of (n,k) combinations such have no more than 4 elements in common? It sees this example:
11
2293
by: Enquiries, Hopkins Research | last post by:
Hi all I have a conundrum that is puzzling me. I have a large codebase in C that I am converting to C++ as fast as possible (i.e. slowly because I keep learning new idioms and stumbling with C++ 'features'). One part of the C code is some optimisation functions that expect a pointer to a function which is sent an array of doubles and returns a double i.e. simplified..
0
1611
by: Keith Shearer | last post by:
I'm having a bit of trouble, moving between controls, when filtering on a form. I'm using a continuous form. At the top I have 2 date fields. I want to filter the data specified between the two date fields ... The problem is ... I'm doing my filtering on the afterupdate event. So I type in a date and press TAB. The filter works and automatically refreshes the form, then the cursor does not move on to the next control, but comes back to...
10
13351
by: milk-jam | last post by:
I'm trying to set my datagridview so that the first row will be left blank and to use it as a filtering filed for the datagridview. Until now I was using 2 datagridview the upper one with a header that sort and one row for filtering and the lower one with no header for the data. Only it is very complicated to work this way cause for example when the user click on sort on the upper datagridview I need to sort the lower grid and so on when...
2
1476
by: Konrad | last post by:
Hi Can you point examples in .NET of filtering (avoiding) displaying web pages with unwanted content on machine with ie? Thanks Konrad
0
1176
by: Khurram | last post by:
Hi, I am using Chr(Integer) Function, to get the Character against that Number. This function I was using in VB6 Code and it was working good, but when I am trying to use it in VB.Net, it through Argument Exception "charcode" must be between range 32... to -32.... *******ecSt = decSt + Chr(Convert.ToInt32(Mult(tok, dD, dN)))
44
6724
by: gokkog | last post by:
Hi there, There's a classic hash function to hash strings, where MULT is defined as "31": //from programming pearls unsigned int hash(char *ptr) { unsigned int h = 0; unsigned char *p = ptr;
3
1937
by: GaryDean | last post by:
I have serveral applications now running that are using the MembershipProvider classes and they are each using their own security tables in SQL Server 2005 instead of the express databases - they are all work well. Now we have a need to have many different asp.net websites and web services use a single security database because they all share the same user and administrator community. It doesn't seem there will be a problem with all of...
9
12617
by: jacob navia | last post by:
Hi I am incorporating 128 Bit integer code into lcc-win and it would be nice to have some code to test this feature. Has anyone here code that uses 128 bit integers? Thanks in advance P.S. This feature is now native in the 64 bit version, i.e.
0
8928
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8766
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9158
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8160
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6026
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4535
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4799
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2698
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.