Dynamic database

[TC]

I am in the process of designing a new database to help in my companies
quoting process. On the form that I would like to create I would like to be
able to select from a drop down list a Model type this would then dynamically
set the drop down list for the fields on the form. Example Product A has
Length options of 5’ or 5.5’ Product B has Length options of 7.6’ or 8.1’. If
I select product A from the drop down list I would like to have the drop down
list for Length refer to table with the lengths for Product A. If I delected
B I would like the drop down list to refer to teh table with the Legths for
Product B
This is just in the design stage right now so I do not have sample code to
post.
Thanks
Tim

 

[mscertified]

I would not have different products in different tables.
Otherwise you will need to create a new table when you have new products.
Not a good design. Put them all in one table with columns:
Product Name Product length
When you select the product you dynamically reset the source for the length
drop down boxes to say 'SELECT Len from table where product = selected
product'.

-Dorian

 

[TC]

Dorian,
Right now the products are all in the same table it is the lengths that I
have in different tables. My though for this is this way the user could only
select from the lengths that are appropriate for the selected product. I have
some understanding of SQL but I am not sure how you would dynamically reset
the source for the length drop down.

 

[Jason Lepack]

Dorian,
Right now the products are all in the same table it is the lengths that I
have in different tables. My though for this is this way the user could only
select from the lengths that are appropriate for the selected product. I have
some understanding of SQL but I am not sure how you would dynamically reset
the source for the length drop down.





- Show quoted text -

I created this example:

tbl_product:
productID - AutoNumber - PK
productName - Text

tbl_lengths:
lengthID - AutoNumber - PK
productID - Number - FK
lengthNum - Number - The length of the product

I created a form with two combo boxes

cboProduct:
This combo box has the productID as the key field and displays the
product name in the box.
RowSource - "SELECT tbl_product.productID, tbl_product.productName
FROM tbl_product ORDER BY [productName];"
ColumnCount - 2
ColumnHeads - False
ColumnWidths - 0;1440
BoundColumn - 1

cboLengths:
RowSource - "SELECT lengthID, lengthNum FROM tbl_lengths WHERE
productID=forms!form1!cboProduct ORDER BY lengthNum;"
ColumnCount - 2
ColumnHeads - False
ColumnWidths - 0;1440
BoundColumn - 1

Then in the afterUpdate event of cboProduct put the statement:
cboLength.Requery

Cheers,
Jason Lepack