Help filling second combo box from fist combo box 
Author Message
 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



Sat, 15 Jan 2000 03:00:00 GMT  
 Help filling second combo box from fist combo box



Quote:
> 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

What's happening here is that when you select customer A, your code adds
customer A's locations to the combo box, and then when you select customer
B, it adds customer B's locations to it.  You need to clear the location
combo box immediately before you start adding locations to it.

Bob Rossney



Sun, 16 Jan 2000 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Help filling second combo box from first combo box

2. Filling combo box text box area from data

3. enter data in a combo box based on value from another combo box

4. Limit subform combo box by selection in parent combo box

5. Want Access combo box to filter rowsource of another combo box

6. Populating a combo box from another combo box

7. Populate a combo box from another combo box selection

8. selecting records for 2nd combo box dependant on first combo box

9. Combo box selection narrows next combo box choices

10. load 2nd combo box - depend on first combo box

11. Setting Combo box ListIndex initiates Combo box Click event

12. combo box/database combo box vlaue question - newbie

 

 
Powered by phpBB® Forum Software