Re: Filtering records on a form using multiple combo boxes

So, then, you will need to build the Where string from that information,
then build the SQL string with the Where clause in it, and assign the string
to the RowSource of the list box.

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

"BIGRED56" <BIGRED56@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:193660DE-8206-40DA-9839-97B381E183F1@xxxxxxxxxxxxxxxx
Hi allen, this is what i am looking to do...
I am doing a final display page for my database. I t will be used for the
purpose of looking up already entered records.

What I have is 3 unbounded combo boxes that all independently filter a
list
box to display certain records..
- these 3 combo boxes have command buttons to activate the filtering
I then have 2 text boxes(txtbegin and txtend), which uses its own query to
filter the field (Date Located ) in the record list box..

What I would like to do is have the Date box dependent on the other 3
combo
boxes. So you can have a date range and another combo filter, filtering
the
final List display


"Allen Browne" wrote:

List boxes don't have a Filter property, so you will need to assign their
RowSource.

The RowSource is typically a SQL statement.
The filter string becomes the WHERE clause that you patch into the middle
of
the string, after the FROM clause, and before the ORDER BY clause.

This kind of thing:
Dim strSql As String
strSql = "SELECT * FROM Table1 WHERE " & strWhere & " ORDER BY ID;"
Me.List1.RowSource = strSql

"BIGRED56" <BIGRED56@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7440B85D-09EF-45EE-84D8-180F5309FE2F@xxxxxxxxxxxxxxxx
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


.