Filter rowsource of combobox (1 Viewer)

Kayleigh

Member
Local time
Today, 08:53
Joined
Sep 24, 2020
Messages
706
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

I’m here to help
Staff member
Local time
Today, 01:53
Joined
Oct 29, 2018
Messages
21,357
If you simply use a saved query, does the format stay intact?
 
Last edited:

Kayleigh

Member
Local time
Today, 08:53
Joined
Sep 24, 2020
Messages
706
Yes
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:53
Joined
Oct 29, 2018
Messages
21,357
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

AWF VIP
Local time
Today, 04:53
Joined
Jan 11, 2013
Messages
3,849
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:

Code:
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

Registered User.
Local time
Today, 19:53
Joined
Jul 4, 2013
Messages
2,770
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

Super Moderator
Staff member
Local time
Today, 04:53
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom