alternating cascading combo boxes for filter

  • Thread starter Thread starter C Hayes
  • Start date Start date
C

C Hayes

I have four major fields that my db is filtered by regularly

I have four combo boxes with grouped options.

address lastname firstname company

I need to filter each by each as they are used.

i.e.

address lastname firstname company
123 Any St Mike

where lastname and company only contain instances of address and firstname

i.e.
address lastname firstname company
Jones mike

where address and company only contain instances of lastname and firstname

one more instance for example
address lastname firstname company
Smith Larry ABC Company

where address only contains instances of lastname, firstname and company.

I currently filter the form by each of these combo boxes, how can I filter
the form by the cascading combo boxes together?
 
Here is an example where I do exactly that:

Private Function SetFilters()
Dim strFilter As String 'Used by Form Filtering procedures

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _
.cboOrigDate & """"
End If

If .cboCurrDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CurrentReleaseTarget] = " & .cboCurrDate
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

If Not IsNull(.txtDescrSearch) Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitShortDescr] Like ""*" &
Me.txtDescrSearch & "*"""
End If

.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True

End With 'Me

End Function

**********
The AddAnd function referenced above:

Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If


AddAnd_Exit:

Exit Function
On Error GoTo 0

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmStartForm"
GoTo AddAnd_Exit

End Function
 
thanks! This is great. I'll have to examine the code and make it fit.

I'll work through this and let you know how it goes.

Klatuu said:
Here is an example where I do exactly that:

Private Function SetFilters()
Dim strFilter As String 'Used by Form Filtering procedures

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _
.cboOrigDate & """"
End If

If .cboCurrDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CurrentReleaseTarget] = " & .cboCurrDate
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

If Not IsNull(.txtDescrSearch) Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitShortDescr] Like ""*" &
Me.txtDescrSearch & "*"""
End If

.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True

End With 'Me

End Function

**********
The AddAnd function referenced above:

Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If


AddAnd_Exit:

Exit Function
On Error GoTo 0

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmStartForm"
GoTo AddAnd_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


C Hayes said:
I have four major fields that my db is filtered by regularly

I have four combo boxes with grouped options.

address lastname firstname company

I need to filter each by each as they are used.

i.e.

address lastname firstname company
123 Any St Mike

where lastname and company only contain instances of address and firstname

i.e.
address lastname firstname company
Jones mike

where address and company only contain instances of lastname and firstname

one more instance for example
address lastname firstname company
Smith Larry ABC Company

where address only contains instances of lastname, firstname and company.

I currently filter the form by each of these combo boxes, how can I filter
the form by the cascading combo boxes together?
Click to expand...
Click to expand...
 
Please post back if you have any questions. Maybe I should point out a
couple of things. Here are tw different sets:

This entry's combo has only one column with the actual values to match. It
has a row source of a union query That presents (All) as the first row. It
means include all rows for this field. For example:
SELECT "(All)" As Dummy FROM dbo_Initiative UNION SELECT DISTINCT
Format(OrigReleaseTarget,"yyyy-mm") FROM dbo_Initiative WHERE
OrigReleaseTarget IS NOT NULL;

So this code will not be included in the filtering; otherwise, it will check
to see if any previous choices were made that put a value in strFilter. If
so, it will add the word AND to the filter string.

If .cboCurrDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CurrentReleaseTarget] = " & .cboCurrDate
End If

This works exactly the same way, except it is using a foreign key to get the
value, so the first column of the combo is hidden so the user sees only the
text descripton, but the value returned by the bound column of the combo is
the value to be used in the filter. Note that when the user sees (All), the
value in the bound column will be 0. If it is 0 then don't include this
combo in the filter:

SELECT 0 As ID, "(All)" As Dummy FROM dbo_Initiative UNION SELECT StatusID,
StatusDescr FROM dbo_ProjectStatus;
If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

--
Dave Hargis, Microsoft Access MVP


C Hayes said:
thanks! This is great. I'll have to examine the code and make it fit.

I'll work through this and let you know how it goes.

Klatuu said:
Here is an example where I do exactly that:

Private Function SetFilters()
Dim strFilter As String 'Used by Form Filtering procedures

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _
.cboOrigDate & """"
End If

If .cboCurrDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CurrentReleaseTarget] = " & .cboCurrDate
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

If Not IsNull(.txtDescrSearch) Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitShortDescr] Like ""*" &
Me.txtDescrSearch & "*"""
End If

.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True

End With 'Me

End Function

**********
The AddAnd function referenced above:

Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If


AddAnd_Exit:

Exit Function
On Error GoTo 0

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmStartForm"
GoTo AddAnd_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


C Hayes said:
I have four major fields that my db is filtered by regularly

I have four combo boxes with grouped options.

address lastname firstname company

I need to filter each by each as they are used.

i.e.

address lastname firstname company
123 Any St Mike

where lastname and company only contain instances of address and firstname

i.e.
address lastname firstname company
Jones mike

where address and company only contain instances of lastname and firstname

one more instance for example
address lastname firstname company
Smith Larry ABC Company

where address only contains instances of lastname, firstname and company.

I currently filter the form by each of these combo boxes, how can I filter
the form by the cascading combo boxes together?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top