Ed:
Rather than cluttering up the bound form with unbound controls you might
prefer to use a separate unbound dialogue form, frmFilterDlg say, which can
be opened from a button on the main bound form. The code for the button
would pass the name of the bound form to the dialogue form like so:
DoCmd.OpenForm "frmFilterDlg", _
WindowMode:=acDialog, _
OpenArgs:=Me.Name
On the dialogue form you'd have text boxes txtStartDate and txtEndDate for
the dates again, but also controls for the other fields on which you might
want to filter the bound form. Some can be combo boxes, cboCompany, cboRig,
cboCompanyRep, while that for the memo field would be a text box, txtNotes
say.
The combo boxes would reproduce those on the bound form exactly, but without
having any value for their ControlSource properties, so you can just copy and
paste them from the main form and then delete their ControlSources. This
would mean you'd be able to select from them rather than typing in the names.
The memo field could be set up so that you can enter any substring and
return records where that substring is included in the memo field.
I'm assuming that the company, rig and company reps are recorded in the
telephone log table by numeric foreign keys which reference the primary keys
of the respective referenced tables (even though you see the names in the
combo boxes their values are the hidden numbers); I'll call these fields
CompanyID, RigID and CompanyRepID for this example. I'll call the memo field
Notes.
In the dialogue form you'd probably want two buttons , one to implement the
filter and one to cancel the operation and return to the bound form without
changing its filter. For the Cancel button simply close the form with:
DoCmd.Close acForm, Me.Name
For the button to implement the filter you'd need to examine each of the
unbound controls to see if they are Null or not and build a string expression
for the filter, then apply this to the main bound form and close the dialogue
form, so the code would go like this:
Dim frm As Form
Dim strFilter As String
Set frm = Forms(Me.OpenArgs)
' examine each control and build string for filter
If Not IsNull(Me.txtStartDate) Then
strFilter = " And " & _
YourDateTimeField] >= #" & _
Format(Me.txtStartDate,"mm/dd/yyyy") & _
"# And [YourDateTimeField] < #" & _
Format(Me.txtEndDate,"mm/dd/yyyy") & _
"# +1"
End If
If Not IsNull(Me.cboComany) Then
strFilter = strFilter & " And " & _
"CompanyID = " & Me.cboCompany
End If
If Not IsNull(Me.cboRig) Then
strFilter = strFilter & " And " & _
"RigID = " & Me.cboRig
End If
If Not IsNull(Me.cboComanyRep) Then
strFilter = strFilter & " And " & _
"CompanyRepID = " & Me.cboCompanyRep
End If
If Not IsNull(Me.txtNotes) Then
strFilter = strFilter & " And " & _
"Notes Like ""*" & Me.txtNotes & "*"""
End If
' remove redundant leading " And "
strFilter = Mid$(strFilter,6)
' set filter property of bound form
frm.Filter = strFilter
frm.FilterOn = True
' close dialogue form
DoCmd.Close acForm, Me.Name
You'll see from the above that it would be a relatively simple task to add
other controls to the dialogue form for other fields if you wished; you'd
just need to include another If….End If construct in the code for each extra
control.
If the foreign keys are 'natural' keys, i.e. the actual text names of the
company, rig and rep rather than numeric IDs you'd need to wrap the values in
quotes, e.g.
"CompanyID = """ & Me.cboCompany & """"
Don't forget that if you include spaces or other special characters in any
control or field names you need to enclose the name in brackets [like so] in
the code.
Ken Sheridan
Stafford, England