Cascading combos is a recurrent demand and many questions posted here concern this topic. Here is a class that allows to bound two combos in a cascading relationship.

1. Create a new Class Module and paste this code into it.
Code:
Private WithEvents m_cboParent As ComboBox
Private m_cboChild As ComboBox
Private m_lngParentIndex As Long
Private m_lngChildIndex As Long
Private m_lngColumnType As Long
Private m_strParentColumn As String
Private m_strChildColumn As String
Private m_strChildSource As String

Private Sub Class_Terminate()

    Set m_cboParent = Nothing
    Set m_cboChild = Nothing
    
End Sub

Public Function Initialize(ByVal ParentComboBox As ComboBox, _
                           ByVal ChildComboBox As ComboBox, _
                           ByVal ParentIndex As Long, _
                           ByVal ChildIndex As Long, _
                           Optional ByVal Silent As Boolean) As Boolean

    Dim strObjectName As String
    
    Set m_cboParent = ParentComboBox
    Set m_cboChild = ChildComboBox
    If ParseComboBox(ParentComboBox, ParentIndex, False) = False Then
        strObjectName = ParentComboBox.Name
    Else
        If ParseComboBox(ChildComboBox, ChildIndex, True) = False Then strObjectName = ChildComboBox.Name
    End If
    If Len(strObjectName) > 0 Then
        If Silent = False Then
            MsgBox "Error while parsing " & strObjectName & ".", vbExclamation, "Cls_CascadingCombos:Initialize"
        End If
    Else
        m_cboParent.AfterUpdate = "[Event Procedure]"
        Initialize = True
    End If
        
End Function

Private Function ParseComboBox(ByVal Combo As ComboBox, ByVal Index As Long, Child As Boolean) As Boolean

    Dim qdf As DAO.QueryDef
    
    If Combo.RowSourceType = "Table/Query" Then
        Set qdf = CurrentDb.CreateQueryDef("", Combo.RowSource)
        If Index <= qdf.Fields.Count Then
            If Child = False Then
                m_lngParentIndex = Index
                m_strParentColumn = qdf.Fields(Index).Name
            Else
                m_lngChildIndex = Index
                m_strChildColumn = qdf.Fields(Index).Name
                m_strChildSource = Replace(Combo.RowSource, ";", "")
                If InStr(m_strChildSource, "ORDER BY") > 0 Then
                    m_strChildSource = Replace(m_strChildSource, "ORDER BY", "@C ORDER BY")
                Else
                    m_strChildSource = m_strChildSource & " @C"
                End If
                m_strChildSource = Trim(m_strChildSource) & ";"
                Select Case qdf.Fields(Index).Type
                    Case dbText, dbMemo:    m_lngColumnType = 1 ' Text --> Use "'".
                    Case dbDate:            m_lngColumnType = 2 ' Date/Time --> Use "#"
                    Case dbBoolean:         m_lngColumnType = 3 ' Boolean --> Convert to vbFalse (False) or vbTrue (True)
                    Case Else:              m_lngColumnType = 0 ' Numeric or boolean --> Do nothing.
                End Select
            End If
        End If
        ParseComboBox = True
    End If

End Function

Private Sub m_cboParent_AfterUpdate()

    Dim varValue As Variant
    Dim strvalue As String
    
    varValue = m_cboParent.Column(m_lngParentIndex)
    Select Case m_lngColumnType
        Case 0: strvalue = varValue ' Numeric or boolean.
        Case 1: strvalue = "'" & varValue & "'" ' Text.
        Case 2: strvalue = "#" & Year(varValue) & "-" & Month(varValue) & "-" & Day(varValue) & "#" ' Date/Time.
        Case 3: strvalue = IIf(varValue = False, vbFalse, vbTrue) ' Boolean.
    End Select
    m_cboChild.RowSource = Replace(m_strChildSource, "@C", " WHERE " & m_strChildColumn & " = " & strvalue)
    m_cboChild.Requery
    
End Sub
2. Save the Class Module as Cls_CascadingCombos

3. In the module of the form that contains the two combos you want to act as cascading combos, paste this code:
Code:
Private m_clsCascadingCombos As Cls_CascadingCombos

Private Sub Form_Open(Cancel As Integer)

    Set m_clsCascadingCombos = New Cls_CascadingCombos
    m_clsCascadingCombos.Initialize Me.Combo_1, Me.Combo_2, 0, 1
    
End Sub
That's all!

Notes:

a) Both combos must have their RowSourceType property set to "Table/Query".
b) The RowSource property of both combos must be a "SELECT... " statement without a "WHERE..." clause ("ORDER BY..." is ok).
c) In the statement:
Code:
    m_clsCascadingCombos.Initialize Me.Combo_1, Me.Combo_2, 0, 1
- Combo_1 is the first combo (i.e. the one that intitiate the cascading action --> the parent).
- Combo_2 is the second combo (i.e. the one the contents of which changes then another value is selected in the parent combo --> the child).
- The first numeric value (0 in the example) is the index of the column in the parent combo that will be used to change the contents of the child combo.
- The second numeric value (1 in the example) is the index of the column in the child combo that must match the value of the specified column in the parent combo.
d) You can pass an optional parameter to the Initialize method of the class to prevent it displaying an error message if there is a problem while performing the initialization of the combos. In such a case, test the return value of the method:
Code:
    If m_clsCascadingCombos.Initialize (Me.Combo_1, Me.Combo_2, 0, 1, True) = False then '--> an error occured while initializing the class.