Query Based on Selection-once more JOhn

[PF]

John,

I added the quotes as you said. Selecting the ProdNumber from a combo box now
adds the description in the proper place and brings up the ProdSizes available
for that ProdNumber. However, when selecting the size, the Price does not show
up . . . it gives me a Compile Error - Syntex Error. I've tried changing
things but to no avail. Here is what I have. Everything works great down to
'Get the price

Private Sub cboProdNumber_AfterUpdate()
'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;"





End Sub

Private Sub cboSize_AfterUpdate()
'Get the price
Me.txtPrice.Value = DLookup("Price", "tblProductPrices", "(ProdNumber="""&
Me.cboProdNumber.Value & """") And ("_ & "Size = """ & Me.cboSize.Value &
""")")
End Sub

Thanks,
Phyl

 

[Dan Artuso]

Hi,
Yes, it's a bit of mess.
Try this:

Me.txtPrice.Value = DLookup("Price", "tblProductPrices", "ProdNumber=""" & _
Me.cboProdNumber & """ And Size = """ & Me.cboSize & """")

 

[PF]

It Works! Thanks, Dan

Phyl

 

[Dan Artuso]

You're welcome