Dlookup and Comboboxes

[Smithsat34]

Hi Board,

I have a form that recalls a product price from a table (tblFurniture), using a dlookup.

As follows:

UnitPrice: DLookUp("[1stBand]","tblFurniture","[ProductNameText]='" & [Element] & "'")

This works fine, but I want to return the price based on the selected band (1st,2nd,3rd etc) from my combobox ([cbo_SpecifyBanding])


Appreciate any assistance

Regards

bob

 

[ranman256]

Instead of Dlookup, use a query in the combo box that returns many columns.
when user picks the item, grab whatever value you want from a column.
(note: in VB, columns begin with zero)

txtPrice= cboBox.column(1) 'Column2

 

[Smithsat34]

Ranman,

Thanks for taking the time to respond, not sure if your solution is going to give me the result I'm after due to the way the base data is arranged in the tblFurniture. In it I have a column containing the product name, there is a price for each product in the 1st band column, the 2nd band relates to a different 'finish' to the product and this higher price is contained in the third column, and so on.
So the banding relates to an 'offset' or hlookup if this was in Excel.
I'll look at your suggestion in the meantime..

thanks
bob

 

[welshgasman]

It appears your DB is not normalised.?
You would have a field for the band and multiple records for each product/band.

Then it would be a simple

UnitPrice: DLookUp("[Price]","tblFurniture","[ProductNameText]='" & [Element] & "' & [Band]='" & [cbo_SpecifyBanding] & "'")

You might try (not tested) if the combo holds the field name?

UnitPrice: DLookUp(EVAL([cbo_SpecifyBanding]),"tblFurniture","[ProductNameText]='" & [Element] & "'")

 

[Smithsat34]

WelshGasMan

Thanks for looking at the query, I took onboard your comment to normalize the table, so took the time to do this, I am now getting a syntax error with the following, note that some of the field names have changed following my original post..

Me.UnitPrice = DLookup("[Price]", "tblFurnitureNormalized", "[Element]='" & [cbo_AddElement] & "' & "[Band]='" & [cbo_SpecifyBanding] & "'")

I have tried quite a few combinations here of '" , but I'm not sure of the logic which drives this so its just stabbing in the dark!

It was working until I added [Band] and [cbo_SpecifyBanding]

Your advices - much appreciated

Regards,

bob

 

[welshgasman]

Use single quotes if the control has text, none if numeric.?
So if the cbo_SpecifyBanding is just the numeric key it as it would be in my system, then it would be

Me.UnitPrice = DLookup("[Price]", "tblFurnitureNormalized", "[Element]='" & [cbo_AddElement] & "' & "[Band]=" & [cbo_SpecifyBanding])

A way to debug it, is declare a string and build that, then use in the function. That way you can debug.print and see exactly what it contains?

Dim strParams as String
strParams = "[Element]='" & [cbo_AddElement] & "' & "[Band]='" & [cbo_SpecifyBanding] & "'"
debug.print strParams
Me.UnitPrice = DLookup("[Price]", "tblFurnitureNormalized", strParams)

HTH

WelshGasMan

Thanks for looking at the query, I took onboard your comment to normalize the table, so took the time to do this, I am now getting a syntax error with the following, note that some of the field names have changed following my original post..

Me.UnitPrice = DLookup("[Price]", "tblFurnitureNormalized", "[Element]='" & [cbo_AddElement] & "' & "[Band]='" & [cbo_SpecifyBanding] )

I have tried quite a few combinations here of '" , but I'm not sure of the logic which drives this so its just stabbing in the dark!

It was working until I added [Band] and [cbo_SpecifyBanding]

Your advices - much appreciated

Regards,

bob

 

[stumac]

If using multiple criteria in a DLookup then you will require 'AND':

[COLOR=#574123]DLookup("[Price]", "tblFurnitureNormalized", "[Element]='" & [cbo_AddElement] & "' AND [Band]='" & [cbo_SpecifyBanding] & "'")[/COLOR]

I much prefer the option of adding hidden fields to the combo box and referencing a text box to that it tends to work much faster.

 

[Smithsat34]

Stumac, WelshGasMan

Thanks very much, I was starting to doubt my sanity! All comments taken on board..

bob

 

[stumac]

No problem - remember if using via VBA you may need to include the update in various events - the onchange for both combos and also the oncurrent for the form. You may need to add in some validation checks first too.

It can be used as a text box expression (prefix with = ), however, if either fields are blank it will show #Error

 

[welshgasman]

Oops, sorry Smithsat34, my bad

If using multiple criteria in a DLookup then you will require 'AND':

[COLOR=#574123]DLookup("[Price]", "tblFurnitureNormalized", "[Element]='" & [cbo_AddElement] & "' AND [Band]='" & [cbo_SpecifyBanding] & "'")[/COLOR]

I much prefer the option of adding hidden fields to the combo box and referencing a text box to that it tends to work much faster.