Full Version: Sorting Listbox off of two combo boxes?
UtterAccess Forums > Microsoft® Access > Access Forms
I am trying to sort a listbox based off the selection of two combo boxes.
Ocan't figure out the query or anyway to do this.
Thanks again,
ps. demo attached
When you say "Sort" - do you in fact mean Filter?
If you were sorting - you'd choose a field, not a value.
Hi Leigh,
suppose Filter then....
Still stuck..
Here's the actual attachment...
Fixed the issue myself..
Thanks for everyones assistance...
Attached is the working sample..
FWIW I'd look to filter the already existing recordset of the listbox.
(Each time you're "filtering" the listbox by assigning the rowsource you're just requerying the database for a smaller version of the list you already have.)
The code added is :
Dim rstPerm As DAO.Recordset
Private Sub cboCategorySort_AfterUpdate()
End Sub
Private Sub cboRatingSort_AfterUpdate()
End Sub
Sub SortTheList()
    Dim strFilter As String
    strFilter = IIf(IsNull(Me.cboCategorySort), "", "Category = '" & Me.cboCategorySort & "'")
    strFilter = strFilter & IIf(IsNull(Me.cboRatingSort), "", IIf(Len(strFilter) > 0, " AND ", "") & "Rating = '" & Me.cboRatingSort & "'")
    If Len(strFilter) > 0 Then
        If rstPerm Is Nothing Then
            Set rstPerm = Me.SortList.Recordset.Clone
        End If
        rstPerm.Filter = strFilter
        Set Me.SortList.Recordset = rstPerm.OpenRecordset
        Set Me.SortList.Recordset = rstPerm
    End If
End Sub

Notice the addition of the Option Explicit option in your module's header.
This is soo important to always include.
Hi Leigh

Never knew you could filter a recordset like this - Great tip - thanks.gif
No probs, glad you find it interesting Bernie.
This sort of implementation is an area of functionality that can be massively powerful.
Although its only available in Acc2002 onwards (where the native listcontrols were given their own Recordset object property).
But if you're meaning DAO's recordset filtering in particular then yeah - that's been there pretty much since year dot. :-)
This example is not massively dissimilar to the Combo Filter example on my examples page... Sheesh! My website is still down. That's well dodgy! I'll attach it here instead :-s
Though in that example the recordset is assigned (and I use an ADO one) - ultimately it's all still an object to reference, set or retrieve and then manipulate at will.
Mr. Purvis,
bow to thee....
Thank you.
No problemo.
Hi Leigh
implemented your suggestion of filtering on an already existing recordset, but have two observations
1. The method relies on setting rstPerm as a global variable,
If there is an error then global variables get reset, and I try to avoid using them; I have implemented your method but rather than using the recordset clone method when rstPerm is nothing, I have specified (e.g.)
    If rstPerm Is Nothing Then
        strSQL = "SELECT TableName.* From Tablename;"
        Set qdf = CurrentDb.CreateQueryDef("")
        With qdf
            .SQL = strSQL
            Set rstPerm = .OpenRecordset()
        End With
    End If

2. I specify an order (which is variable depending on form selection) hence have amended the code:
    Select Case srcForm1![CmbSort]
    Case 1
        strSort = "Sort Criteria 1"
    Case 2
        strSort = "Sort Criteria 2"
    Case 3
        strSort = "Sort Criteria 3"
    End Select
    rstPerm.Filter = strFilter
    rstPerm.Sort = strSort
    Set srcForm1.CalibItemID.Recordset = rstPerm.OpenRecordset

So, question is: Have I handled this in the right way? Is there a better way?
Hi Bernie
It certainly doesn't require a global variable. The example did use a module level variable though. (If so desired - it could be pulled into the procedure and be made static! But Statics, though convenient at times, can be a pain to destroy in a controlled manner.)
Though I made no attempt to put any real error handling in there, it would be fairly standard stuff that could be used.
However an unhandled error is what's required to scupper variables in memory (global or otherwise) - and such an occurance in the application would probably be disasterous anyway - in the runtime you're done and out, otherwise you've (hopefully) just had the user select End (which is what actually kills the variables - just like an End command in code).
Opening the recordset yourself is naturally an option. FWIW it's the more common method of dealing with list and form recordsets (i.e. explicitly assigning them - rather than working with their default recordset).
Checking if it's Nothing is the standard check to see if it needs to be relaunched.
(Again though - if I've had an unhandled error somewhere in the application then I personally view that as a far bigger problem than a dialog's list no longer working afterwards :-)
Naturally - if you're opening and assigning the recordset yourself - make sure that the combo isn't bound by default to begin with. That's just being wasteful then. :-)
Hi Leigh
Thank you for the clarification, your post, as ever, superb. notworthy.gif
In the context I am using it, a static variable sounds ideal - though (and showing my ignorance here) I have not come across them before. Destroying this static variable shouldn't be a problem as it will always be required until the form is closed: - from Visual Basic
Hi Bernie. Thanks.
Yes the Static dropping out of scope when the class is destroyed (i.e. form closes) isn't what I mean by a controlled destruction ;-)
Whereas with a module level vairable - we're free to manually call
Set Object = Nothing
to do so with a static requires a procedure that can be called in different modes (which is just *not* worth it).
FWIW - the "going out of scope" default destruction should generally be fine. (Especially for an object you didn't explicitly open - like a Clone or built in object).
Hi Leigh
appreciate that you didn't mean that closing the form was a controlled destruction, I intended to mean that by closing the form (ie it going out of scope) I did not have to invoke some other specific method of detroying the variable in a controlled manner - hence 'it wasn't a problem'.
>I'm not following what you mean by "controlled destruction".
It would seem to me that any method that used static variables
would have a parameter, optional or otherwise, that could be
used to initialize/reset the static variables .
That's exactly the kind of thing I was meaning yes. Another entry path into the procedure (decided by dedicated parameter value) just to "destroy" (i.e. close - or whatever) the variables local to that procedure.
IMO it's like going in the opposite direction to the kind of clarity that classes can provide - reusing the same procedure - extra code in it to handle the "mode" in which it's being executed.
Not difficult - just IMO not often worth it for me.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.