products table

[mikevds]

I have a database of all the following tables
Candy list table
Candy item Number
UPC Number
Item Number
Product Name
Catagory Number
Subcatagory Number
Vendor Number
Distributor Number
Department Number
Picture

Catagory Table
Catagory Number
Catagory Name

Distributor Table
Distributor Number
Distributor Name

Product Table
Product Name (same field as candy list table)


Subcatagory AutoNumber
Subcatagory Name

Vendor Table
Vendor Auto Number
Vendor Name

Now here is what I want the database to do.

On the form it will be set up as a combo box linked to a subform
or two combo boxes linked together

When the user choses for example let's say
Product Name only the products appear associated within that catagory from the catagory Table or the vendor table

Since the lookup wizard messes up things in the table, I want to do it on the form.
Here is where I am confused in order to set up a one to many relationship on the combo box which would be products linked to the catagory subform. Would I need the product name in the products list table or should I take the product list field form the candy list field.

I also want to associate with the product name with the vendor number (in the form to link a catagory list box and a vendor subform) so that's why I made a seperate product name fiield with the autonumber field in the product table as in the candy list table.

Is having two tables with the same tables necesarry, since I want to link the catagory with the subform in a lookup wizard in a form,with the vendor name, associated with that number or would it cause more confusion?

My feeling was I didn't need the products table but I would like to see the products linked as a cross reference in a products combo box on a form with a subform associated with the vendor.

I am thinking now is in the candy list table the products name instead of it
being the same field products name table, in the candy list table should I just
have the product number instead or would that be the wrong way to go?

Which way do you recommend to go?

Now for the coding
MY coding for the products table would be ProductID, ProductDesc FROM tblProducts ORDER BY ProductDesc; for the combo box associated with the catagory
How would I know what coding to do from vendor to get a drop down list associated with each product for a linked subform or a combo box.
I believe it would be the same coding as linking the combo box with the subform.
Is their a website that can fill in the coding aslong I tell what fields to link together to make a drop down boxes cascade.
I had some experience in the past a music db cascading two list boxes it seems different with different fields.

Regards,

 

[jal]

I don't see any need for Products table. Or, rather, the Candy list table should be renamed to "Products" table. This products table (the CandyList table) probably should have an autonumber ProductID column (you called it ItemNumber, but I personally would change the name to ProductID).

How would I know what coding to do from vendor to get a drop down list associated with each product for a linked subform or a combo box.

To populate a combobox:

cboVenders.RowSource = "SELECT VendorName, VendorID FROM Vendors"


Use WHERE clauses as filters. For instance to populate a subform that shows products for the selected vendor:

Me.subform1.Form.RecordSource = "SELECT * FROM Products WHERE VendorID = " & cboVendorID.Column(1)

This assumes that vendorID is the 2nd column of the combo box - the first column would be

cboVendorID.Column(0)


If vendorID is TEXT instead of numeric, add quotes:

Me.subform1.Form.RecordSource = "SELECT * FROM Products WHERE VendorID = '" & cboVendorID.Column(1) & "'"


I

 

[mikevds]

Re: products table coding

Where do I add this in
This assumes that vendorID is the 2nd column of the combo box - the first column would be

cboVendorID.Column(0)

In the vba coding?

Regards