Cascading combobox

[Jeff_Ford]

Cascading combo

I know there is plenty of info out of there on how to do cascading combos, but im still sruggling! (Not just laziness), ive downloded sample databases but i guess i wont understand one until i have one working within my database.

I need one that allows a product to be selected based upon the selection of a supplier. The combo is needed on frmMainForm.

I have a table tbl uppliers with the Supplier ID and Name
I then have a table tbl Product with the Product ID and Product Name against the Supplier ID

Is this the correct structure? Will a cascading combo work from this structure?

Ive attached it, if anyone can help it would be great!

 

[wizcow]

Jeff

To get you started try this.

step 1.
ComboBox1;
Set your controls source to your suppliers table. So it will list all your suppliers.

step 2.
build a query based on your two tables tblSupplier and tblProduct.
Include your Product field and your Supplier field.
In the Supplier fields criterea row enter something like this...
forms![NameOfForm]![ComboBox1]
This makes the query look at your form's combo box to get the Supplier. The query will then display only Products of that supplier.

step 3.
ComboBox2;
Set your control source to the query you just build in step 2.
In the AfterUpdate event of this control you will have to do a
Me.Refresh

Try this link too
CascadingComboBoxes2k.mdb

Good Luck

Tom

 

[Jeff_Ford]

How can i set the control source of the combo to the suppliers table when the record source of the form is set to tbl Batches?

 

[wizcow]

Jeff

You should add the SupplierID to your tblBatches.

I added a form called test, give it a look.

Tom