Filter parents on which children are selected

Question

I have a table with products and a table with components. Each product has many components, so they are joined by a 'bundles' table.

tblProducts
PID, Description
1, Alpha
2, Bravo
3, Charlie

tblComponents
CID, Description, Category
11, Apple, Cat1
12, Banana, Cat2
13, Orange, Cat3

tblBundles
PID, CID
1, 11
1, 12
1, 13
2, 12

I need to create a form with several listboxes (based on the tblComponents.Category) that will allow me to end up with a filtered list of products. e.g. choose Banana and be left with Product 1 and 2. Then choose Orange and be left with Product 1.

How can I go about getting this?

You seem to want cascading comboboxes ( http://stackoverflow.com/questions/927256/is-there-a-simple-way-of-populating-dropdown-in-this-access-database-schema/927588#927588 ) However, I suspect that a subform might be better (http://stackoverflow.com/questions/12131211/create-form-to-add-records-in-multiple-tables/12132196#12132196). — Fionnuala, Jan 12 '15 at 13:31

score 0 · Answer 1 · answered Jan 14 '15 at 10:49

in case you want to create cascading comboboxes you can proceed in this way:

cmbComp is the Father combobox, cmbProd is the child populated basing on the component selected.

in the AfterUpdate event of cmbComp you must create the Record source string to populate the 2nd combo.

Private Sub cmbComp_AfterUpdate()
    Dim qdf As QueryDef, strSQL As String
    Dim i As Integer
    Dim qryName As String

    '-------------------------------------------------
    ' Delete all combobox items
    '-------------------------------------------------
    Me.cmbProd.RowSource = ""
    Me.cmbProd.Requery

    '-------------------------------------------------
    ' Define a name for the query
    '-------------------------------------------------
    qryName = "qryProducts"

    '-------------------------------------------------
    ' Delete query if already existing
    '-------------------------------------------------
    For Each qdf In CurrentDb.QueryDefs
        If qdf.Name = qryName Then
            CurrentDb.QueryDefs.Delete qryName
        End If
    Next qdf

    '-------------------------------------------------
    ' Create a query to use as a RowSource
    '-------------------------------------------------
    strSQL = "SELECT tbBundles.PID, " & _
             "DlookUp(""Product"",""tbProducts"",""PID=""+cstr([tbBundles.PID])) " & _
             "AS Product FROM tbBundles WHERE CID = " + CStr(Me.cmbComp)

    Set qdf = CurrentDb.CreateQueryDef(qryName, strSQL)     ' Create a query (check in the Navigation Pane)

    Set qdf = Nothing                                       ' Destroy the object qdf

    '-------------------------------------------------
    ' Set up the child combo-box
    '-------------------------------------------------
    With Me.cmbProd
        .ColumnCount = 2
        .ColumnWidths = "0;2,54"        ' Show Product as item of the list
        .RowSource = "qryProducts"      ' Set query as rowsource
        .Requery
    End With

End Sub

Please, note that to try this snippet of code you must create a form with 2 combos, cmbComp (RowSource set up with wizard on tbComponents) and cmbProd, unbounded (it will be populated by code) The form doesn't need any DataSource to try the code. I tried on Access 2010 and worked with you tables. Bye — Wiz, Jan 14 '15 at 10:52

Filter parents on which children are selected

1 Answers1