Multi-Select List

M

Melissa

Is it possible to manipulate this code to have more than one multi-select
list. I have a search criteria form created in 2003 Access. It works
perfectly until I try to add additional multi-select lists.

I get the message: "Compile Error: For control variable already in use" if I
try to add additional multi-select lists.

Here is the code I am using. I am not very familiar with code. I got this
code from an example database so as much explanation on the solution to this
problem would be very much appreciated.

Thanks,
Melissa

_______________________________________________________________________

Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me.txtFirstName = ""
Me.txtLastName = ""
Me.txtMaxAge = ""
Me.txtMinAge = ""
Me.cmbCompany = 0
Me.cmbCountry = 0

' De-select each item in Color List (multiselect list)
For intIndex = 0 To Me.lstFavColor.ListCount - 1
Me.lstFavColor.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " &
BuildFilter

' Requery the subform
Me.frmsubClients.Requery
End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors

' Check for LIKE First Name
If Me.txtFirstName > "" Then
varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*""
AND "
End If

' Check for LIKE Last Name
If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName & "*""
AND "
End If

' Check for min Age
If Me.txtMinAge > "" Then
varWhere = varWhere & "[Age] > " & Me.txtMinAge & " AND "
End If

' Check for max Age
If Me.txtMaxAge > "" Then
varWhere = varWhere & "[Age] < " & Me.txtMaxAge & " AND "
End If

' Check for CompanyID
If Me.cmbCompany > 0 Then
varWhere = varWhere & "[CompanyID] = " & Me.cmbCompany & " AND "
End If

' Check for CountryID
If Me.cmbCountry > 0 Then
varWhere = varWhere & "[CountryID] = " & Me.cmbCountry & " AND "
End If

' Check for Colors in multiselect list
For Each varItem In Me.lstFavColor.ItemsSelected
varColor = varColor & "[FavColor] = """ & _
Me.lstFavColor.ItemData(varItem) & """ OR "

Next

' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function
 
M

Melissa

I figured out how to add to it. Now the problem I am having is that I would
like this to run the filter as AND not OR. For example, I have a Region and
Employee filter. I want to see the results of this employee in a specific
region but right now the code gives me all the results of the employee I
select and all of the results from the region I select. Is it possible to
manipulate this code to filter with AND rather than OR?

Here is the code I am using:

__________________________________________________________________________
Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items. nothing in here yet until i finish customizing
the code


' De-select each item in the list (multiselect list. This is also in the
works.
For intIndex = 0 To Me.lstFilterShipTo.ListCount - 1
Me.lstFilterShipTo.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmEmailContacts.Form.RecordSource = "SELECT * FROM
qryDistLookupEmail " & BuildFilter

' Requery the subform
Me.frmEmailContacts.Requery

Me.FilterOn = False

End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors


' Check for Region
For Each varItem In Me.lstFilterRegion.ItemsSelected
varColor = varColor & "[Region] = """ & _
Me.lstFilterRegion.ItemData(varItem) & """ OR "

Next

' Check for CSR
For Each varItem In Me.lstFilterCSR.ItemsSelected
varColor = varColor & "[CSR] = """ & _
Me.lstFilterCSR.ItemData(varItem) & """ OR "


Next

' Check for Ship To
For Each varItem In Me.lstFilterShipTo.ItemsSelected
varColor = varColor & "[ShipNo] = """ & _
Me.lstFilterShipTo.ItemData(varItem) & """ OR "

Next


' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function



--
Melissa


Melissa said:
Is it possible to manipulate this code to have more than one multi-select
list. I have a search criteria form created in 2003 Access. It works
perfectly until I try to add additional multi-select lists.

I get the message: "Compile Error: For control variable already in use" if I
try to add additional multi-select lists.

Here is the code I am using. I am not very familiar with code. I got this
code from an example database so as much explanation on the solution to this
problem would be very much appreciated.

Thanks,
Melissa

_______________________________________________________________________

Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me.txtFirstName = ""
Me.txtLastName = ""
Me.txtMaxAge = ""
Me.txtMinAge = ""
Me.cmbCompany = 0
Me.cmbCountry = 0

' De-select each item in Color List (multiselect list)
For intIndex = 0 To Me.lstFavColor.ListCount - 1
Me.lstFavColor.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " &
BuildFilter

' Requery the subform
Me.frmsubClients.Requery
End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors

' Check for LIKE First Name
If Me.txtFirstName > "" Then
varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*""
AND "
End If

' Check for LIKE Last Name
If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName & "*""
AND "
End If

' Check for min Age
If Me.txtMinAge > "" Then
varWhere = varWhere & "[Age] > " & Me.txtMinAge & " AND "
End If

' Check for max Age
If Me.txtMaxAge > "" Then
varWhere = varWhere & "[Age] < " & Me.txtMaxAge & " AND "
End If

' Check for CompanyID
If Me.cmbCompany > 0 Then
varWhere = varWhere & "[CompanyID] = " & Me.cmbCompany & " AND "
End If

' Check for CountryID
If Me.cmbCountry > 0 Then
varWhere = varWhere & "[CountryID] = " & Me.cmbCountry & " AND "
End If

' Check for Colors in multiselect list
For Each varItem In Me.lstFavColor.ItemsSelected
varColor = varColor & "[FavColor] = """ & _
Me.lstFavColor.ItemData(varItem) & """ OR "

Next

' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function
Click to expand...
 
D

Douglas J. Steele

Change your function to something like:

Private Function BuildFilter() As String
Dim varColor As Variant
Dim varItem As Variant
Dim strWhere As String

strWhere = vbNullString ' Main filter
varColor = Null ' Subfilter used for colors

' Check for Region
For Each varItem In Me.lstFilterRegion.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterRegion.ItemData(varItem) & """, "
Next varItem
If IsNull(varColor) = False Then
strWhere = strWhere & "[Region] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check for CSR
varColor = Null
For Each varItem In Me.lstFilterCSR.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterCSR.ItemData(varItem) & """, "
Next varItem

If IsNull(varColor) = False Then
strWhere = strWhere & "[CSR] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check for Ship To
varColor = Null
For Each varItem In Me.lstFilterShipTo.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterShipTo.ItemData(varItem) & """, "
Next varItem

If IsNull(varColor) = False Then
strWhere = strWhere & "[ShipNo] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check if there is a filter to return...
If Len(strWhere) > 0 Then
strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 5)
End If

BuildFilter = strWhere

End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Melissa said:
I figured out how to add to it. Now the problem I am having is that I would
like this to run the filter as AND not OR. For example, I have a Region
and
Employee filter. I want to see the results of this employee in a specific
region but right now the code gives me all the results of the employee I
select and all of the results from the region I select. Is it possible to
manipulate this code to filter with AND rather than OR?

Here is the code I am using:

__________________________________________________________________________
Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items. nothing in here yet until i finish
customizing
the code


' De-select each item in the list (multiselect list. This is also in
the
works.
For intIndex = 0 To Me.lstFilterShipTo.ListCount - 1
Me.lstFilterShipTo.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmEmailContacts.Form.RecordSource = "SELECT * FROM
qryDistLookupEmail " & BuildFilter

' Requery the subform
Me.frmEmailContacts.Requery

Me.FilterOn = False

End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors


' Check for Region
For Each varItem In Me.lstFilterRegion.ItemsSelected
varColor = varColor & "[Region] = """ & _
Me.lstFilterRegion.ItemData(varItem) & """ OR "

Next

' Check for CSR
For Each varItem In Me.lstFilterCSR.ItemsSelected
varColor = varColor & "[CSR] = """ & _
Me.lstFilterCSR.ItemData(varItem) & """ OR "


Next

' Check for Ship To
For Each varItem In Me.lstFilterShipTo.ItemsSelected
varColor = varColor & "[ShipNo] = """ & _
Me.lstFilterShipTo.ItemData(varItem) & """ OR "

Next


' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function



--
Melissa


Melissa said:
Is it possible to manipulate this code to have more than one multi-select
list. I have a search criteria form created in 2003 Access. It works
perfectly until I try to add additional multi-select lists.

I get the message: "Compile Error: For control variable already in use"
if I
try to add additional multi-select lists.

Here is the code I am using. I am not very familiar with code. I got this
code from an example database so as much explanation on the solution to
this
problem would be very much appreciated.

Thanks,
Melissa

_______________________________________________________________________

Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me.txtFirstName = ""
Me.txtLastName = ""
Me.txtMaxAge = ""
Me.txtMinAge = ""
Me.cmbCompany = 0
Me.cmbCountry = 0

' De-select each item in Color List (multiselect list)
For intIndex = 0 To Me.lstFavColor.ListCount - 1
Me.lstFavColor.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " &
BuildFilter

' Requery the subform
Me.frmsubClients.Requery
End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors

' Check for LIKE First Name
If Me.txtFirstName > "" Then
varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName &
"*""
AND "
End If

' Check for LIKE Last Name
If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName &
"*""
AND "
End If

' Check for min Age
If Me.txtMinAge > "" Then
varWhere = varWhere & "[Age] > " & Me.txtMinAge & " AND "
End If

' Check for max Age
If Me.txtMaxAge > "" Then
varWhere = varWhere & "[Age] < " & Me.txtMaxAge & " AND "
End If

' Check for CompanyID
If Me.cmbCompany > 0 Then
varWhere = varWhere & "[CompanyID] = " & Me.cmbCompany & " AND "
End If

' Check for CountryID
If Me.cmbCountry > 0 Then
varWhere = varWhere & "[CountryID] = " & Me.cmbCountry & " AND "
End If

' Check for Colors in multiselect list
For Each varItem In Me.lstFavColor.ItemsSelected
varColor = varColor & "[FavColor] = """ & _
Me.lstFavColor.ItemData(varItem) & """ OR "

Next

' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function
Click to expand...
Click to expand...
 
M

Melissa

Hi Doug,

Thank you so much for this. It worked perfectly. Is there a limit to the
amount that can be added? If I add one more it works, but if I add anymore
than that it doesn't work.

Thanks,
--
Melissa


Douglas J. Steele said:
Change your function to something like:

Private Function BuildFilter() As String
Dim varColor As Variant
Dim varItem As Variant
Dim strWhere As String

strWhere = vbNullString ' Main filter
varColor = Null ' Subfilter used for colors

' Check for Region
For Each varItem In Me.lstFilterRegion.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterRegion.ItemData(varItem) & """, "
Next varItem
If IsNull(varColor) = False Then
strWhere = strWhere & "[Region] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check for CSR
varColor = Null
For Each varItem In Me.lstFilterCSR.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterCSR.ItemData(varItem) & """, "
Next varItem

If IsNull(varColor) = False Then
strWhere = strWhere & "[CSR] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check for Ship To
varColor = Null
For Each varItem In Me.lstFilterShipTo.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterShipTo.ItemData(varItem) & """, "
Next varItem

If IsNull(varColor) = False Then
strWhere = strWhere & "[ShipNo] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check if there is a filter to return...
If Len(strWhere) > 0 Then
strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 5)
End If

BuildFilter = strWhere

End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Melissa said:
I figured out how to add to it. Now the problem I am having is that I would
like this to run the filter as AND not OR. For example, I have a Region
and
Employee filter. I want to see the results of this employee in a specific
region but right now the code gives me all the results of the employee I
select and all of the results from the region I select. Is it possible to
manipulate this code to filter with AND rather than OR?

Here is the code I am using:

__________________________________________________________________________
Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items. nothing in here yet until i finish
customizing
the code


' De-select each item in the list (multiselect list. This is also in
the
works.
For intIndex = 0 To Me.lstFilterShipTo.ListCount - 1
Me.lstFilterShipTo.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmEmailContacts.Form.RecordSource = "SELECT * FROM
qryDistLookupEmail " & BuildFilter

' Requery the subform
Me.frmEmailContacts.Requery

Me.FilterOn = False

End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors


' Check for Region
For Each varItem In Me.lstFilterRegion.ItemsSelected
varColor = varColor & "[Region] = """ & _
Me.lstFilterRegion.ItemData(varItem) & """ OR "

Next

' Check for CSR
For Each varItem In Me.lstFilterCSR.ItemsSelected
varColor = varColor & "[CSR] = """ & _
Me.lstFilterCSR.ItemData(varItem) & """ OR "


Next

' Check for Ship To
For Each varItem In Me.lstFilterShipTo.ItemsSelected
varColor = varColor & "[ShipNo] = """ & _
Me.lstFilterShipTo.ItemData(varItem) & """ OR "

Next


' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function



--
Melissa


Melissa said:
Is it possible to manipulate this code to have more than one multi-select
list. I have a search criteria form created in 2003 Access. It works
perfectly until I try to add additional multi-select lists.

I get the message: "Compile Error: For control variable already in use"
if I
try to add additional multi-select lists.

Here is the code I am using. I am not very familiar with code. I got this
code from an example database so as much explanation on the solution to
this
problem would be very much appreciated.

Thanks,
Melissa

_______________________________________________________________________

Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me.txtFirstName = ""
Me.txtLastName = ""
Me.txtMaxAge = ""
Me.txtMinAge = ""
Me.cmbCompany = 0
Me.cmbCountry = 0

' De-select each item in Color List (multiselect list)
For intIndex = 0 To Me.lstFavColor.ListCount - 1
Me.lstFavColor.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " &
BuildFilter

' Requery the subform
Me.frmsubClients.Requery
End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors

' Check for LIKE First Name
If Me.txtFirstName > "" Then
varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName &
"*""
AND "
End If

' Check for LIKE Last Name
If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName &
"*""
AND "
End If

' Check for min Age
If Me.txtMinAge > "" Then
varWhere = varWhere & "[Age] > " & Me.txtMinAge & " AND "
End If

' Check for max Age
If Me.txtMaxAge > "" Then
varWhere = varWhere & "[Age] < " & Me.txtMaxAge & " AND "
End If

' Check for CompanyID
If Me.cmbCompany > 0 Then
varWhere = varWhere & "[CompanyID] = " & Me.cmbCompany & " AND "
End If

' Check for CountryID
If Me.cmbCountry > 0 Then
varWhere = varWhere & "[CountryID] = " & Me.cmbCountry & " AND "
End If

' Check for Colors in multiselect list
For Each varItem In Me.lstFavColor.ItemsSelected
varColor = varColor & "[FavColor] = """ & _
Me.lstFavColor.ItemData(varItem) & """ OR "

Next

' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If
Click to expand...
Click to expand...
Click to expand...
 
M

Melissa

I was able to add additional filters. There appears to be something wrong
with one of the filters I am trying to add. Not sure what it is yet, but when
I run it no error message comes up, it just returns no values. I will keep
researching it.
--
Melissa


Melissa said:
Hi Doug,

Thank you so much for this. It worked perfectly. Is there a limit to the
amount that can be added? If I add one more it works, but if I add anymore
than that it doesn't work.

Thanks,
--
Melissa


Douglas J. Steele said:
Change your function to something like:

Private Function BuildFilter() As String
Dim varColor As Variant
Dim varItem As Variant
Dim strWhere As String

strWhere = vbNullString ' Main filter
varColor = Null ' Subfilter used for colors

' Check for Region
For Each varItem In Me.lstFilterRegion.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterRegion.ItemData(varItem) & """, "
Next varItem
If IsNull(varColor) = False Then
strWhere = strWhere & "[Region] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check for CSR
varColor = Null
For Each varItem In Me.lstFilterCSR.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterCSR.ItemData(varItem) & """, "
Next varItem

If IsNull(varColor) = False Then
strWhere = strWhere & "[CSR] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check for Ship To
varColor = Null
For Each varItem In Me.lstFilterShipTo.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterShipTo.ItemData(varItem) & """, "
Next varItem

If IsNull(varColor) = False Then
strWhere = strWhere & "[ShipNo] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check if there is a filter to return...
If Len(strWhere) > 0 Then
strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 5)
End If

BuildFilter = strWhere

End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Melissa said:
I figured out how to add to it. Now the problem I am having is that I would
like this to run the filter as AND not OR. For example, I have a Region
and
Employee filter. I want to see the results of this employee in a specific
region but right now the code gives me all the results of the employee I
select and all of the results from the region I select. Is it possible to
manipulate this code to filter with AND rather than OR?

Here is the code I am using:

__________________________________________________________________________
Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items. nothing in here yet until i finish
customizing
the code


' De-select each item in the list (multiselect list. This is also in
the
works.
For intIndex = 0 To Me.lstFilterShipTo.ListCount - 1
Me.lstFilterShipTo.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmEmailContacts.Form.RecordSource = "SELECT * FROM
qryDistLookupEmail " & BuildFilter

' Requery the subform
Me.frmEmailContacts.Requery

Me.FilterOn = False

End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors


' Check for Region
For Each varItem In Me.lstFilterRegion.ItemsSelected
varColor = varColor & "[Region] = """ & _
Me.lstFilterRegion.ItemData(varItem) & """ OR "

Next

' Check for CSR
For Each varItem In Me.lstFilterCSR.ItemsSelected
varColor = varColor & "[CSR] = """ & _
Me.lstFilterCSR.ItemData(varItem) & """ OR "


Next

' Check for Ship To
For Each varItem In Me.lstFilterShipTo.ItemsSelected
varColor = varColor & "[ShipNo] = """ & _
Me.lstFilterShipTo.ItemData(varItem) & """ OR "

Next


' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function



--
Melissa


:

Is it possible to manipulate this code to have more than one multi-select
list. I have a search criteria form created in 2003 Access. It works
perfectly until I try to add additional multi-select lists.

I get the message: "Compile Error: For control variable already in use"
if I
try to add additional multi-select lists.

Here is the code I am using. I am not very familiar with code. I got this
code from an example database so as much explanation on the solution to
this
problem would be very much appreciated.

Thanks,
Melissa

_______________________________________________________________________

Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me.txtFirstName = ""
Me.txtLastName = ""
Me.txtMaxAge = ""
Me.txtMinAge = ""
Me.cmbCompany = 0
Me.cmbCountry = 0

' De-select each item in Color List (multiselect list)
For intIndex = 0 To Me.lstFavColor.ListCount - 1
Me.lstFavColor.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " &
BuildFilter

' Requery the subform
Me.frmsubClients.Requery
End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors

' Check for LIKE First Name
If Me.txtFirstName > "" Then
varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName &
"*""
AND "
End If

' Check for LIKE Last Name
If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName &
"*""
AND "
End If

' Check for min Age
If Me.txtMinAge > "" Then
varWhere = varWhere & "[Age] > " & Me.txtMinAge & " AND "
End If

' Check for max Age
If Me.txtMaxAge > "" Then
varWhere = varWhere & "[Age] < " & Me.txtMaxAge & " AND "
End If

' Check for CompanyID
If Me.cmbCompany > 0 Then
varWhere = varWhere & "[CompanyID] = " & Me.cmbCompany & " AND "
End If

' Check for CountryID
If Me.cmbCountry > 0 Then
varWhere = varWhere & "[CountryID] = " & Me.cmbCountry & " AND "
End If

' Check for Colors in multiselect list
For Each varItem In Me.lstFavColor.ItemsSelected
varColor = varColor & "[FavColor] = """ & _
Click to expand...
Click to expand...
Click to expand...
 
M

Melissa

I figured out the problem....there was no corresponding data for the filter I
was trying to run.. Now I feel like a moron. :)

Doug, thank you again for providing me with this code. It works great!!!!!

This next question might need a new post, but I also have several filters
that are setup as Yes/No checkboxes. I tried to use the code you provided for
these filters but it gives me an error "Run Time Error 2001 You canceled the
previous operation"
--
Melissa


Melissa said:
Hi Doug,

Thank you so much for this. It worked perfectly. Is there a limit to the
amount that can be added? If I add one more it works, but if I add anymore
than that it doesn't work.

Thanks,
--
Melissa


Douglas J. Steele said:
Change your function to something like:

Private Function BuildFilter() As String
Dim varColor As Variant
Dim varItem As Variant
Dim strWhere As String

strWhere = vbNullString ' Main filter
varColor = Null ' Subfilter used for colors

' Check for Region
For Each varItem In Me.lstFilterRegion.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterRegion.ItemData(varItem) & """, "
Next varItem
If IsNull(varColor) = False Then
strWhere = strWhere & "[Region] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check for CSR
varColor = Null
For Each varItem In Me.lstFilterCSR.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterCSR.ItemData(varItem) & """, "
Next varItem

If IsNull(varColor) = False Then
strWhere = strWhere & "[CSR] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check for Ship To
varColor = Null
For Each varItem In Me.lstFilterShipTo.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterShipTo.ItemData(varItem) & """, "
Next varItem

If IsNull(varColor) = False Then
strWhere = strWhere & "[ShipNo] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check if there is a filter to return...
If Len(strWhere) > 0 Then
strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 5)
End If

BuildFilter = strWhere

End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Melissa said:
I figured out how to add to it. Now the problem I am having is that I would
like this to run the filter as AND not OR. For example, I have a Region
and
Employee filter. I want to see the results of this employee in a specific
region but right now the code gives me all the results of the employee I
select and all of the results from the region I select. Is it possible to
manipulate this code to filter with AND rather than OR?

Here is the code I am using:

__________________________________________________________________________
Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items. nothing in here yet until i finish
customizing
the code


' De-select each item in the list (multiselect list. This is also in
the
works.
For intIndex = 0 To Me.lstFilterShipTo.ListCount - 1
Me.lstFilterShipTo.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmEmailContacts.Form.RecordSource = "SELECT * FROM
qryDistLookupEmail " & BuildFilter

' Requery the subform
Me.frmEmailContacts.Requery

Me.FilterOn = False

End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors


' Check for Region
For Each varItem In Me.lstFilterRegion.ItemsSelected
varColor = varColor & "[Region] = """ & _
Me.lstFilterRegion.ItemData(varItem) & """ OR "

Next

' Check for CSR
For Each varItem In Me.lstFilterCSR.ItemsSelected
varColor = varColor & "[CSR] = """ & _
Me.lstFilterCSR.ItemData(varItem) & """ OR "


Next

' Check for Ship To
For Each varItem In Me.lstFilterShipTo.ItemsSelected
varColor = varColor & "[ShipNo] = """ & _
Me.lstFilterShipTo.ItemData(varItem) & """ OR "

Next


' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function



--
Melissa


:

Is it possible to manipulate this code to have more than one multi-select
list. I have a search criteria form created in 2003 Access. It works
perfectly until I try to add additional multi-select lists.

I get the message: "Compile Error: For control variable already in use"
if I
try to add additional multi-select lists.

Here is the code I am using. I am not very familiar with code. I got this
code from an example database so as much explanation on the solution to
this
problem would be very much appreciated.

Thanks,
Melissa

_______________________________________________________________________

Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me.txtFirstName = ""
Me.txtLastName = ""
Me.txtMaxAge = ""
Me.txtMinAge = ""
Me.cmbCompany = 0
Me.cmbCountry = 0

' De-select each item in Color List (multiselect list)
For intIndex = 0 To Me.lstFavColor.ListCount - 1
Me.lstFavColor.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " &
BuildFilter

' Requery the subform
Me.frmsubClients.Requery
End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors

' Check for LIKE First Name
If Me.txtFirstName > "" Then
varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName &
"*""
AND "
End If

' Check for LIKE Last Name
If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName &
"*""
AND "
End If

' Check for min Age
If Me.txtMinAge > "" Then
varWhere = varWhere & "[Age] > " & Me.txtMinAge & " AND "
End If

' Check for max Age
If Me.txtMaxAge > "" Then
varWhere = varWhere & "[Age] < " & Me.txtMaxAge & " AND "
End If

' Check for CompanyID
If Me.cmbCompany > 0 Then
varWhere = varWhere & "[CompanyID] = " & Me.cmbCompany & " AND "
End If

' Check for CountryID
If Me.cmbCountry > 0 Then
varWhere = varWhere & "[CountryID] = " & Me.cmbCountry & " AND "
End If

' Check for Colors in multiselect list
For Each varItem In Me.lstFavColor.ItemsSelected
varColor = varColor & "[FavColor] = """ & _
Click to expand...
Click to expand...
Click to expand...
 
M

Melissa

I found somewhat of a solution, but there are some kinks...

I created a combo box with the row source pointing to the table where these
values reside.

SELECT [CS Beer Order Contacts - Email].[Distributor Communications] FROM
[CS Beer Order Contacts - Email] GROUP BY [CS Beer Order Contacts -
Email].[Distributor Communications];

I used this code to run the filter:


If Me.lstFilterDistComm = -1 Then
strWhere = strWhere & "([Distributor Communications] = True) AND "
ElseIf Me.lstFilterDistComm = 0 Then
strWhere = strWhere & "([Distributor Communications] = False) AND "
End If

Two problems I am running into.

1. When I am in the form the drop down box choices are "yes" or "no" but
when I make a selection it changes to "0" or "-1". I know this is because
it's a True/False box but how can I have it show Yes or No rather than the
numbers?

2. The other problem I have is I need it to default to All values. So, if I
don't want this to be a filter it just returns all values.

Thanks,
--
Melissa


Melissa said:
I figured out the problem....there was no corresponding data for the filter I
was trying to run.. Now I feel like a moron. :)

Doug, thank you again for providing me with this code. It works great!!!!!

This next question might need a new post, but I also have several filters
that are setup as Yes/No checkboxes. I tried to use the code you provided for
these filters but it gives me an error "Run Time Error 2001 You canceled the
previous operation"
--
Melissa


Melissa said:
Hi Doug,

Thank you so much for this. It worked perfectly. Is there a limit to the
amount that can be added? If I add one more it works, but if I add anymore
than that it doesn't work.

Thanks,
--
Melissa


Douglas J. Steele said:
Change your function to something like:

Private Function BuildFilter() As String
Dim varColor As Variant
Dim varItem As Variant
Dim strWhere As String

strWhere = vbNullString ' Main filter
varColor = Null ' Subfilter used for colors

' Check for Region
For Each varItem In Me.lstFilterRegion.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterRegion.ItemData(varItem) & """, "
Next varItem
If IsNull(varColor) = False Then
strWhere = strWhere & "[Region] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check for CSR
varColor = Null
For Each varItem In Me.lstFilterCSR.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterCSR.ItemData(varItem) & """, "
Next varItem

If IsNull(varColor) = False Then
strWhere = strWhere & "[CSR] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check for Ship To
varColor = Null
For Each varItem In Me.lstFilterShipTo.ItemsSelected
varColor = varColor & """" & _
Me.lstFilterShipTo.ItemData(varItem) & """, "
Next varItem

If IsNull(varColor) = False Then
strWhere = strWhere & "[ShipNo] IN (" & _
Left(varColor, Len(varColor) - 2) & ") AND "
End If

' Check if there is a filter to return...
If Len(strWhere) > 0 Then
strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 5)
End If

BuildFilter = strWhere

End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I figured out how to add to it. Now the problem I am having is that I would
like this to run the filter as AND not OR. For example, I have a Region
and
Employee filter. I want to see the results of this employee in a specific
region but right now the code gives me all the results of the employee I
select and all of the results from the region I select. Is it possible to
manipulate this code to filter with AND rather than OR?

Here is the code I am using:

__________________________________________________________________________
Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items. nothing in here yet until i finish
customizing
the code


' De-select each item in the list (multiselect list. This is also in
the
works.
For intIndex = 0 To Me.lstFilterShipTo.ListCount - 1
Me.lstFilterShipTo.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmEmailContacts.Form.RecordSource = "SELECT * FROM
qryDistLookupEmail " & BuildFilter

' Requery the subform
Me.frmEmailContacts.Requery

Me.FilterOn = False

End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors


' Check for Region
For Each varItem In Me.lstFilterRegion.ItemsSelected
varColor = varColor & "[Region] = """ & _
Me.lstFilterRegion.ItemData(varItem) & """ OR "

Next

' Check for CSR
For Each varItem In Me.lstFilterCSR.ItemsSelected
varColor = varColor & "[CSR] = """ & _
Me.lstFilterCSR.ItemData(varItem) & """ OR "


Next

' Check for Ship To
For Each varItem In Me.lstFilterShipTo.ItemsSelected
varColor = varColor & "[ShipNo] = """ & _
Me.lstFilterShipTo.ItemData(varItem) & """ OR "

Next


' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function



--
Melissa


:

Is it possible to manipulate this code to have more than one multi-select
list. I have a search criteria form created in 2003 Access. It works
perfectly until I try to add additional multi-select lists.

I get the message: "Compile Error: For control variable already in use"
if I
try to add additional multi-select lists.

Here is the code I am using. I am not very familiar with code. I got this
code from an example database so as much explanation on the solution to
this
problem would be very much appreciated.

Thanks,
Melissa

_______________________________________________________________________

Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me.txtFirstName = ""
Me.txtLastName = ""
Me.txtMaxAge = ""
Me.txtMinAge = ""
Me.cmbCompany = 0
Me.cmbCountry = 0

' De-select each item in Color List (multiselect list)
For intIndex = 0 To Me.lstFavColor.ListCount - 1
Me.lstFavColor.Selected(intIndex) = False
Next

End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " &
BuildFilter

' Requery the subform
Me.frmsubClients.Requery
End Sub


Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors

' Check for LIKE First Name
If Me.txtFirstName > "" Then
varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName &
"*""
AND "
End If

' Check for LIKE Last Name
If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName &
"*""
AND "
End If

' Check for min Age
If Me.txtMinAge > "" Then
varWhere = varWhere & "[Age] > " & Me.txtMinAge & " AND "
End If

' Check for max Age
If Me.txtMaxAge > "" Then
varWhere = varWhere & "[Age] < " & Me.txtMaxAge & " AND "
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Melissa said:
I found somewhat of a solution, but there are some kinks...

I created a combo box with the row source pointing to the table where
these
values reside.

SELECT [CS Beer Order Contacts - Email].[Distributor Communications] FROM
[CS Beer Order Contacts - Email] GROUP BY [CS Beer Order Contacts -
Email].[Distributor Communications];
Click to expand...

Mmmm. Beer.... said:
I used this code to run the filter:


If Me.lstFilterDistComm = -1 Then
strWhere = strWhere & "([Distributor Communications] = True) AND "
ElseIf Me.lstFilterDistComm = 0 Then
strWhere = strWhere & "([Distributor Communications] = False) AND "
End If

Two problems I am running into.

1. When I am in the form the drop down box choices are "yes" or "no" but
when I make a selection it changes to "0" or "-1". I know this is because
it's a True/False box but how can I have it show Yes or No rather than the
numbers?
Click to expand...

What's the RowSource for the combo box?
2. The other problem I have is I need it to default to All values. So, if
I
don't want this to be a filter it just returns all values.
Click to expand...

You'd need to provide a third choice "All" and handle it properly in your
code.

I should be able to provide a more specific answer once I know how you're
populating the combo box.
 
M

Melissa

Hi Doug,

I think this is what you are looking for... This is the row source.

SELECT [CS Beer Order Contacts - Email_new].[Distributor Communications]
FROM [CS Beer Order Contacts - Email_new] GROUP BY [CS Beer Order Contacts -
Email_new].[Distributor Communications];


--
Melissa


Douglas J. Steele said:
Melissa said:
I found somewhat of a solution, but there are some kinks...

I created a combo box with the row source pointing to the table where
these
values reside.

SELECT [CS Beer Order Contacts - Email].[Distributor Communications] FROM
[CS Beer Order Contacts - Email] GROUP BY [CS Beer Order Contacts -
Email].[Distributor Communications];
Click to expand...

Mmmm. Beer.... said:
I used this code to run the filter:


If Me.lstFilterDistComm = -1 Then
strWhere = strWhere & "([Distributor Communications] = True) AND "
ElseIf Me.lstFilterDistComm = 0 Then
strWhere = strWhere & "([Distributor Communications] = False) AND "
End If

Two problems I am running into.

1. When I am in the form the drop down box choices are "yes" or "no" but
when I make a selection it changes to "0" or "-1". I know this is because
it's a True/False box but how can I have it show Yes or No rather than the
numbers?
Click to expand...

What's the RowSource for the combo box?
2. The other problem I have is I need it to default to All values. So, if
I
don't want this to be a filter it just returns all values.
Click to expand...

You'd need to provide a third choice "All" and handle it properly in your
code.

I should be able to provide a more specific answer once I know how you're
populating the combo box.
Click to expand...
 
D

Douglas J. Steele

In a way, that seems overkill, since you're really only offering two choices
(True or False), mean that setting the RowSourceType to Value List and the
RowSource to True;False would be much simpler. In fact, make the RowSource
equal to True;False;All, and change your code to

Select Case Me.lstFilterDistComm
Case True
strWhere = strWhere & "([Distributor Communications] = True) AND "
Case False
strWhere = strWhere & "([Distributor Communications] = False) AND "
End Select

That means that when they select All from the combo box, you'll get the
desired all values.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Melissa said:
Hi Doug,

I think this is what you are looking for... This is the row source.

SELECT [CS Beer Order Contacts - Email_new].[Distributor Communications]
FROM [CS Beer Order Contacts - Email_new] GROUP BY [CS Beer Order
Contacts -
Email_new].[Distributor Communications];


--
Melissa


Douglas J. Steele said:
Melissa said:
I found somewhat of a solution, but there are some kinks...

I created a combo box with the row source pointing to the table where
these
values reside.

SELECT [CS Beer Order Contacts - Email].[Distributor Communications]
FROM
[CS Beer Order Contacts - Email] GROUP BY [CS Beer Order Contacts -
Email].[Distributor Communications];
Click to expand...

Mmmm. Beer.... said:
I used this code to run the filter:


If Me.lstFilterDistComm = -1 Then
strWhere = strWhere & "([Distributor Communications] = True) AND
"
ElseIf Me.lstFilterDistComm = 0 Then
strWhere = strWhere & "([Distributor Communications] = False)
AND "
End If

Two problems I am running into.

1. When I am in the form the drop down box choices are "yes" or "no"
but
when I make a selection it changes to "0" or "-1". I know this is
because
it's a True/False box but how can I have it show Yes or No rather than
the
numbers?
Click to expand...

What's the RowSource for the combo box?
2. The other problem I have is I need it to default to All values. So,
if
I
don't want this to be a filter it just returns all values.
Click to expand...

You'd need to provide a third choice "All" and handle it properly in your
code.

I should be able to provide a more specific answer once I know how you're
populating the combo box.
Click to expand...
Click to expand...
 
M

Melissa

Doug...you are amazing!!! I have been working on this project for such a long
time and without having a background in writing code it has been a bit
challenging. You have given me exactly what I needed and your explanations
were clear and easy to follow. THANK YOU SO MUCH!!!
--
Melissa


Douglas J. Steele said:
In a way, that seems overkill, since you're really only offering two choices
(True or False), mean that setting the RowSourceType to Value List and the
RowSource to True;False would be much simpler. In fact, make the RowSource
equal to True;False;All, and change your code to

Select Case Me.lstFilterDistComm
Case True
strWhere = strWhere & "([Distributor Communications] = True) AND "
Case False
strWhere = strWhere & "([Distributor Communications] = False) AND "
End Select

That means that when they select All from the combo box, you'll get the
desired all values.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Melissa said:
Hi Doug,

I think this is what you are looking for... This is the row source.

SELECT [CS Beer Order Contacts - Email_new].[Distributor Communications]
FROM [CS Beer Order Contacts - Email_new] GROUP BY [CS Beer Order
Contacts -
Email_new].[Distributor Communications];


--
Melissa


Douglas J. Steele said:
I found somewhat of a solution, but there are some kinks...

I created a combo box with the row source pointing to the table where
these
values reside.

SELECT [CS Beer Order Contacts - Email].[Distributor Communications]
FROM
[CS Beer Order Contacts - Email] GROUP BY [CS Beer Order Contacts -
Email].[Distributor Communications];

Mmmm. Beer.... <g>

I used this code to run the filter:


If Me.lstFilterDistComm = -1 Then
strWhere = strWhere & "([Distributor Communications] = True) AND
"
ElseIf Me.lstFilterDistComm = 0 Then
strWhere = strWhere & "([Distributor Communications] = False)
AND "
End If

Two problems I am running into.

1. When I am in the form the drop down box choices are "yes" or "no"
but
when I make a selection it changes to "0" or "-1". I know this is
because
it's a True/False box but how can I have it show Yes or No rather than
the
numbers?

What's the RowSource for the combo box?

2. The other problem I have is I need it to default to All values. So,
if
I
don't want this to be a filter it just returns all values.

You'd need to provide a third choice "All" and handle it properly in your
code.

I should be able to provide a more specific answer once I know how you're
populating the combo box.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

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

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

Ask a Question

Similar Threads

Run-Time 2580 error 0
multi-search criteria form 3
Report Options 4
Help With Search Form 1
searching and finding records from separate tables 1
Help with WHERE filter on opening report from dialog 4
Help Coding with Search Form 5
Update unbound TextBoxes on Form; Need to Read AND Write 1

Top