Cascading Combo Boxes

[Tim]

I have a cascading combo box that shows duplicate entries. (ie. I have the
first box set up as a category and the second box w/ suppliers based on
category. My 3rd box has supplies. One supplier may have multiple supplies.
I only need to see the supplier once, not for every supply they provide).

This is the code that I currently have set:
Private Sub cboCategory_AfterUpdate()
On Error Resume Next
cboSupplier.RowSource = "Select tblSupply.Supplier " & _
"FROM tblSupply " & _
"WHERE tblSupply.Category = '" & cboCategory.Value & "' " & _
"ORDER BY tblSupply.Supplier;"
End Sub
Private Sub cboSupplier_AfterUpdate()
On Error Resume Next
cboSupply.RowSource = "Select tblSupply.Supply " & _
"FROM tblSupply " & _
"WHERE tblSupply.Supplier = '" & cboSupplier.Value & "' " & _
"ORDER BY tblSupply.Supply;"
End Sub
Private Sub cboSupply_AfterUpdate()
On Error Resume Next
cboSerial.RowSource = "Select tblSupply.SerialNumber " & _
"FROM tblSupply " & _
"WHERE tblSupply.Supply = '" & cboSupply.Value & "' " & _
"ORDER BY tblSupply.SerialNumber;"
End Sub

Thank you.

 

[SteveS]

Hi Tim,

Have you tried using "Distinct"?

Private Sub cboCategory_AfterUpdate()
On Error Resume Next
cboSupplier.RowSource = "Select DISTINCT tblSupply.Supplier " & _
"FROM tblSupply " & _
"WHERE tblSupply.Category = '" & cboCategory.Value & "' " & _
"ORDER BY tblSupply.Supplier;"
End Sub
Private Sub cboSupplier_AfterUpdate()
On Error Resume Next
cboSupply.RowSource = "Select DISTINCT tblSupply.Supply " & _
"FROM tblSupply " & _
"WHERE tblSupply.Supplier = '" & cboSupplier.Value & "' " & _
"ORDER BY tblSupply.Supply;"
End Sub
Private Sub cboSupply_AfterUpdate()
On Error Resume Next
cboSerial.RowSource = "Select DISTINCT tblSupply.SerialNumber " & _
"FROM tblSupply " & _
"WHERE tblSupply.Supply = '" & cboSupply.Value & "' " & _
"ORDER BY tblSupply.SerialNumber;"
End Sub


HTH

 

[Tim]

Works perfect. Thank you

Hi Tim,

Have you tried using "Distinct"?

Private Sub cboCategory_AfterUpdate()
On Error Resume Next
cboSupplier.RowSource = "Select DISTINCT tblSupply.Supplier " & _
"FROM tblSupply " & _
"WHERE tblSupply.Category = '" & cboCategory.Value & "' " & _
"ORDER BY tblSupply.Supplier;"
End Sub
Private Sub cboSupplier_AfterUpdate()
On Error Resume Next
cboSupply.RowSource = "Select DISTINCT tblSupply.Supply " & _
"FROM tblSupply " & _
"WHERE tblSupply.Supplier = '" & cboSupplier.Value & "' " & _
"ORDER BY tblSupply.Supply;"
End Sub
Private Sub cboSupply_AfterUpdate()
On Error Resume Next
cboSerial.RowSource = "Select DISTINCT tblSupply.SerialNumber " & _
"FROM tblSupply " & _
"WHERE tblSupply.Supply = '" & cboSupply.Value & "' " & _
"ORDER BY tblSupply.SerialNumber;"
End Sub


HTH