Re: Filtering records on a form using multiple combo boxes

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

Hi ALLEN,
I have a question about this coding I have 3 combo boxes and alist which
filter another list that displays records

If I wanted to write this code How would I do it to filter the record
display list box rather then the form...

Bigred

"Allen Browne" wrote:

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