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!
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.

Signature
Dave Hargis, Microsoft Access MVP
> 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
[quoted text clipped - 5 lines]
> display all records when the form is open until a selection is made. As
> always any help is greatly appreciated!
Jon M. - 29 Feb 2008 20:07 GMT
You sir are good, thank you very much!

Signature
Jon M.
> Easy enough to do.
> First, take any criteria out of your form's record source query so all rows
[quoted text clipped - 122 lines]
> > display all records when the form is open until a selection is made. As
> > always any help is greatly appreciated!
Klatuu - 29 Feb 2008 20:10 GMT
Thanks, Jon.
If you have any problems getting to work for you, please post back.

Signature
Dave Hargis, Microsoft Access MVP
> You sir are good, thank you very much!
>
[quoted text clipped - 124 lines]
> > > display all records when the form is open until a selection is made. As
> > > always any help is greatly appreciated!
Jon M. - 29 Feb 2008 21:29 GMT
I do have a question on this, I am up to adding my (All) to my two combo
boxes, I am using a text value in both boxes so I am using your fisrt
example. Is the example code to be entered? Or is it to be entered in the
properties of each box? If it is code what should I use as my begining?
Private Sub? Private Function? As you can tell I'm a bit of a novice with
the coding.

Signature
Jon M.
> Thanks, Jon.
> If you have any problems getting to work for you, please post back.
[quoted text clipped - 127 lines]
> > > > display all records when the form is open until a selection is made. As
> > > > always any help is greatly appreciated!
Klatuu - 29 Feb 2008 21:31 GMT
The code you are talking about is a Union Query. It goes in the row source
property of the combo box.

Signature
Dave Hargis, Microsoft Access MVP
> I do have a question on this, I am up to adding my (All) to my two combo
> boxes, I am using a text value in both boxes so I am using your fisrt
[quoted text clipped - 134 lines]
> > > > > display all records when the form is open until a selection is made. As
> > > > > always any help is greatly appreciated!