Re: Filtering records on a form using multiple combo boxes

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

The example below shows how to build up a filter string from the non-blank
boxes. You can then use the same filter string to output a report of the
same records.

The example assumes the CompanyID is a Number type field.
If it is a Text type field, you need extra quotes:
strWhere = strWhere & "([CompanyID] = """ & _
Me.cboFindCompanyID & """) AND "

With the date range,
if you supply you get:
Only a start date all records from that date on
Only an end date all records up to that date
Both all records between the dates
Neither all records.

The example is crafted so that it is dead easy to add more search boxes if
needed. Each one tacks an " AND " on the end ready for the next one, and the
trailing " AND " is chopped off at the end. If no criteria were found, the
code returns all records.

Here is the code to filter the form:
--------------filter code starts------------------
Dim strWhere As String
Dim lngLen As Long
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first.

If Not IsNull(Me.cboFindCompanyID) Then
strWhere = strWhere & "([CompanyID] = " & _
Me.cboFindCompanyID & ") AND "
End If

If Not IsNull(Me.cboFindAgentID) Then
strWhere = strWhere & "([AgentID] = " & _
Me.cboFindAgentID & ") AND "
End If

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = "([LeadDate] < " & _
Format(Me.txtEndDate, conDateFormat) & ") AND "
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = "([LeadDate] > " & _
Format(Me.txtStartDate, conDateFormat) & ") AND "
Else 'Both start and end dates.
strWhere = "([LeadDate] Between " & _
Format(Me.txtStartDate, conDateFormat) & " And " & _
Format(Me.txtEndDate, conDateFormat) & ") AND "
End If
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
Endif
--------------filter code ends------------------

Now, if you wanted to print the same records, your command button's Click
event procedure would be:

Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kevin Kraemer" <KevinKraemer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F1B19F6B-1D21-45B8-AED3-49DA5E5A35A8@xxxxxxxxxxxxxxxx
> Welcome Weekend Warriors!
>
> I am pretty good with Access, not so such with VB code.
>
> I have a Marketing / Sales Lead table which includes fields like
> LeadDate (Date the lead comes in)
>
> MarketingCampaignID (foreign key from the Marketing Campaign table)
> CompanyID (foreign key from the Company table)
> AgentID (foreign key from the Agent table)
>
> Finally a PRINT checkbox.
>
> I have a form which brings in all records and fields from the Sales Lead
> table. From there I want to be able to filter the records on the form
> based
> on a date range as well as selections in the combo boxes (all located in
> the
> header) of a continuous form.
>
> The date fields are text boxes (StartDate and EndDate) - records whose
> LeadDate is between those 2 date text boxes I would like to filter and
> show
> only those filtered records on the form.
>
> Then I have 3 unbound combo boxes with the row source being the main table
> of the combo box. For the Agent Combo Box (AgentCB), the row source is
> the
> Agent table, with the bound field being 1 (which is the primary key called
> AgentID). Once an agent is selected (let's say Agent 1), I would like to
> filter the current records in the form based on the agent ID (while
> continuing to filter on the date text boxes).
>
> The other 3 combo boxes - one for the company and one for the marketing
> campaigns are set up the same way.
>
> After a person selects an agent, they may also want to select a marketing
> campaign (let's say Magazine Ad) - I would like the form to display
> records
> for the date range for Agent 1 for the Magazine ad.
>
> Hopefully that is descriptive enough to understand.
>
> Finally I have them click on a PRINT checkbox, since they may only want to
> include some of the records. What I also have is a Print All button so
> all
> the check boxes get checked. I had run an update query to do this in the
> past, but I don't know how to do that with the multiple filters.
>
> Thanks ahead of time for your help


.

Relevant Pages