Filter rowsource of combobox

[Kayleigh]

Hi
What is the best way to filter a listbox 's rowsource. I would like to show all results in listbox onload but if user selects AM/PM, it should filter the listbox to only display those results.
I have been using dynamic SQL strings onChange which work but do not save the data formats as I specify in the query.

 

[theDBguy]

If you simply use a saved query, does the format stay intact?

 

[Kayleigh]

Yes

 

[theDBguy]

Yes

I see. Have you tried using a TempVar for your criteria and then simply requery the listbox? You can update the TempVar in the Change event.

 

[moke123]

I do something similiar if I'm reading this right.
I use an option group with selections of "open", "closed", and "all". The default I use is "Open" but you could set it to "all". In my code I use a select case on the OG and set the rowsource accordingly. Something along these lines:

Private sub SetLists()

Select Case Me.Frame0

    Case 1

        Me.lstClients.RowSource = "Select PartyID, LastName & ', ' & FirstName as vName from tblPeople where PartyID in(" & strOpen & ") order by LastName & ', ' & FirstName"

    Case 2

        Me.lstClients.RowSource = "Select PartyID, LastName & ', ' & FirstName as vName from tblPeople where PartyID in(" & strClosed & ") order by LastName & ', ' & FirstName"


    Case 3
        Me.lstClients.RowSource = "Select PartyID, LastName & ', ' & FirstName as vName from tblPeople where PartyID in(" & strAll & ") order by LastName & ', ' & FirstName"

    End Select

End Sub

I then call SetLists in the onload event, the OG's afterupdate event, and where ever else I need it.

 

[Cronk]

I do the same as Moke123. In multi-column lists, I also put a sort ascending/descending, toggled by double clicking the column header label, on relevant columns eg I might put that on a suburb column but not on the street name.

 

[Pat Hartman]

This is your typical cascading combo.
combo #1 = AM/PM, Combo #2 is filtered by combo#1

Create a querydef as the rowSource for combo2 with a WHERE clause and save it.

Where (AMPM = Forms!yourform!cboAMPM OR Forms!yourform!cboAMPM Is Null)

Then in the AfterUpdate event of cboAMPM, requery combo2
Me.cboDetails.Requery

The OR part of the where condition allows the second combo to open unfiltered initially.

 

[Kayleigh]

Great! Thanks

 

[Pat Hartman]

You're welcome.