Cascading Combo Boxes

G

Guest

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.
 
G

Guest

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
 
G

Guest

Works perfect. Thank you

SteveS said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cascading Combo Subform Tied to Parent 4
Cascading Combo boxes using CASE command 12
Cascading combo boxes - are they able to do this? 6
cascading combo boxes requery problem 6
Cascading Combo box Code 1
Cascading Combo Boxes in a continuous subform 1
Cascading Combo Box - help needed please. 6
Cascading Combo --- Just Not Happening!! 5

Top