Multiselect List Box

  • Thread starter Thread starter sse1979
  • Start date Start date
S

sse1979

I'm trying to modify Allen Browne's Search form to use a multiselect list
box, but my listbox has only one column. I want to filter my list by
choosing multiple counties in my listbox (I also have other choices in text
boxes of state, date fields, etc.). I have pasted my code below for my
filter button. I'm not great with writing and deciphering code, but seem to
do ok sometimes in modifying, but I can't get this one. I've read through
many other posts here, and still can't figure it out.
Everything works till I put in the listbox. txtFilterCity is the name of my
lb. County is the name of the field I am querying. I also couldn't get the
reset button to clear the listbox, so help with that would be appreciated too.
Thank you in advance.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If


'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If


'Yes/No field and combo example. If combo is blank or contains "ALL", we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

If Not IsNull(Me.txtAcresMin) Then
strWhere = strWhere & "([Acreage] >= " & Me.txtAcresMin & ") AND "
End If

If Not IsNull(Me.txtAcresMax) Then
strWhere = strWhere & "([Acreage] <= " & Me.txtAcresMax & ") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
 
Once you make a list box Multiselect, any reference to it will always return
Null, whether or not anything's selected.

Replace

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If

with

'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "
End If

Make sure you add the following declarations to the code:

Dim strCounty As String
Dim varSelected As Variant

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



sse1979 said:
I'm trying to modify Allen Browne's Search form to use a multiselect list
box, but my listbox has only one column. I want to filter my list by
choosing multiple counties in my listbox (I also have other choices in
text
boxes of state, date fields, etc.). I have pasted my code below for my
filter button. I'm not great with writing and deciphering code, but seem
to
do ok sometimes in modifying, but I can't get this one. I've read through
many other posts here, and still can't figure it out.
Everything works till I put in the listbox. txtFilterCity is the name of
my
lb. County is the name of the field I am querying. I also couldn't get
the
reset button to clear the listbox, so help with that would be appreciated
too.
Thank you in advance.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards;
_
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """)
and "
End If


'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If


'Yes/No field and combo example. If combo is blank or contains "ALL",
we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

If Not IsNull(Me.txtAcresMin) Then
strWhere = strWhere & "([Acreage] >= " & Me.txtAcresMin & ") AND "
End If

If Not IsNull(Me.txtAcresMax) Then
strWhere = strWhere & "([Acreage] <= " & Me.txtAcresMax & ") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...
 
Thank you for your assistance. Though I came up with a problem when typing in
& _ (That is simply an ampersand and a underscore, correct?)
""", "
After I enter that code, those two lines turn red and I get a vb error
window saying: Compile error: Expected: line number or label or statement or
end of statement.
I entered the declaration Doug mentioned to do, but when I try new code
suggested, I get this error. Any ideas? Please? :)
So the code that I edited now looks like this:

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and"
End If

Douglas J. Steele said:
Once you make a list box Multiselect, any reference to it will always return
Null, whether or not anything's selected.

Replace

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If

with

'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "
End If

Make sure you add the following declarations to the code:

Dim strCounty As String
Dim varSelected As Variant

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



sse1979 said:
I'm trying to modify Allen Browne's Search form to use a multiselect list
box, but my listbox has only one column. I want to filter my list by
choosing multiple counties in my listbox (I also have other choices in
text
boxes of state, date fields, etc.). I have pasted my code below for my
filter button. I'm not great with writing and deciphering code, but seem
to
do ok sometimes in modifying, but I can't get this one. I've read through
many other posts here, and still can't figure it out.
Everything works till I put in the listbox. txtFilterCity is the name of
my
lb. County is the name of the field I am querying. I also couldn't get
the
reset button to clear the listbox, so help with that would be appreciated
too.
Thank you in advance.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards;
_
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """)
and "
End If


'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If


'Yes/No field and combo example. If combo is blank or contains "ALL",
we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

If Not IsNull(Me.txtAcresMin) Then
strWhere = strWhere & "([Acreage] >= " & Me.txtAcresMin & ") AND "
End If

If Not IsNull(Me.txtAcresMax) Then
strWhere = strWhere & "([Acreage] <= " & Me.txtAcresMax & ") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...

.
Click to expand...
 
When running this code I get a syntax error, missing operator related to: strWhere = strWhere & "([County] IN (" & strCounty & ") and" portion. My code follows, County has been changed to city...Any help is appreciated!

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim strCity As String
Dim varSelected As Variant
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCity = strCity & """" & Me.txtFilterCity.ItemData(varSelected)
Next varSelected
strWhere = strWhere & "([City] IN (" & strCity & ") AND "
End If


I am trying to modify Allen Browne's Search form to use a multiselect list
box, but my listbox has only one column. I want to filter my list by
choosing multiple counties in my listbox (I also have other choices in text
boxes of state, date fields, etc.). I have pasted my code below for my
filter button. I am not great with writing and deciphering code, but seem to
do ok sometimes in modifying, but I cannot get this one. I have read through
many other posts here, and still cannot figure it out.
Everything works till I put in the listbox. txtFilterCity is the name of my
lb. County is the name of the field I am querying. I also could not get the
reset button to clear the listbox, so help with that would be appreciated too.
Thank you in advance.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If


'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If


'Yes/No field and combo example. If combo is blank or contains "ALL", we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

If Not IsNull(Me.txtAcresMin) Then
strWhere = strWhere & "([Acreage] >= " & Me.txtAcresMin & ") AND "
End If

If Not IsNull(Me.txtAcresMax) Then
strWhere = strWhere & "([Acreage] <= " & Me.txtAcresMax & ") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
On Saturday, December 05, 2009 7:55 AM Douglas J. Steele wrote:
Once you make a list box Multiselect, any reference to it will always return
Null, whether or not anything's selected.

Replace

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If

with

'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "
End If

Make sure you add the following declarations to the code:

Dim strCounty As String
Dim varSelected As Variant
Click to expand...
On Monday, January 25, 2010 9:35 AM sse1979 wrote:
Thank you for your assistance. Though I came up with a problem when typing in
& _ (That is simply an ampersand and a underscore, correct?)
""", "
After I enter that code, those two lines turn red and I get a vb error
window saying: Compile error: Expected: line number or label or statement or
end of statement.
I entered the declaration Doug mentioned to do, but when I try new code
suggested, I get this error. Any ideas? Please? :)
So the code that I edited now looks like this:

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and"
End If

"Douglas J. Steele" wrote:
Click to expand...
Click to expand...
Click to expand...
 
When running this code I get a syntax error, missing operator related to: strWhere = strWhere & "([County] IN (" & strCounty & ") and" portion. My code follows, County has been changed to city...Any help is appreciated!
Click to expand...

Since the County (or City? which is it???) is a Text field, you must include
either ' or " delimiters around each instance of the county name. You're
putting a " mark ("""" in the code) before the county name but not after. Try
changing

strCity = strCity & """" & Me.txtFilterCity.ItemData(varSelected)

to

strCity = strCity & """" & Me.txtFilterCity.ItemData(varSelected) & ""","

in order to build up a string like

"Ada","Canyon","Boise","O'Hare",

After the loop, trim off the trailing comma:

strCity = Left(strCity, Len(strCity) - 1)

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Back
Top