Cascading ComboBoxes depending on two Criteria 1

[netrusher]

Below is the code I am using for cascading Comboboxes. This
works ok to a degree. What I am needing is a way for the
Private Sub NonConformanceDescription to be dependent on
the two ComboBoxes above it and not just the one immediately
above it. So I would want the Private Sub NonConformanceDescription Event
to be dependent on both the Privatge Sub SystemGroup Event
and the Private Sub FaultCategory Event.

Can someone point me in the right direction to adjusting
my existing code to accomplish this?

Private Sub FaultCategory_AfterUpdate()

   On Error Resume Next
   SystemGroup.RowSource = "SELECT DISTINCT GrnTroubleShootingGuideTBL.SystemGroup " & _
            "FROM GrnTroubleShootingGuideTBL " & _
            "WHERE GrnTroubleShootingGuideTBL.FaultCategory = '" & FaultCategory.Value & "' " & _
            "ORDER BY GrnTroubleShootingGuideTBL.SystemGroup;"

'    Me.SystemGroup = Blank
'    Me.NonConformanceDescription = Blank
'    Me.NonConformanceGroup = Blank
'    Me.PossibleCause = Blank
'    Me.ProcedureOrAction = Blank
    
        If Me.FaultCategory.Value = "No Faults" Then
        Me.NonConformanceDescription.Value = "No Faults"
        End If

End Sub

Private Sub SystemGroup_AfterUpdate()

   On Error Resume Next
   NonConformanceDescription.RowSource = "SELECT DISTINCT GrnTroubleShootingGuideTBL.NonConformanceDescription " & _
            "FROM GrnTroubleShootingGuideTBL " & _
            "WHERE GrnTroubleShootingGuideTBL.SystemGroup = '" & SystemGroup.Value & "' " & _
            "ORDER BY GrnTroubleShootingGuideTBL.NonConformanceDescription;"
            
'    Me.NonConformanceDescription = Blank
'    Me.NonConformanceGroup = Blank
'    Me.PossibleCause = Blank
'    Me.ProcedureOrAction = Blank

End Sub

Private Sub NonConformanceDescription_AfterUpdate()

   On Error Resume Next
   PossibleCause.RowSource = "SELECT DISTINCT GrnTroubleShootingGuideTBL.NonConformanceGroup " & _
            "FROM GrnTroubleShootingGuideTBL " & _
            "WHERE GrnTroubleShootingGuideTBL.NonConformanceDescription = '" & NonConformanceDescription.Value & "' " & _
            "ORDER BY GrnTroubleShootingGuideTBL.NonConformanceGroup;"
    
'    Me.NonConformanceGroup = Blank
'    Me.PossibleCause = Blank
'    Me.ProcedureOrAction = Blank
    
End Sub

Private Sub NonConformanceGroup_AfterUpdate()

   On Error Resume Next
   PossibleCause.RowSource = "SELECT DISTINCT GrnTroubleShootingGuideTBL.PossibleCause " & _
            "FROM GrnTroubleShootingGuideTBL " & _
            "WHERE GrnTroubleShootingGuideTBL.NonConformanceGroup = '" & NonConformanceGroup.Value & "' " & _
            "ORDER BY GrnTroubleShootingGuideTBL.PossibleCause;"
    
'    Me.PossibleCause = Blank
'    Me.ProcedureOrAction = Blank
    
End Sub

Private Sub PossibleCause_AfterUpdate()

   On Error Resume Next
   ProcedureOrAction.RowSource = "SELECT DISTINCT GrnTroubleShootingGuideTBL.ProcedureOrAction " & _
            "FROM GrnTroubleShootingGuideTBL " & _
            "WHERE GrnTroubleShootingGuideTBL.PossibleCause = '" & PossibleCause.Value & "' " & _
            "ORDER BY GrnTroubleShootingGuideTBL.ProcedureOrAction;"
    
'    Me.ProcedureOrAction = Blank
    
End Sub

 

[Remou]

Is it not already dependant on these two? System Group is dependant on Fault Category, so if Non-Conformance Description is dependant on System Group it is thereby dependant on Fault Category, yesno?

http://lessthandot.com

 

[netrusher]

Well sort of.

The issue is System Group can be the same for two
different Fault Category Values but NonConformanceDescription
values is different depending on the Fault Category Value.

For SystemGroup ComboBox I get the value based on FaultCategory ComboBox
For NonConformanceDescription ComboBox I get the value based on SystemGroup Combobox

What I need is for the NonConformanceDescription ComboBox is to get it's value based on both the
FaultCategory ComboBox and the SystemGroup ComboBox and not just the SystemGroup ComboBox

Hope that makes sense. I do not know how to make the NonConformanceDescription ComboBox Value to be
based on both the FaultCategory ComboBox and the SystemGroup ComboBox

 

[Remou]

Use And.

"SELECT DISTINCT ts.SystemGroup " & _
            "FROM GrnTroubleShootingGuideTBL ts " & _
            "WHERE ts.FaultCategory = '" & FaultCategory.Value & _
            "' AND ts.SystemGroup = '" & SystemGroup.Value & "' " & _
            "ORDER BY ts.SystemGroup;"

http://lessthandot.com

 

[netrusher]

Thanks Remou, I appreciate your help with this.