Newby-Query based on selection - twice

[PF]

I want to create a query that when a particular product is selected from combo
box, it will display the description, then a combo box to select from colors
available, then combo box to select sizes available, and finally, displays the
pricing based on the product number and size selected.

I have 1) a table of product #s with descriptions; 2) another table with
product #s and colors available; and 3) a table with product #s and sizes
available and prices for those sizes.

Thanks for any help.
Phyl

 

[John Nurick]

Hi Phyl,

You can't do this with a query alone: you have to use a form (but don't
bind it to any of your tables). Let's call the form "frmPriceQuery"

Put three comboboxes on your form; call them cboProdNumber, cboColors
and cboSize. Also two textboxes, txtDescription (large enough for the
description) and txtPrice.

The RowSource of cboProdNumber should be a query on the table of product
numbers and descriptions, returning two fields. The first field should
be the product number and the second the product name or some such.

Leave the RowSource of the other two combos empty for now.

In the AfterUpdate event procedure of cboProdNumber, put something like
this VBA code (untested air code). You'll need to alter the table and
field names to fit those you've used (by the way, life is much simpler
in the long run if you never use spaces or special characters in names:
e.g. use "ProductNumber" instead of "Product #").

'Clear the last price
Me.txtPrice.Value = Null
'Get the description
Me.txtDescription.Value = DLookup("Description", _
"tblProdNumDescriptions", _
"ProdNumber = " & Me.cboProdNumber.Value)
'Get the colors
Me.cboColors.RowSource = "SELECT ColorName FROM " _
& "tblProductColors WHERE ProdNumber = " _
& Me.cboProdNumber.Value & " ORDER BY ColorName;"
'Get the sizes
Me.cboSize.RowSource = "SELECT Size FROM " _
& "tblProductSizes WHERE ProdNumber = " _
& Me.cboProdNumber.Value & " ORDER BY Size;"

So when the user selects a product, the description appears and the
other comboboxes are populated with the relevant values.

Next, put something like this in the AfterUpdate event procedure of
cboSize:

'Get the price
Me.TxtPrice.Value = DLookup("Price", "tblProductPrices", _
"(ProdNumber = " & Me.cboProdNumber.Value ") AND (" _
& "Size = """ & Me.cboSize.Value & """)")

NB: I've assumed that ProdNumber is a numeric field and Size is a text
field (see the quote marks in the last DLookup()). If ProdNumber is
actually a text field you'll need to add quotes elsewhere.

 

[PF]

Wow! Thanks for the help, John. I read the manual over and over and over and
while it helps, I have found that getting help from folks like you sure can't
be beat!

Thanks again, I'll get right on it.

Phyl