Help with comboxes filtering and sorting a query

[cinnie]

Hello - I have two comboxes that filter and sort records in query
qryRegSales.

The first is used to Filter results in a query:

Private Sub cbxFilterRegion_AfterUpdate()
Me.Filter = "[RegionID] = " & cbxFilterRegion
Me.FilterOn = True
End Sub

The second is used to chose a sort field for the query:

Private Sub cbxSelectSortField_AfterUpdate()
Dim strQry As String
Select Case Me.cbxSelectSortField
Case "North"
strQry = "SELECT * FROM qryRegSales ORDER BY North"
Case "South"
strQry = "SELECT * FROM qryRegSales ORDER BY South"
Case ... etc
.....
Case Else
MsgBox "Not a recognized value."
End Select
Me.RecordSource = strQry
Me.Requery
End Sub

PROBLEM: both comboxes work fine alone, but I want them to work together.
When I use cboFilter, the records are filtered as expected. But, when I then
use cboSortField, it sorts the correct field but 'forgets' about the filter.
(All records show again) I'd like to be able to use either box independently
or both in combination, but I just can't get the code right. Interestingly
enough, if I apply the sort THEN the filter in that order, all works fine.
Any clues out there?

thank you

 

[Carl Rapson]

My guess is, resetting the RecordSource of the form somehow is wiping out
the Filter. That's why applying the filter AFTER the RecordSource works. My
suggestion would be to handle both cases in both AfterUpdate events,
RecordSource first and Filter second.

Carl Rapson