Dlookup and Comboboxes

Smithsat34

Board Regular
Joined
Jan 20, 2014
Messages
54
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
Rich (BB code):
UnitPrice: DLookUp("[Price]","tblFurniture","[ProductNameText]='" & [Element] & "' & [Band]='" & [cbo_SpecifyBanding] & "'")

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

Rich (BB code):
UnitPrice: DLookUp(EVAL([cbo_SpecifyBanding]),"tblFurniture","[ProductNameText]='" & [Element] & "'")
 
Upvote 0
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
 
Upvote 0
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
Code:
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?
Code:
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..
Code:
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
 
Upvote 0
If using multiple criteria in a DLookup then you will require 'AND':

Code:
[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.
 
Upvote 0
Stumac, WelshGasMan

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

bob
 
Upvote 0
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
 
Upvote 0
Oops, sorry Smithsat34, my bad :oops:

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

Code:
[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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top