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.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub
Thank you,
William
19  3507 
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.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub
Thank you,
William
On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> 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
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.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub
Thank you,
William
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.spam.cox.net> wrote in message
news:7n********************************@4ax.com... On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> 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
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)
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.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub
Thank you,
William
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****************@tk2msftngp13.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.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub
Thank you,
William
OK. Sorry, I missed a bit. Try this:
strWhere = "Where [College] = '" & Me.lstCollege & "' and "
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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****************@tk2msftngp13.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.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub
Thank you,
William
On Mon, 22 Dec 2003 14:11:52 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> 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.spam.cox.net> wrote in message
news:7n********************************@4ax.com.. . On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> 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
>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 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.sfrmSearchResults.Visible = True
>Me.lblSubformInstructions.Visible = True
> Dim strWhere As String
> Dim rst As Recordset
>
> If Len(Me.lstCollege & "") > 0 Then
> strWhere = "[College] = '" & Me.lstCollege & "'"
> End If
>
> If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter = strWhere
> Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
>End Sub
>
>Thank you,
>
>William
>
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.ItemsSelected.Count = 0 or Me.lstPlans.Count = 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.ItemsSelected 'loop through all items selected
strHold = strHold & Me.lstPlans.Itemdata(varID) & ", "
Next
strHold = :(" & Left(strHold,Len(strHold)-2) & ")" 'remove space and comma,
surround in parantheses
Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere & " And AcadPlan
In " & strHold
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
end if
Check out the IN predicate for SQL
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.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = 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.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
If Len(Me.lstHonors & "") > 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.txtEMPLID & "") > 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)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True
Thanks again for your help. It is much appreciated.
William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:uc**************@TK2MSFTNGP09.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.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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****************@tk2msftngp13.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.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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.sfrmSearchResults.Visible = True
> Me.lblSubformInstructions.Visible = True
> Dim strWhere As String
> Dim rst As Recordset
>
> If Len(Me.lstCollege & "") > 0 Then
> strWhere = "[College] = '" & Me.lstCollege & "'"
> End If
>
> If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter = strWhere
> Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
> End Sub
>
> Thank you,
>
> William
>
>
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********@admissions.umass.edu> 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.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = 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.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
If Len(Me.lstHonors & "") > 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.txtEMPLID & "") > 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)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True
Thanks again for your help. It is much appreciated.
William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:uc**************@TK2MSFTNGP09.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.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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****************@tk2msftngp13.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.ListCount - 1
> If lstAcadPlan.Selected(i) Then
> strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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.sfrmSearchResults.Visible = True
> > Me.lblSubformInstructions.Visible = True
> > Dim strWhere As String
> > Dim rst As Recordset
> >
> > If Len(Me.lstCollege & "") > 0 Then
> > strWhere = "[College] = '" & Me.lstCollege & "'"
> > End If
> >
> > If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter = strWhere
> > Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
> > End Sub
> >
> > Thank you,
> >
> > William
> >
> >
>
>
Roger....thanks again.
Sorry for the confusion. Regarding the filtering problem, all records are
being returned. Also, I don't quite understand the If Len(strWhere). I'm
not sure which sections you are referring to. Should I be using that
instead of say, If Len(Me.lstCollege & "") > 0 Then.....................
William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... 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********@admissions.umass.edu> 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.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = 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.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
If Len(Me.lstHonors & "") > 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.txtEMPLID & "") > 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)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True
Thanks again for your help. It is much appreciated.
William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:uc**************@TK2MSFTNGP09.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.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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****************@tk2msftngp13.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.ListCount - 1
> > If lstAcadPlan.Selected(i) Then
> > strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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.sfrmSearchResults.Visible = True
> > > Me.lblSubformInstructions.Visible = True
> > > Dim strWhere As String
> > > Dim rst As Recordset
> > >
> > > If Len(Me.lstCollege & "") > 0 Then
> > > strWhere = "[College] = '" & Me.lstCollege & "'"
> > > End If
> > >
> > > If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter = strWhere
> > > Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
> > > End Sub
> > >
> > > Thank you,
> > >
> > > William
> > >
> > >
> >
> >
>
>
Whoa, sorry about that....I see now what sections you are referring
to.....I'm working through it right now and will let you know how I make
out.
Thanks again....
William
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu... Roger....thanks again.
Sorry for the confusion. Regarding the filtering problem, all records are
being returned. Also, I don't quite understand the If Len(strWhere). I'm
not sure which sections you are referring to. Should I be using that
instead of say, If Len(Me.lstCollege & "") > 0 Then.....................
William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... 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********@admissions.umass.edu> 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.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = 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.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
If Len(Me.lstHonors & "") > 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.txtEMPLID & "") > 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)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True
Thanks again for your help. It is much appreciated.
William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:uc**************@TK2MSFTNGP09.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.ListCount - 1
> If lstAcadPlan.Selected(i) Then
> strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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****************@tk2msftngp13.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.ListCount - 1
> > > If lstAcadPlan.Selected(i) Then
> > > strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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.sfrmSearchResults.Visible = True
> > > > Me.lblSubformInstructions.Visible = True
> > > > Dim strWhere As String
> > > > Dim rst As Recordset
> > > >
> > > > If Len(Me.lstCollege & "") > 0 Then
> > > > strWhere = "[College] = '" & Me.lstCollege & "'"
> > > > End If
> > > >
> > > > If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter =
strWhere > > > > Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
> > > > End Sub
> > > >
> > > > Thank you,
> > > >
> > > > William
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Put the Debug line at the bottom of the procedure just above: Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter =
strWhere
Where you have it now displays nothing because strWhere has not yet been set
to anything. You can also move the line around to different points in the
procedure to see what the Where clause looks like at each point. This is
only used for development, however and should be commented out for
production as it uses up processing time.
--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu... Roger,
The debug idea sounds great. I'm trying it right now with no luck though.
nothing shows up in the immediate window. This is what I have:
If Len(Me.lstCollege & "") > 0 Then
Debug.Print strWhere
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
I have set a breakpoint at strWhere = "[College] = '" & Me.lstCollege &
"'" and opened the immediate window. I select the first two criteria on the
form and click the show results button and the line strWhere =
"[College] = '" & Me.lstCollege & "'" gets highlighted in yellow but nothing shows up
in the immediate window.
Sorry for my numerous posts trying to get this thing done!
William
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu... Whoa, sorry about that....I see now what sections you are referring
to.....I'm working through it right now and will let you know how I make
out.
Thanks again....
William
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu... Roger....thanks again.
Sorry for the confusion. Regarding the filtering problem, all records are being returned. Also, I don't quite understand the If Len(strWhere). I'm not sure which sections you are referring to. Should I be using that
instead of say, If Len(Me.lstCollege & "") > 0
Then.....................
William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
> 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********@admissions.umass.edu> 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.sfrmRoundUpSearchResults.Visible = True
> > Me.lblSubformInstructions.Visible = True
> > Dim strWhere As String
> > Dim rst As Recordset
> >
> > strWhere = strWhere & "[AcadPlan] IN ("
> > For i = 0 To lstAcadPlan.ListCount - 1
> > If lstAcadPlan.Selected(i) Then
> > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "',
" > > End If
> > Next i
> > strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
> >
> > Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere > > Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn =
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.sfrmRoundUpSearchResults.Visible = True
> > Me.lblSubformInstructions.Visible = True
> > Dim strWhere As String
> > Dim rst As Recordset
> >
> > If Len(Me.lstCollege & "") > 0 Then
> > strWhere = "[College] = '" & Me.lstCollege & "'"
> > End If
> >
> > If Len(Me.lstAcadPlan & "") > 0 Then
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "[AcadPlan] IN ("
> > For i = 0 To lstAcadPlan.ListCount - 1
> > If lstAcadPlan.Selected(i) Then
> > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "',
" > > End If
> > Next i
> > strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
> > Else
> > strWhere = "[AcadPlan] IN ("
> > For i = 0 To lstAcadPlan.ListCount - 1
> > If lstAcadPlan.Selected(i) Then
> > strWhere = "'" & lstAcadPlan.Column(0, i) & "', "
> > End If
> > Next i
> > strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
> > End If
> >
> > If Len(Me.lstHonors & "") > 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.txtEMPLID & "") > 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)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere > > Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn =
True > >
> > Thanks again for your help. It is much appreciated.
> >
> > William
> >
> >
> >
> >
> >
> >
> >
> > "Roger Carlson" <NO**************@hotmail.com> wrote in message
> > news:uc**************@TK2MSFTNGP09.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.ListCount - 1
> > > If lstAcadPlan.Selected(i) Then
> > > strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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****************@tk2msftngp13.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.ListCount - 1
> > > > > If lstAcadPlan.Selected(i) Then
> > > > > strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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.sfrmSearchResults.Visible = True
> > > > > > Me.lblSubformInstructions.Visible = True
> > > > > > Dim strWhere As String
> > > > > > Dim rst As Recordset
> > > > > >
> > > > > > If Len(Me.lstCollege & "") > 0 Then
> > > > > > strWhere = "[College] = '" & Me.lstCollege & "'"
> > > > > > End If
> > > > > >
> > > > > > If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter =
strWhere > > > > > > Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True > > > > > > End Sub
> > > > > >
> > > > > > Thank you,
> > > > > >
> > > > > > William
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Hello Again,
I finally was able to play around with the debug feature and can at least
see results. Here are the ongoing problems I'm having.
1. I can't get the results from the lstAcadPlan list box selection to
concatenate with other criteria (I get a run time error).
2. If I only want to choose an item from one of the criteria (lstCollege
for example), I also get a run time error.
When I use just the following code by itself, it works--as long I select one
item from the lstCollege list box and any number of items from the
lstAcadPlan list box.
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
Debug.Print strWhere
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Here is what is displayed in the debug window (I selected three academic
plans):
[AcadPlan] IN ('BA-AFROAM',
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH',
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART',
If I add the following to the code above and select the AcadPlan AND State,
I get a runtime error 2448 (can't assign a value to this object). If I
select just the State and I get the same run time error.
If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Debug.Print strWhere
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If
Here is what then gets displayed in the debug Window
[AcadPlan] IN)
It should be returning
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART', AND [State] = 'MA'
Thanks for your help...
William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... 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********@admissions.umass.edu> 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.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = 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.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset
If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
If Len(Me.lstHonors & "") > 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.txtEMPLID & "") > 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)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True
Thanks again for your help. It is much appreciated.
William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:uc**************@TK2MSFTNGP09.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.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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****************@tk2msftngp13.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.ListCount - 1
> > If lstAcadPlan.Selected(i) Then
> > strWhere = strWhere & "'" & lstAcadPlan.Column(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********@admissions.umass.edu> 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.sfrmSearchResults.Visible = True
> > > Me.lblSubformInstructions.Visible = True
> > > Dim strWhere As String
> > > Dim rst As Recordset
> > >
> > > If Len(Me.lstCollege & "") > 0 Then
> > > strWhere = "[College] = '" & Me.lstCollege & "'"
> > > End If
> > >
> > > If Len(Me.lstAcadPlan & "") > 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)("sfrmSearchResults").Form.Filter = strWhere
> > > Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
> > > End Sub
> > >
> > > Thank you,
> > >
> > > William
> > >
> > >
> >
> >
>
>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Actually, you should get a return string like this:
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART') AND [State] = 'MA'
The AND State = 'MA' should be outside the closing parenthesis of the
IN() clause. You're neglecting to put the closing parenthesis after
the last selected item in the ListBox.
Here is a function I created to get items from ListBoxes (Acc97):
Under the Case dbText, if using Access 2000 & greater use:
Replace(lst.ItemData(row), "'", "''")
instead of
ReplaceStr(lst.ItemData(row), "'", "''", 0)
Public Function getIDs(lst As Control, ByVal intType As Integer) _
As String
' Purpose:
' Get a list of the item IDs into a comma separated string
' In:
' lst A ref to a list box control
' intType One of the dbText, dbDate, dbTime, dbNumeric
' Out:
' A string of comma-delimited IDs. Format: "1,2,3,4"
' If the intType is undefined an empty string is returned.
' Created:
' mgf 8mar2000
' Modified:
' mgf 10mar2000 Added intType selection
'
Dim row As Variant
For Each row In lst.ItemsSelected
Dim strIDs As String
Select Case intType
Case dbText
strIDs = strIDs & "'" & _
ReplaceStr(lst.ItemData(row), "'", "''", 0) & "',"
Case dbDate, dbTime
' Assumes USA date format mm/dd/yyyy
strIDs = strIDs & "#" & lst.ItemData(row) & "#,"
Case dbNumeric
strIDs = strIDs & lst.ItemData(row) & ","
Case Else
' Don't know how to handle this type
Exit Function
End Select
Next row
' Return string w/o trailing comma
getIDs = Left$(strIDs, Len(strIDs) - 1)
End Function
It is called this way:
' Check if any data in ListBox
If lstAvailable.ItemsSelected.Count > 0 Then
Dim strResult As String
' Get selected items IDs
strResult = getIDs(lstAvailable, dbNumeric)
strWhere = " Items In (" & strResult & ") "
End If
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP/DRu4echKqOuFEgEQK3vQCfTTmCDIhyH1TvV4ITHjDX9M4WdawA n3f2
QZR+Xw0zx0fkgGb3ikptpoSA
=S6v1
-----END PGP SIGNATURE-----
William Wisnieski wrote: Hello Again,
I finally was able to play around with the debug feature and can at least
see results. Here are the ongoing problems I'm having.
1. I can't get the results from the lstAcadPlan list box selection to
concatenate with other criteria (I get a run time error).
2. If I only want to choose an item from one of the criteria (lstCollege
for example), I also get a run time error.
When I use just the following code by itself, it works--as long I select one
item from the lstCollege list box and any number of items from the
lstAcadPlan list box.
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
Debug.Print strWhere
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Here is what is displayed in the debug window (I selected three academic
plans):
[AcadPlan] IN ('BA-AFROAM',
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH',
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART',
If I add the following to the code above and select the AcadPlan AND State,
I get a runtime error 2448 (can't assign a value to this object). If I
select just the State and I get the same run time error.
If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Debug.Print strWhere
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If
Here is what then gets displayed in the debug Window
[AcadPlan] IN)
It should be returning
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART', AND [State] = 'MA'
Thanks for your help...
William
Why don't you go one step further with all the excellent help and put
the code to generate the IN clause in a reusable function?
eg
Public Function GetListBoxValues(byref plst as Listbox,
Optional Byref pblnIsString as Boolean) as string
' IN : plst - a multi select list box
' pblnIsString 0 - '[value]' if True, [Value] if False
' OUT: A string representing the innards of your IN Clause
'
your previous code...(with a few mods)
End Function
usage
Function BuildWhereClause() as string
dim strWhere as String
If me.lstWhatever.ItemsSelected.Count > 0 then
if Len(strWhere) > 0 then strWhere = strWhere & " AND "
strWhere = strWhere & "SomeField IN(" &
GetListBoxValues(me.lstWhatever, True) & ")"
end if
etc
BuildWhereClause = strWhere
End Function
Just would seem to me to make the whole thing a little more reusable
and flexible....
Peter
Thanks for everyone's generous help on this....still trying to work through
it.
MG, I've tried putting a closing parentheses at the end of the line but it
won't let me....
I'm trying to change this:
strWhere = strWhere & "[AcadPlan] IN ("
to this: strWhere = strWhere & "[AcadPlan] IN (")
But I get an error message that says, "expected end of statement. Where
should the closing parentheses be?
Here's all of my code again:
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
Debug.Print strWhere
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Thanks,
William
"MGFoster" <me@privacy.com> wrote in message
news:Xk*******************@newsread1.news.pas.eart hlink.net... -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Actually, you should get a return string like this:
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART') AND [State] = 'MA'
The AND State = 'MA' should be outside the closing parenthesis of the
IN() clause. You're neglecting to put the closing parenthesis after
the last selected item in the ListBox.
Here is a function I created to get items from ListBoxes (Acc97):
Under the Case dbText, if using Access 2000 & greater use:
Replace(lst.ItemData(row), "'", "''")
instead of
ReplaceStr(lst.ItemData(row), "'", "''", 0)
Public Function getIDs(lst As Control, ByVal intType As Integer) _
As String
' Purpose:
' Get a list of the item IDs into a comma separated string
' In:
' lst A ref to a list box control
' intType One of the dbText, dbDate, dbTime, dbNumeric
' Out:
' A string of comma-delimited IDs. Format: "1,2,3,4"
' If the intType is undefined an empty string is returned.
' Created:
' mgf 8mar2000
' Modified:
' mgf 10mar2000 Added intType selection
'
Dim row As Variant
For Each row In lst.ItemsSelected
Dim strIDs As String
Select Case intType
Case dbText
strIDs = strIDs & "'" & _
ReplaceStr(lst.ItemData(row), "'", "''", 0) & "',"
Case dbDate, dbTime
' Assumes USA date format mm/dd/yyyy
strIDs = strIDs & "#" & lst.ItemData(row) & "#,"
Case dbNumeric
strIDs = strIDs & lst.ItemData(row) & ","
Case Else
' Don't know how to handle this type
Exit Function
End Select
Next row
' Return string w/o trailing comma
getIDs = Left$(strIDs, Len(strIDs) - 1)
End Function
It is called this way:
' Check if any data in ListBox
If lstAvailable.ItemsSelected.Count > 0 Then
Dim strResult As String
' Get selected items IDs
strResult = getIDs(lstAvailable, dbNumeric)
strWhere = " Items In (" & strResult & ") "
End If
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP/DRu4echKqOuFEgEQK3vQCfTTmCDIhyH1TvV4ITHjDX9M4WdawA n3f2
QZR+Xw0zx0fkgGb3ikptpoSA
=S6v1
-----END PGP SIGNATURE-----
William Wisnieski wrote:
Hello Again,
I finally was able to play around with the debug feature and can at
least see results. Here are the ongoing problems I'm having.
1. I can't get the results from the lstAcadPlan list box selection to
concatenate with other criteria (I get a run time error).
2. If I only want to choose an item from one of the criteria
(lstCollege for example), I also get a run time error.
When I use just the following code by itself, it works--as long I select
one item from the lstCollege list box and any number of items from the
lstAcadPlan list box.
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
Debug.Print strWhere
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Here is what is displayed in the debug window (I selected three academic
plans):
[AcadPlan] IN ('BA-AFROAM',
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH',
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART',
If I add the following to the code above and select the AcadPlan AND
State, I get a runtime error 2448 (can't assign a value to this object). If I
select just the State and I get the same run time error.
If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Debug.Print strWhere
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If
Here is what then gets displayed in the debug Window
[AcadPlan] IN)
It should be returning
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART', AND [State] = 'MA'
Thanks for your help...
William
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
The In() clause should be formatted like this (string items):
IN ('item2', 'item2', 'item3')
The closing parenthesis is after 'item3' - this indicates that the
list (the values inside the parentheses) is ended.
Your post indicates you don't understand how to concatenate strings in
VBA, but your For...Next loop looks like it should produce a correct
IN() clause - IF any items are selected; otherwise, you'll have a
Where string like this:
[AcadPlan] IN () -- an empty list, which causes an error.
You can check for selected items by:
If Me!lstAcadPlan.ItemsSelected.Count > 0 Then
' There are selected items
... etc. ...
You could put a break-point on:
strWhere = Left(strWhere, Len(strWhere) - 2) & ") "
step thru it (F8) to the next command and print the value of the
variable strWhere in the debug window:
? strWhere
to see if it is formatted correctly.
HTH,
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP/JQY4echKqOuFEgEQJ18ACgmFxZuxG1Nq05OX51Zb+vUXseRVQA oLAO
VwMh3FCvvLN9HDc5uI6guZP0
=i+dA
-----END PGP SIGNATURE-----
William Wisnieski wrote: Thanks for everyone's generous help on this....still trying to work through
it.
MG, I've tried putting a closing parentheses at the end of the line but it
won't let me....
I'm trying to change this:
strWhere = strWhere & "[AcadPlan] IN ("
to this: strWhere = strWhere & "[AcadPlan] IN (")
But I get an error message that says, "expected end of statement. Where
should the closing parentheses be?
Here's all of my code again:
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
Debug.Print strWhere
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Thanks,
William
"MGFoster" <me@privacy.com> wrote in message
news:Xk*******************@newsread1.news.pas.eart hlink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Actually, you should get a return string like this:
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART') AND [State] = 'MA'
The AND State = 'MA' should be outside the closing parenthesis of the
IN() clause. You're neglecting to put the closing parenthesis after
the last selected item in the ListBox.
Here is a function I created to get items from ListBoxes (Acc97):
Under the Case dbText, if using Access 2000 & greater use:
Replace(lst.ItemData(row), "'", "''")
instead of
ReplaceStr(lst.ItemData(row), "'", "''", 0)
Public Function getIDs(lst As Control, ByVal intType As Integer) _
As String
' Purpose:
' Get a list of the item IDs into a comma separated string
' In:
' lst A ref to a list box control
' intType One of the dbText, dbDate, dbTime, dbNumeric
' Out:
' A string of comma-delimited IDs. Format: "1,2,3,4"
' If the intType is undefined an empty string is returned.
' Created:
' mgf 8mar2000
' Modified:
' mgf 10mar2000 Added intType selection
'
Dim row As Variant
For Each row In lst.ItemsSelected
Dim strIDs As String
Select Case intType
Case dbText
strIDs = strIDs & "'" & _
ReplaceStr(lst.ItemData(row), "'", "''", 0) & "',"
Case dbDate, dbTime
' Assumes USA date format mm/dd/yyyy
strIDs = strIDs & "#" & lst.ItemData(row) & "#,"
Case dbNumeric
strIDs = strIDs & lst.ItemData(row) & ","
Case Else
' Don't know how to handle this type
Exit Function
End Select
Next row
' Return string w/o trailing comma
getIDs = Left$(strIDs, Len(strIDs) - 1)
End Function
It is called this way:
' Check if any data in ListBox
If lstAvailable.ItemsSelected.Count > 0 Then
Dim strResult As String
' Get selected items IDs
strResult = getIDs(lstAvailable, dbNumeric)
strWhere = " Items In (" & strResult & ") "
End If
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP/DRu4echKqOuFEgEQK3vQCfTTmCDIhyH1TvV4ITHjDX9M4WdawA n3f2
QZR+Xw0zx0fkgGb3ikptpoSA
=S6v1
-----END PGP SIGNATURE-----
< snip previous posts >
Finally, thanks to all of your help, I was able to get the form up and
running without any problems. Here is the final code I used behind my Show
Results button:
Private Sub cmdShowResults_Click()
Me.sfrmUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Me.cmdPrint.Visible = True
Dim rst As Recordset
Dim strWhere As String
If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If
If Me!lstAcadPlan.ItemsSelected.Count > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "AND" & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If
If Len(Me.lstHonors & "") > 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.txtEMPLID & "") > 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)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
Set rst = Forms("frmSearch").sfrmSearchResults.Form.Recordse t
If rst.RecordCount = 0 Then
MsgBox "There were no records that matched the criteria. Click OK to
search again.", vbOKOnly, "Search"
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = False
Me.sfrmSearchResults.Visible = False
Me.lblSubformInstructions.Visible = False
Me.lstCollege = Null
Me.lstAcadPlan = Null
Me.lstHonors = Null
Me.cboState = Null
Me.txtEMPLID = Null
Me.txtLast = Null
End If
End Sub
Thanks again to all of you and this invaluable newsgroup!
William
"Pink Panther" <Pi*************@mail.com> wrote in message
news:ec**************************@posting.google.c om... Why don't you go one step further with all the excellent help and put
the code to generate the IN clause in a reusable function?
eg
Public Function GetListBoxValues(byref plst as Listbox,
Optional Byref pblnIsString as Boolean) as string
' IN : plst - a multi select list box
' pblnIsString 0 - '[value]' if True, [Value] if False
' OUT: A string representing the innards of your IN Clause
'
your previous code...(with a few mods)
End Function
usage
Function BuildWhereClause() as string
dim strWhere as String
If me.lstWhatever.ItemsSelected.Count > 0 then
if Len(strWhere) > 0 then strWhere = strWhere & " AND "
strWhere = strWhere & "SomeField IN(" &
GetListBoxValues(me.lstWhatever, True) & ")"
end if
etc
BuildWhereClause = strWhere
End Function
Just would seem to me to make the whole thing a little more reusable
and flexible....
Peter
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message news:<3f********@news-1.oit.umass.edu>... Finally, thanks to all of your help, I was able to get the form up and
running without any problems. Here is the final code I used behind my Show
Results button:
Private Sub cmdShowResults_Click()
<SNIP> For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If
Well, first off, well done on getting it to work. Now that it works,
it might be time for some tweaking...
Instead of looping through all the items in the listbox and seeing if
they're selected...
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
Why not use the ItemsSelected collection of the listbox and just do
the same thing? Won't make much of a difference if you don't have a
ton of items in your listbox, but if you do, it could make your app
run faster...
dim varItem as Variant
For each varItem in lstAcadPlan.ItemsSelected
'do something
Next varItem
there's code that shows how to do this at www.mvps.org/access
look for something like "listbox" + varitem and you'll find an example
of iterating through the list of selected items...
HTH,
Pieter This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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...
|
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...
|
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...
|
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...
|
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...
| | |
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
|
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...
|
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 =...
|
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...
|
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
...
|
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,...
| | |
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...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
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...
|
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,...
|
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...
| | |
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
