Use a Combo list value to activate/ provide criteria for a Dlookup

[Jonny123]

hey, im a beginner so im not sure what kind of info is needed to help me, so ..

Textbox1 (Combobox listed Single Door; Double Door etc from a table.)
Textbox2 (Text Box, manually entered a number.)
Textbox3 - THE PROBLEM.

I want Textbox3 to use Textbox1's value (or text to be accurate)
To look up a value located in the same table as it.

Dlookup("[Doorvalue]","[Doortbl]","[Textbox1]")

But my problem is I cant specify what value is used, only the first will show.


Doortype______Value______DoorID
Single Door________4____________1
Double Door_______6____________2
Double/s Door_____15____________3


If any more info is needed let me know.

Most importantly, Thank you.

EDIT!

If Textbox1 = "Single Door" Then
Textbox3.value=Dlookup("[Doorvalue]","[Doortbl]","[Textbox1]")

Somthing like that if its possible.

[CreganTur]

Welcome to the forum- always good to see new members.

It seems to me that the issue is you're not referencing the value of textbox1 correctly in the DLookup function and you're not using the Criteria parameter correctly. Try this:

[vba]Me.Textbox3 = DLookup("Doorvalue", "Doortbl", "FieldName = '" & Me.Textbox1 & "'")[/vba]

In VBA code when you want to refer to an object on the currently active Form, you will need to use the 'Me.' keyword. In the Criteria parameter of the Dlookup function you have to reference the field in the table that you want to use to find the referenced textbox value. Replace 'FieldName' with the name of the actual field in your table. It's a lot like a SQL WHERE clause. You have to use the single quotes to tell dlookup that the value of the referenced textbox is a string data type. If you leave them off the function will fail.

HTH

[andysuth]

Is that you Steve?

try here:

http://www.vbaexpress.com/forum/showthread.php?t=22298

[vba]

stg2 = DLookup("DOORCODE", "DoorDesign", "DOORDESCRIPTION= '" & Me.door_type & "'")

[/vba]

-AS