Cascading Combobox based on TWO "WHEREs?

[whitelockben]

I want a combobox based on the value of two other comboboxes but I am not sure how to write it in VB. I would have though something like:

Private Sub cboT_AfterUpdate()
On Error Resume Next
cboC.RowSource = "Select DISTINCT tblCables.C " & _
"FROM tblCables " & _
"WHERE tblCables.T = '" & cboT.Value & "' " And "WHERE tblCables.D = '" & cboD.Value & "' " & _
"ORDER BY tblCables.C;"
End Sub

However I am aware this is not correct, can someone help me with this please.

Thanks,

Ben

 

[vbaInet]

Ben,

Firstly, Resume Next is not a good error handling technique so I have removed it. You need to implement a more effective error handling procedure.

Secondly, shouldn't you be using Numeric IDs to filter the query?

Lastly, here's the amended code:

Private Sub cboT_AfterUpdate()
    cboC.RowSource = "Select DISTINCT C " & _
                     "FROM tblCables " & _
                     "WHERE T = '" & cboT.Value & "' And WHERE D = '" & cboD.Value & "' " & _
                     "ORDER BY C;"
    cboC.Requery
End Sub

 

[whitelockben]

Thats great thanks, Ill take aboard the mentioned Numeric IDs.

 

[vbaInet]

You're welcome!

 

[MSAccessRookie]

Ben,

Firstly, Resume Next is not a good error handling technique so I have removed it. You need to implement a more effective error handling procedure.

Secondly, shouldn't you be using Numeric IDs to filter the query?

Lastly, here's the amended code:

Private Sub cboT_AfterUpdate()
    cboC.RowSource = "Select DISTINCT C " & _
                     "FROM tblCables " & _
                     "WHERE T = '" & cboT.Value & "' And [COLOR=red][B]WHERE[/B][/COLOR] D = '" & cboD.Value & "' " & _
                     "ORDER BY C;"
    cboC.Requery
End Sub

Will this work as written, or does the OP need to remove the second WHERE? I always thought there was only one WHERE per SQL.

 

[whitelockben]

No I needed to remove the second where.

Thanks for the input everyone!

 

[vbaInet]

Oops... thanks, I forgot to take that off. I think the OP would have picked up on that.