Listbox to show all table but then filter comboboxes?

[Stildawn]

Hi All

I am building a form, on it will be a listbox that just contains all columns (minus ID) from the database table.

When the form opens, I want the listbox to show all records.

Above the listbox will be a bunch of comboboxs that hopefully will filter the listbox below.

What I need the listbox to do, is always show all records unless a combobox is entered, then only show records that match the value.

But there can be more than one filter, for example there is a origin city column and destination city column. Both of these will have comboboxes.

If the user selects "New York" in origin combobox, then only records that have origin as "New York" will show in the listbox. But if the user has both "New York" in origin, and "Miami" in destination, then again only records that show origin = New York AND destination = Miami will show.

There is more than two, and each one needs to be able to filter the listbox by itself as in the example below, the user can leave the origin and just filter by "Miami" in destination combobox. It also needs to filter with all the other comboboxs as well?

I hope that makes sense?

Thanks in advance.

 

[JHB]

And what problem do you have with that if any?

 

[theDBguy]

Hi. Welcome to the forum. Have you tried looking for some demos? It may not be exactly what you want but you should be able to use some of it.

 

[Stildawn]

Sorry, I dont know how to do it well.

I have this so far that I've worked out myself:

Private Sub Combo61_AfterUpdate()


    Dim strRS As String
      
    'Creates filters based on combobox
    If Me.Combo61.ListIndex >= 0 Then strRS = strRS & " WHERE ((([Imports FRT Rates].POL)=""" & Me.Combo61.Value & """)"
    
    If Me.Combo78.ListIndex >= 0 Then strRS = strRS & " WHERE (([Imports FRT Rates].POD)=""" & Me.Combo78.Value & """)"
    
    'Adds filter to string
    strRS = "SELECT [Imports FRT Rates].POL, [Imports FRT Rates].POD, [Imports FRT Rates].Carrier, [Imports FRT Rates].Country, [Imports FRT Rates].[20GP], [Imports FRT Rates].[40GP], [Imports FRT Rates].[Valid From], [Imports FRT Rates].[Valid To], [Imports FRT Rates].[Transit Time (Days)] FROM [Imports FRT Rates]" & strRS & ");"

    'Updated source to Listbox
    If Me.Combo61.Value = "" Then
        If Me.Combo78.Value = "" Then
            Me.List19.RowSource = "SELECT [Imports FRT Rates].POL, [Imports FRT Rates].POD, [Imports FRT Rates].Carrier, [Imports FRT Rates].Country, [Imports FRT Rates].[20GP], [Imports FRT Rates].[40GP], [Imports FRT Rates].[Valid From], [Imports FRT Rates].[Valid To], [Imports FRT Rates].[Transit Time (Days)] FROM [Imports FRT Rates];"
            Me.List19.Requery
        Else
            Me.List19.RowSource = strRS
            Me.List19.Requery
        End If
    Else
        Me.List19.RowSource = strRS
        Me.List19.Requery
    End If

End Sub

I dont think its overly good approach though, I dont think it expands well with more and more comboboxs etc, and it seems like this is something that Access would be able to do more naturally?

Also the code creates an error when trying to run the front end via Access Runtime on another machine:

The expression On Load you entered as the event property setting produced the following error:

The operation on the object | failed. The expression may not result in the name of a macro, the name of a user defined function, or [event procedure] There may have been an error evaluating the function, event, or macro

This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated for example if the OnOpen property of a form is set to = [Field], this error occurs because a macro or event name is expected to run when the event occurs

 

[isladogs]

Its all very easy to do but I suggest you use a subform rather than a listbox to display the filtered data.
I have an example app that shows how it can be done with multiple filters.
See the Student Target Grade Explorer app at http://www.mendipdatasystems.co.uk/multiple-group-filter/4594454290