
Help filling second combo box from fist combo box
I have two combo boxes, one for customers and the other for locations. I
want the location combo box to change depending on the customer selected.
This works initially, however if I change the customer I get the previous
cust. locations plus the newly selected customers locations.
Is there a way to prevent this from happening, so that I only get the
locations for the currently selected customer?
This code executes when the form is loaded:
Dim crst As Recordset
Dim custr As String
'selects records from table
custr = "SELECT DISTINCT customer FROM tblcust"
' binds sql results to recordset object
Set crst = dbs.OpenRecordset(custr)
'fills combo box with values from recordset
Do While crst.EOF = False
cmbCustomer.AddItem crst(0)
crst.MoveNext
Loop
This code selects the location based on the customer selected:
Dim lrst As Recordset
Set dbs = DBEngine.Workspaces(0).OpenDatabase("C:\my
documents\vb\Usedparts\part.mdb")
Set lrst = dbs.OpenRecordset("SELECT DISTINCT location FROM tblcust " _
& "WHERE customer = '" & [cmbCustomer] & "'")
'fills combo box with values from recordset
Do While lrst.EOF = False
cmbCLocation.AddItem lrst(0)
lrst.MoveNext
Loop
Thanks for the help.
-----
James McKane