Error in cascading combo box filter on a sub

[ste4en]

I have 4 steps of cascading comboboxes, each selection filters the data in the next combo box and applies the filter to the line items in the sub form. Everything works fine except when I make the 4th selection the data in the sub form does not requery.

The vba is below; please take a look at the last part "Private Sub ComboGroup4_AfterUpdate()". I included the code for all 4 comboGroup boxes.

I have also taken a screen shot of the form window showing the problem.

Thanks
Steve

Private Sub ComboGroup1_AfterUpdate()

     Dim strSQL As String
     Dim strSQLSF As String
          
     ComboGroup2 = Null
     ComboGroup3 = Null
     ComboGroup4 = Null
     
     strSQL = "SELECT DISTINCT tblData.Group2 FROM tblData "
     strSQL = strSQL & " WHERE tblData.Group1 = '" & ComboGroup1 & "'"
     strSQL = strSQL & " ORDER BY tblData.Group2;"
     
     ComboGroup2.RowSource = strSQL
               
     strSQLSF = "SELECT * FROM tblData "
     strSQLSF = strSQLSF & " WHERE tblData.Group1 = '" & ComboGroup1 & "'"
               
     Me!SfrmUpdate.LinkChildFields = "Group1"
     Me!SfrmUpdate.LinkMasterFields = "Group1"
    
     Me.RecordSource = strSQLSF
     Me.Requery
          
End Sub

Private Sub ComboGroup2_AfterUpdate()

    Dim strSQL As String
    Dim strSQLSF As String
        
    ComboGroup3 = Null
    ComboGroup4 = Null
    
    strSQL = " SELECT DISTINCT tbldata.Group3 FROM tblData "
    strSQL = strSQL & " WHERE tblData.Group1 = '" & ComboGroup1 & "' And  "
    strSQL = strSQL & " tblData.Group2 = '" & ComboGroup2 & "'"
    strSQL = strSQL & " ORDER BY tblData.Group3;"
    
    ComboGroup3.RowSource = strSQL
    
    strSQLSF = " SELECT * FROM tblData "
    strSQLSF = strSQLSF & " WHERE tblData.Group1 = '" & ComboGroup1 & "' And  "
    strSQLSF = strSQLSF & " tblData.Group2 = '" & ComboGroup2 & "'"
    
    
    Me!SfrmUpdate.LinkChildFields = ""
    Me!SfrmUpdate.LinkMasterFields = ""
     
    Me!SfrmUpdate.LinkChildFields = "Group1;Group2"
    Me!SfrmUpdate.LinkMasterFields = "Group1;Group2"
    Me.RecordSource = strSQLSF
    Me.Requery

End Sub

Private Sub ComboGroup3_AfterUpdate()

    Dim strSQL As String
    Dim strSQLSF As String
    
    ComboGroup4 = Null
    
    strSQL = " SELECT DISTINCT tbldata.Group4 FROM tblData "
    strSQL = strSQL & " WHERE tblData.Group1 = '" & ComboGroup1 & "' And  "
    strSQL = strSQL & " tblData.Group2 = '" & ComboGroup2 & "' And "
    strSQL = strSQL & " tblData.Group3 = '" & ComboGroup3 & "'"
    strSQL = strSQL & " ORDER BY tblData.Group4;"
    
    ComboGroup4.RowSource = strSQL
    
    
    strSQLSF = " SELECT * FROM tblData "
    strSQLSF = strSQLSF & " WHERE tblData.Group1 = '" & ComboGroup1 & "' And  "
    strSQLSF = strSQLSF & " tblData.Group2 = '" & ComboGroup2 & "' And "
    strSQLSF = strSQLSF & " tblData.group3 = '" & ComboGroup3 & "'"
    
    Me!SfrmUpdate.LinkChildFields = ""
    Me!SfrmUpdate.LinkMasterFields = ""
     
    Me!SfrmUpdate.LinkChildFields = "Group1; Group2; Group3"
    Me!SfrmUpdate.LinkMasterFields = "Group1; Group2; Group3"
    Me.RecordSource = strSQLSF
    Me.Requery
    
End Sub
Private Sub ComboGroup4_AfterUpdate()

    
    Dim strSQLSF As String
    
    strSQLSF = " SELECT * FROM tblData "
    strSQLSF = strSQLSF & " WHERE tblData.Group1 = '" & ComboGroup1 & "' And  "
    strSQLSF = strSQLSF & " tblData.Group2 = '" & ComboGroup2 & "' And "
    strSQLSF = strSQLSF & " tblData.group3 = '" & ComboGroup3 & "' And "
    strSQLSF = strSQLSF & " tblData.group4 = '" & ComboGroup4 & "'"
    
    
    Me!SfrmUpdate.LinkChildFields = ""
    Me!SfrmUpdate.LinkMasterFields = ""
     
    Me!SfrmUpdate.LinkChildFields = "Group1; Group2; Group3; Group4"
    Me!SfrmUpdate.LinkMasterFields = "Group1; Group2; Group3; Group4"
    Me.RecordSource = strSQLSF
    Me.Requery
   
    
End Sub

 

[ste4en]

Picture did not work trying again...

 

[ste4en]

ok, try this link

http://www.hardyus.com/comboPic.pdf

Thanks

 

[PHV]

Get rid of single quotes for numeric fields:
strSQLSF = strSQLSF & " tblData.group4 = " & ComboGroup4

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ste4en]

that was it thanks very much. Ste4en