DLookup as ControlSource through VBA code

[brunopg7]

Below is my code:

Forms![OrderForm]![Sku].ControlSource =
"=DLookUp(""[Sku]"",""OrderTable"",
""ItemNumber = '" & Forms![OrderForm]![ItemNumber] & "')"

I run the above code and the control source is now:

=DLookUp("[Sku]","OrderTable",
"ItemNumber = '1234-BA')

How do I change my code so I see the below in my control source:

=DLookUp("[Sku]","OrderTable",
"ItemNumber = '" & Forms![OrderForm]![ItemNumber] & "')

 

[josephff]

it is the same thing i believe...when you run code it shows you the actual value from Forms![OrderForm]![ItemNumber] instead.

so if you changed the value to itemnumber to 2222-BA, and ran it, this would show up

=DLookUp("[Sku]","OrderTable",
"ItemNumber = '2222-BA')

is it not doing what it supposed to or are you just concerned with what its showing in the controlsource?

 

[brunopg7]

it is the same thing i believe...when you run code it shows you the actual value from Forms![OrderForm]![ItemNumber] instead.

so if you changed the value to itemnumber to 2222-BA, and ran it, this would show up

=DLookUp("[Sku]","OrderTable",
"ItemNumber = '2222-BA')

is it not doing what it supposed to or are you just concerned with what its showing in the controlsource?

The issue is that it is the control source for a field in a datasheet.
When I set the control source manually

=DLookUp("[Sku]","OrderTable",
"ItemNumber = '" & Forms![OrderForm]![ItemNumber] & "')

it works, but if I set it through VBA it doesn't do it on an itemNumber per ItemNumber basis.

 

[josephff]

check your quotation marks on

Forms![OrderForm]![Sku].ControlSource = 
"=DLookUp(""[Sku]"",""OrderTable"",
""ItemNumber = '" & Forms![OrderForm]![ItemNumber] & "')"

you've cancelled out all the quotations except for

'" & Forms![OrderForm]![ItemNumber] & "'

try cancelling them out and see if that works

 

[josephff]

Forms![OrderForm]![Sku].ControlSource = 
"=DLookUp(""[Sku]"",""OrderTable"",
""ItemNumber = """" & Forms![OrderForm]![ItemNumber] & """""")"

try this

 

[brunopg7]

Forms![OrderForm]![Sku].ControlSource = 
"=DLookUp(""[Sku]"",""OrderTable"",
""ItemNumber = """" & Forms![OrderForm]![ItemNumber] & """""")"

try this

Ok I tried your code and got this as the control source:

  =DLookUp("[Sku]","OrderTable",
"ItemNumber = "" & Forms![OrderForm]![ItemNumber] & """)

 

[brunopg7]

This worked!

"=DLookUp(""Sku"",""OrderTable"",""ItemNumber='"" & "Forms![OrderForm]![ItemNumber]" & ""'"")"

 

[josephff]

glad you got it working, those single and double quotation marks can be a pain sometimes lol

 

[brunopg7]

yeah its a good feeling to get it to work...!