Multipule Filter on a Form

[Kat]

I have a form that I would like to use a Combo box to filter off of. I have
never used the filter option in the form before. How do I structure the
filter to look at the (2) combo boxes to pull the certain record(s)?

CmboSalesRep

CmboOrderNumbr

Form: LoanerSearch

So in the LoanerSeach form there are (2) fields called: SalesRep and
OrderNumber. I want both the fields to match what is in the combo box.
Also, within the form LoanerSearch there are subforms. How does that affect
the filter? Do I need to put the filter in them too?

 

[Arvin Meyer [MVP]]

Here's an example using 2 list boxes which works exactly the same as what
you require:

http://www.accessmvp.com/Arvin/Combo.zip

 

[Ken Sheridan]

Firstly the combo boxes should be unbound, not bound to the SalesRep and
OrderNumber fields. You can have separate controls bound to them.

You can either filter the form progressively as you select an item from each
combo box or you can have a separate button to do it after you've made the
selections. Either way the code is the same; for the first it would go in
the AfterUpdate event procedures of both combo boxes; for the second in the
Click event procedure of the button. The code would go like this:

Dim strFilter As String

' filter on sales rep if one selected
If Not IsNull(Me.CmboSalesRep) Then
strFilter = "SalesRep = """ & Me.CmboSalesRep & """ And "
' if no order number selected remove ' And ' from filter expression
If IsNull(Me.CmboOrderNumber) Then
strFilter = Left(strFilter, Len(strFilter) - 5)
End If
End If

' filter on order number if selected
If Not IsNull(Me.CmboOrderNumber) Then
strFilter = strFilter & "OrderNumber = " & Me.CmboOrderNumber
End If

' if filter has been selected set filter property and turn on filter
If len(strFilter) > 0 Then
Me.Filter = strFilter
Me.FilterOn = True
Else
' otherwise turn off filter to show all records
Me.FilterOn = False
End If

Using the AfterUpdate event procedures, if both combo boxes are Null to
start with then when you select a sales rep you should see all records for
that rep; then when you select an order number you should see the record(s)
for that sales rep for that order number only.

Using a separate button the filter will only be implemented when the button
is clicked, after making the selections in the combo boxes.

If each order number can apply to one sales rep only then you can set it up
so that when you select a sales rep the order numbers combo box will show
just the orders for that sales rep. To do this set the RowSource property of
the CmboOrderNumber control so it references the CmboSalesrep control e.g.

SELECT OrderNumber FROM Orders WHERE SalesRep =
Forms!LoanerSearch!CmboSalesRep ORDER BY OrderNumber;

and in the AfterUpdate event of CmboSalesRep requery CmboOrderNumber with:

Me.CmboOrderNumber = Null
Me.CmboOrderNumber.Requery

If you do this I'd suggest using a separate button to filter the form, but
you could do it if using the AfterUpdate events to implement the filter
progressively. If you do this change the AfterUpdate event procedure of
CmboSalesRep like so:

Dim strFilter As String

' filter on sales rep if one selected
If Not IsNull(Me.CmboSalesRep) Then
strFilter = "SalesRep = """ & Me.CmboSalesRep & """"
End If

' requery CmboOrderNumber to show relevant orders only
Me.CmboOrderNumber.Requery
' set CmboOrderNumber to Null
CmboOrderNumber = Null

' if filter has been selected set filter property and turn on filter
If len(strFilter) > 0 Then
Me.Filter = strFilter
Me.FilterOn = True
Else
' otherwise turn off filter to show all records
Me.FilterOn = False
End If

Leave the code for CmboOrderNum's AfterUpdate event procedure as before.

In all of the above I've assumed that SalesRep is of text data type and
OrderNumber of number data type, hence the inclusion of quotes characters
around the former when building the filter expression.

Ken Sheridan
Stafford, England

 

[aaron_kempf]

with Access Data Projects; you can use _TWO_ different filters.
The 'ServerFilter' and the 'Filter'.

I had read somewhere recently that Access MDB doesn't support multiple
items on the filter property. It kinda shocked me when I read that--
I wish I had saved that bookmark.

I think that it might have been in an Access 2000 book by MS Press.

-Aaron

 

[Kat]

Ken -

Could I run by you another stumper I have? I have one form that has 2
subforms in it. When I open the main form to filter on one record I will get
multiple records if one of the subforms has multiple records. So have a main
form with all information regarding an order. Then in a subform (that is
linked to a table called Call Log by the order number) I use this to log the
all phone calls that I have to customer regarding this order number. How can
I have it show all the phone logs with out duplicating the header for every
record in the subform?

 

[Rick Brandt]

with Access Data Projects; you can use _TWO_ different filters.
The 'ServerFilter' and the 'Filter'.

I had read somewhere recently that Access MDB doesn't support multiple
items on the filter property. It kinda shocked me when I read that--
I wish I had saved that bookmark.

I think that it might have been in an Access 2000 book by MS Press.

Then that book would be wrong. An Access filter supports any valid SQL
WHERE clause (with the word "WHERE" removed).

 

[Ken Sheridan]

Kat:

It rather sounds like the main form might be based on a query which joins
the Orders and Call Log tables. If this is the case then remove the Call Log
table from the query on which the main form is based. You should then get
only one record per order in the main form and the calls for the order in the
subform.

Its also possible that the main form might be based on a query which joins
the Orders table to another table, e.g. an OrderDetails table with one row
per item. In this case you'd get multiple records per order, one for each
item ordered, rather than one for each call.

The basic principle her is that the main form's RecordSource should include
either the Orders table solely, or the Orders table joined to other table(s)
to which it is related many-to-one, e.g. a Customers table, but not any
table(s) to which it is related one-to-many, e.g. the Call Log table.

If the cause of the problem isn't along these lines post back with more
details of the main form's RecordSource.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Aaron:

I think it must have been referring to something other than what Kat is
looking for, though I can't imagine what. The Filter property is merely an
expression which evaluates to True, False or Null for each row in the
underlying recordset, those evaluating to True being returned, so can include
Boolean to combine criteria in the usual way.

Ken Sheridan
Stafford, England

 

[Ali]

I am using a main forum with a subforum. I have about 8 combo boxes
and start date and end date fields also. I am using a filter button to
sort all of this. How can I get this going? Not all fields would be
filled to filter.

 

[Ken Sheridan]

Base the subform on a query which references the controls on the main form as
parameters. For each control examine it for 'OR IS NULL' to make it
optional. For simplicity lets assume that there are only three controls,
one, a combo box, for CustomerID and the others, both text boxes, for
DateFrom and DateTo so that orders for a selected customer between two dates
can be returned. The query would be along these lines:

PARAMETERS
Forms!YourForm!txtDateFrom DATETIME,
Forms!YourForm!txtDateTo DATETIME;
SELECT *
FROM Orders
WHERE (CustomerID = Forms!YourForm!cboCustomerID
OE Forms!YourForm!cboCustomerID IS NULL)
AND
(OrderDate >= Forms!YourForm!txtDateFrom
OR Forms!YourForm!txtDateFrom IS NULL)
AND
(OrderDate < DATEADD("d", 1,Forms!YourForm!txtDateTo)
OR Forms!YourForm!txtDateTo IS NULL);

For more controls you'd extend the query in the same way for each. Note
that I've declared the date parameters. This can be important with date
parameters as otherwise they might be interpreted as arithmetic expressions
rather than dates if entered in short date format, and give the wrong results.

With the above example if the user selects a customer, but enters no dates
all orders for the selected customer will be returned; if they don't select a
customer but enter both dates, then all orders for all customers between the
dates will be returned; if they select a customer and enter both dates then
orders between those dates for the selected customer only will be returned.
If they only enter one date then orders any time after of before that date,
depending which one is entered, will be returned. And so on…

To filter the rows shown in the subform on the basis of the values in the
controls on the main form requery the subform control in the button's Click
event procedure:

Me.sfrOrders.Requery

where sfrOrders is the name of the subform control, i.e. the control in the
main form which houses the subform, not the name of its underlying form
object; unless both have the same name of course.

Ken Sheridan
Stafford, England

 

[Ali]

Where am I putting this code? On the On Click event of the "Set
Filter" button?

Currently my subform recordsource is SELECT issues.*, IIf([ID],"View
or Edit","") AS [View or Edit] FROM issues;

I also want all the records to show when i open the form. Then I want
to be able to filter it.

 

[Ken Sheridan]

The Click event procedure of the button just needs one line to requery the
subform control, e.g.

Me.sfrOrders.Requery

The SQL for the query, adapted to suit your table and field names, and the
names of the controls on the main form, can either be used directly as the
RecordSource property of the subform, or can be saved as a query and the name
of the query then used as the RecordSource property of the subform.

Assuming all the controls in the main form in which the parameter values to
filter the subform are entered are Null on opening, then the subform should
show all rows because the IS NULL criteria for each parameter will evaluate
to true for every row.

Ken Sheridan
Stafford, England

Where am I putting this code? On the On Click event of the "Set
Filter" button?

Currently my subform recordsource is SELECT issues.*, IIf([ID],"View
or Edit","") AS [View or Edit] FROM issues;

I also want all the records to show when i open the form. Then I want
to be able to filter it.

Base the subform on a query which references the controls on the main form as
parameters. For each control examine it for 'OR IS NULL' to make it
optional. For simplicity lets assume that there are only three controls,
one, a combo box, for CustomerID and the others, both text boxes, for
DateFrom and DateTo so that orders for a selected customer between two dates
can be returned. The query would be along these lines:

PARAMETERS
Forms!YourForm!txtDateFrom DATETIME,
Forms!YourForm!txtDateTo DATETIME;
SELECT *
FROM Orders
WHERE (CustomerID = Forms!YourForm!cboCustomerID
OE Forms!YourForm!cboCustomerID IS NULL)
AND
(OrderDate >= Forms!YourForm!txtDateFrom
OR Forms!YourForm!txtDateFrom IS NULL)
AND
(OrderDate < DATEADD("d", 1,Forms!YourForm!txtDateTo)
OR Forms!YourForm!txtDateTo IS NULL);

For more controls you'd extend the query in the same way for each. Note
that I've declared the date parameters. This can be important with date
parameters as otherwise they might be interpreted as arithmetic expressions
rather than dates if entered in short date format, and give the wrong results.

With the above example if the user selects a customer, but enters no dates
all orders for the selected customer will be returned; if they don't select a
customer but enter both dates, then all orders for all customers between the
dates will be returned; if they select a customer and enter both dates then
orders between those dates for the selected customer only will be returned..
If they only enter one date then orders any time after of before that date,
depending which one is entered, will be returned. And so on…

To filter the rows shown in the subform on the basis of the values in the
controls on the main form requery the subform control in the button's Click
event procedure:

Me.sfrOrders.Requery

where sfrOrders is the name of the subform control, i.e. the control in the
main form which houses the subform, not the name of its underlying form
object; unless both have the same name of course.

Ken Sheridan
Stafford, England