Multiple Sort in a Continuous Form - Similiar to AutoFilter

[rquinnan]

Good Evening all, I would greatly appreciate any assistance on this
Access 2003 quandary I'm in. And I do apologize if this has been
answered somewhere else, I didn't see one that addressed my problem
though. I'm looking to be able to sort multiple fields from a form
whose record source is a query of fields from a single table.

Background:
The default view is "Continuous Forms." and in the Form Header
section of the form's design, I have 4 'combo boxes' that are
made to look like drop-down lists. They go left to right in this order:
IPT, Tier5, Risk, and Status. The Row Source Type is 'Table/Query'
and the Row Source for the first looks like: "SELECT IPT.ipt FROM
IPT ORDER BY IPT.ipt;"

The [Event Procedure] for 'On Change' has this Visual basic code
for the 4 combo boxes:
___
Private Sub Combo49_Change()
DoCmd.ApplyFilter , "[IPT] = '" & Me.Combo49 & "'"
End Sub
___
Private Sub Combo83_Change()
DoCmd.ApplyFilter , "[Tier5] = '" & Me.Combo83 & "'"
End Sub
___
Private Sub Combo71_Change()
DoCmd.ApplyFilter , "[Risk] = '" & Me.Combo71 & "'"
End Sub
___
Private Sub Combo72_Change()
DoCmd.ApplyFilter , "[OpenClosed] = '" & Me.Combo72 & "'"
End Sub
___

Issue:
The filters work fine and instantly filter hundreds of fields, but I
can only sort one at a time, rather than have it lock in the filter of
'IPT', and then let me choose from the available fields in
'Tier5' and so on.
In a nutshell, I'd like it to do what Autofilter does for an Excel
spreadsheet, if this is possible.

Thanks in advance for your time.
-RQ

[Allen Browne]

You want to filter by the value of the combo, and sort by that field as
well?

My preferred approach would be to use the AfterUpdate event of the combo to
set the Filter and OrderBy properties of the form, like this:
Private Sub Combo49_AfterUpdate()
If Me.Dirty Then Me.Dirty = False 'Save first
Me.Filter = "[IPT] = '" & Me.Combo49 & "'"
Me.OrderBy = "[IPT]"
Me.FilterOn = True
Me.OrderByOn = True
End Sub
It might be even more efficient to change the RecordSource of the form to a
query string, but the users can't just remove the filter as easily that way.

I don't think you were asking how to assign the secondary and tertiary
fields to the sorting order (so it sorts by IPT first, and then within that
by Tier5, and then within that by Risk), but the code would be:
Me.OrderBy = "IPT, Tier5, Risk"
--
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.

"rquinnan" <rq******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Good Evening all, I would greatly appreciate any assistance on this
Access 2003 quandary I'm in. And I do apologize if this has been
answered somewhere else, I didn't see one that addressed my problem
though. I'm looking to be able to sort multiple fields from a form
whose record source is a query of fields from a single table.

Background:
The default view is "Continuous Forms." and in the Form Header
section of the form's design, I have 4 'combo boxes' that are
made to look like drop-down lists. They go left to right in this order:
IPT, Tier5, Risk, and Status. The Row Source Type is 'Table/Query'
and the Row Source for the first looks like: "SELECT IPT.ipt FROM
IPT ORDER BY IPT.ipt;"

The [Event Procedure] for 'On Change' has this Visual basic code
for the 4 combo boxes:
___
Private Sub Combo49_Change()
DoCmd.ApplyFilter , "[IPT] = '" & Me.Combo49 & "'"
End Sub
___
Private Sub Combo83_Change()
DoCmd.ApplyFilter , "[Tier5] = '" & Me.Combo83 & "'"
End Sub
___
Private Sub Combo71_Change()
DoCmd.ApplyFilter , "[Risk] = '" & Me.Combo71 & "'"
End Sub
___
Private Sub Combo72_Change()
DoCmd.ApplyFilter , "[OpenClosed] = '" & Me.Combo72 & "'"
End Sub
___

Issue:
The filters work fine and instantly filter hundreds of fields, but I
can only sort one at a time, rather than have it lock in the filter of
'IPT', and then let me choose from the available fields in
'Tier5' and so on.
In a nutshell, I'd like it to do what Autofilter does for an Excel
spreadsheet, if this is possible.

Thanks in advance for your time.
-RQ

[rquinnan]

Allen, Thanks for getting back so quickly. I tried your code, and it
still has the same issue. I actually think I might not be explaining
myself clearly. I'm not as concerned as to how the fields will be
laid out after the initial filter. But I'm more interested in the
drop down narrowing the next sort to only those matched. For example,
if my data in the form looks like this:

IPT Tier5
Team A Browne
Team A Larry
Team A Browne
Team X Johnson
Team X Browne
Team X Smith

When I click the drop-down filter for 'IPT' and select 'Team A"
then the result is:

IPT Tier5
Team A Browne
Team A Larry
Team A Browne

But when I click the next combo box drop down for 'Tier5' and
select 'Browne' the result is this:

IPT Tier5
Team A Browne
Team A Browne
Team X Browne

It has forgotten the previous filter of 'Team A' and simply listed
ALL occurrences of 'Browne'
I'd like the result to be a filter by 'Team A' and 'Browne'
Example:

IPT Tier5
Team A Browne
Team A Browne

Again, just like Excel's AutoFilter that "holds" the filter (and
switches the drop-down arrow color to blue) and allows you to filter
the results of the previous filter. Can it be done in MS Access?

Thanks again for your time!!

[Allen Browne]

Okay, so you want to create a string that adds in the value of all the
non-blank combos.

This example is set up so that it creates a filter from any of the combos
that have a value. It tacks an " AND " on the end, so it's easy to add as
many as you wish. Then at the end it chops off the trailing " AND ", and
applies the filter.

You can code it in one of the combos, and then call the code from the other
combos to the filter gets applied as soon as any combo is updated.

Private Sub Combo49_AfterUpdate()
Dim strWhere As String
Dim lngLen As Long

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

If Not IsNull(Me.Combo49) Then
strWhere = strWhere & "([IPT] = '" & Me.Combo49 & "') AND "
End If

If Not IsNull(Me.Combo83) Then
strWhere = strWhere & "([Tier5] = '" & Me.Combo83 & "') AND "
End If

'etc for other combos.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lenLen > 0 Then
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn True
Else
MsgBox "No criteria."
End If
End Sub

Private Sub Combo83_AfterUpdate()
Call Combo49_AfterUpdate
End Sub
Note that this example assumes:
- All these combos are unbound (for filtering only), and
- IPT, Tier5, etc are all Text type fields. Remove the extra quotes for
number fields.

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

"rquinnan" <rq******@gmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Allen, Thanks for getting back so quickly. I tried your code, and it
still has the same issue. I actually think I might not be explaining
myself clearly. I'm not as concerned as to how the fields will be
laid out after the initial filter. But I'm more interested in the
drop down narrowing the next sort to only those matched. For example,
if my data in the form looks like this:

IPT Tier5
Team A Browne
Team A Larry
Team A Browne
Team X Johnson
Team X Browne
Team X Smith

When I click the drop-down filter for 'IPT' and select 'Team A"
then the result is:

IPT Tier5
Team A Browne
Team A Larry
Team A Browne

But when I click the next combo box drop down for 'Tier5' and
select 'Browne' the result is this:

IPT Tier5
Team A Browne
Team A Browne
Team X Browne

It has forgotten the previous filter of 'Team A' and simply listed
ALL occurrences of 'Browne'
I'd like the result to be a filter by 'Team A' and 'Browne'
Example:

IPT Tier5
Team A Browne
Team A Browne

Again, just like Excel's AutoFilter that "holds" the filter (and
switches the drop-down arrow color to blue) and allows you to filter
the results of the previous filter. Can it be done in MS Access?

Thanks again for your time!!