Easy enough to do.
First, take any criteria out of your form's record source query so all rows
will be presented.
Now here is some sample code that will cause it to filter like you want it.
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
******************
The AddAnd code that puts the And in the fitler string:
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
***********
Now, to get the (All) in your combo box.
This example uses only one column when you are using a text value to do the
lookup:
SELECT "(All)" As Dummy FROM dbo_Initiative UNION SELECT DISTINCT
InitPriority FROM dbo_Initiative WHERE InitPriority IS NOT NULL;
This example is where you are using a numeric key for the lookup, but it is
hidden and you show a text description to present to the user:
SELECT 0 As ID, "(All)" As Dummy FROM dbo_Initiative UNION SELECT StatusID,
StatusDescr FROM dbo_ProjectStatus;
Then for each combo, put the following directly in the After Update property
in the properties dialog:
=SetFilters()
Now, I also have a command button called Clear Filters that remove the
filtering.
Private Sub cmdClearFilters_Click()
With Me
.cboPriority = "(All)"
.cboOrigDate = "(All)"
.cboCurrDate = "(All)"
.cboInitStatus = 0
.cboInitType = 0
.cboCenter = DLookup("[DefaultCenter]", "tblClientVersion")
.txtDescrSearch = Null
.subInitiative.Form.FilterOn = False
.subInitiative.Form.Requery
End With
End Sub
And last, in the Form Activate event
Call cmdClearFilters_Click
That is so the (All) values will show and no filtering will be done when the
form opens.
--
Dave Hargis, Microsoft Access MVP
Jon M. said:
Okay I have a form, based on one table. My form has two combo boxes on it,
one searches for an employee by building, the other by department. The
results are displayed in a subform beneath the combo boxes. This seems to
work fine. However until you enter a selection into each combo box no
records are displayed. How can I set each combo box to work independently
and/or together to get the desired results? Is there a way I could add
something like an "all" field to both? I would also like the subform to
display all records when the form is open until a selection is made. As
always any help is greatly appreciated!
