Drop down & List Box

[Court]

NEW TO ACCESS!!! PLEASE HELP
Hi I am doing a form for work - somewhat like a PO.

I have 2 Tables

Suppliers(Table 1)
SupplierID SupplierName
BOE Boeh
INT Invet
PFZ Pfiz

Products(Table2)
ProductCode Product SupplierID
1234 Vitamins BOE
2345 Shots BOE
3456 Liquids INT
4567 Solids PFZ

On the form:
I have a Supplier Dropdown list where the clients would choose a supplier.

Once Client has chosen a Supplier, I would like all the products to show up in the list box so they can choose a product.

So if Boeh was choosen
1234 Vitamins BOE
2345 Shots BOE
would show in the listbox below.

How do I make the relationship between the two work?

Please HELP?

 

[pbaldy]

The concept is called cascading combos. I have a little sample here:

http://www.baldyweb.com/CascadingCombo.htm

There wouldn't be any difference having the second control be a listbox instead of a combo.

 

[Court]

So I have my first combo box set up..

PrivateSub cboCountry_AfterUpdate()
On Error Resume Next
SelectCase cboCountry.Value
Case "France"
cboCity.RowSource = "tblFrance"
Case "United Kingdom"
cboCity.RowSource = "tblUnitedKingdom"
Case "United States"
cboCity.RowSource = "tblUnitedStates"
End Select
End Sub

What code do I put for the second combo box? For the Select Statement since I am using multiple tables.? It is only selecting one table.

 

[pbaldy]

The formatting on that post got messed up, and the code is hard to read. Also, your first post said you just had the 2 tables. Is this a different problem?

 

[Court]

Well I was looking at a different way of doing it because nothing was working!!

PLus the inventory is easier to manage by splitting into seperate tables. I was looking at thhis website http://www.fontstuff.com/access/acctut10.htm and was looking at the Multiple row source table and I understand the code and what not but the second combox box I just dont know what to code or wat select statement to use because it needs choose from a particular table depending on the supplier they choose.

So there is the
Suppliers(Table 1)
SupplierID SupplierName
BOE Boeh
INT Invet
PFZ Pfiz

But now there is a seperate table that holds all the products of each supplier
Boeh Table 2
2345 Shots
1234 Vitamins
Inter table 3
3456 Liquids
Pfiz table 4
4567 Solids

 

[pbaldy]

I don't like their design with the separate tables for each country, and I feel the same way about separate tables for each supplier. You don't want to have to add a table and everything else when you get a new supplier.