Syncing Multiple ComboBoxes

[f15e]

I'm hoping you can help me with this even though I know this topic has been beaten to a pulp.

I have 10 comboboxes where each combobox selection filters a datasheet subform further down. Each one of the comboboxes are associated with a specific column on the subform datasheet. There is no order in which the user has to selcct a combobox. If the user selects any one of the 10 comboboxes, I would like other 9 to update based on the remaining dinstinct items left in the associated datasheet column. From here, the process continues until the user selects then number of comboboxes needed to get to the result wanted. My subform filtering works great. It's the comboboxes that I'm having trouble with.

Below is how I'm filtering my subform. The first If statement is for the first combobox selected and the ElseIf statement is for comboboxes selected after the first one where I'm concatenating the previous filters. How do I get my comboboxes to filter correctly? Thank you!

'************************************************* ************************************************** *****
Public Sub Filter_SubForm(CBO_Field_Name As String, sel_cbo_txt As String, CBOs_used As Integer, sel_cbo_name As String)

Dim Main_Form As Form_DB_Docs_Search_Form

Set Main_Form = Form_DB_Docs_Search_Form

sel_cbo_txt = Trim(sel_cbo_txt)

If Len(sel_cbo_txt) > 0 And CBOs_used = 1 Then
Tmp_Filt = "[" & CBO_Field_Name & "] = '" & sel_cbo_txt & "'"


ElseIf Len(sel_cbo_txt) > 0 And CBOs_used > 1 Then
Tmp_Filt = (Tmp_Filt & " And ") & "[" & Datasheet_Field_Name & "] = '" & sel_cbo_txt & "'"
End If


Main_Form.DB_Docs_Search_SubForm.Form.Filter = Tmp_Filt
Main_Form.DB_Docs_Search_SubForm.Form.FilterOn = True

Set Main_Form = Nothing
End Sub
'************************************************* ************************************************** *****

[davegri]

Beaten to a pulp, yes, but most examples are for 2 comboboxes that must me selected in strict order. This example allows any order, and whenever a selection is made in any combobox, the rowsource for all the others is recalculated.
Have a look at
Ultimate Dynamic Multiple Cascading Comboboxes, synchronized in any order

[jojowhite]

you can also try this code, add this to your main form:

Public Function fncFilterSubForm()
    ' MickeyMouse
    ' you need to rename your 10 comboboxes with
    ' common name + a suffix number.
    '
    ' say, for combobox number 1, you name it
    ' cbx1, and for combobox 2, cbx 2, etc.
    '
    ' now create an array here that will hold the
    ' Name of the field each combobox is associated
    ' with for filtering.
    '
    ' on this demo i only have four fields in Table1 to filter
    Dim arrFields As Variant
    Dim i As Integer
    Dim sFilter As String
    
    ' the array elements are associated with cbx1, cbx2, cbx3 and cbx4 respectively
    ' on my demo, they are as follows:
    '
    ' change the array to reflect your fields in the form.
    arrFields = Array("Country", "State", "City", "Street")
    
    Dim m_form As Form, combo As ComboBox
    ' set the your form here
    Set m_form = Forms!Table1
    
    ' loop though each comboboxes on the form
    For i = 1 To 4
        Set combo = m_form.Controls("cbx" & i)
        If combo.ListIndex > -1 Then
            sFilter = sFilter & " And " & arrFields(i - 1) & "='" & Replace$(combo, "'", "''") & "'"
        End If
    Next i
    
    ' check if there is a filter
    If Len(sFilter) <> 0 Then
        ' remove the first 'And' from the filter
        sFilter = Mid$(sFilter, 6)
        With m_form
            .filter = sFilter
            .FilterOn = True
        End With
    Else
        m_form.FilterOn = False
    End If
    
    Set m_form = Nothing
    
End Function

on design view of your Main form, add this to each of your combobox (cbx1 to cbx10)"

=fncFilterSubForm()

see table1 form on the demo.

[ArviLaanemets]

Maybe use Change Events for all those combos!

The Event constructs the filter string for subform looking for current values of all 10 combos, resets all RowSources for other 9 combos taking into account the change in current one, and then updates the filter for subform with constructed filter string.

[f15e]

Thanks for the replies. I will try your suggestions.

One other thing. How would I go about copying a filtered subform datasheet to a newly created table?

[jojowhite]

you can use a Recordset to add to a Tables recordset, here is a snippet code on the Main Form:

    Dim dbs As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsTarget As DAO.Recordset
    Dim m_Form As Form
    Dim fld As DAO.Field
    Dim fldName As String
    
    Set m_Form = Main_Form.DB_Docs_Search_SubForm.Form
    
    Set dbs = CurrentDb
    Set rsSource = m_Form.RecordsetClone
    Set rsTarget = dbs.OpenRecordset("YourNewlyCreatedTableHere")
    
    With rsSource
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Do Until .EOF
                rsTarget.AddNew
                For Each fld In .Fields
                    fldName = fld.Name
                    ' do not copy Autonumber field
                    If Not (fld.Attributes And dbAutoIncrField) Then
                        rsTarget.Fields(fldName) = fld.Value
                    End If
                Next
                rsTarget.Update
                .MoveNext
            Loop
        End If
    End With
    Set rssrource = Nothing
    rsTarget.Close: Set rsTarget = Nothing
    Set dbs = Nothing

[Welshgasman]

If you only have a few records, a recordset might be OK, but I would probably use an Append query and your Select as the criteria.
Not something I have ever needed to do though to be honest. Why do you need that filtered recordset in a new table?, why can't you just refer to it in a query?

[Gicu]

Here is an example of a couple of days to capture the filtered data.
Cheers,

[jojowhite]

here is another demo that will save filtered/unfiltered records to Table2.
using SQL Insert query as proposed in post #7.

[f15e]

So I got comboboxes to sync like I want although it's not the best or most efficient way to do it. I was having a lot of problems with the suggestions given so what I ended up doing was making a temporary copy of my main table, deleting records using queries based on my combobox selections, and then reloading the remaining comboboxes with the distinct values left in my temp table. I also run a routine to check the remaining comboboxes for having only 1 selection left where I then force that selection to display, or if there are 0 selections for that combobox, I display a N/A on the combobox window. No selections available is b/c not all rows are are completely filled for each field and based on the combobox selections, you sometimes have no choices left in your dropdown. This is not by choice; it's just the data I have to work with. As part of my reset button, I delete the existing temp table and make a new copy of my main table, reload the comboboxes, etc....

Overtime I will tweak what I currently have and try to remove the hard coded sections to make it more dynamic and efficient. I did use parts of your suggestions and I greatly appreciate your responses!

[jojowhite]

if you're goal is to just create new rowsource for your 10 combos based on the records being filtered,
then that can be accomplished without ever needing a temp table (that will bloat and increase the size
of your db).

create a Form-wise variable that will hold the filter.
then adjust your combos recordsource using the filter.

the demo db provided only has 4 records, if you can provide enough
records (plus your current form), then we can show you more without
the ever needing a Temp table.

[f15e]

Thanks jojowhite! It may be several days before I'll be able to look into your suggestion, but I definitely will. As I mentioned, my solution is not the best or even good practice, but it works for now. I do want to make it better and learn from it so I'll definitely be taking your suggestion and revising my current version to make it better.