Supply ControlSource through VBA

[Guest]

Is there a reason this code isn't working?

Me!txtAADT1.ControlSource = DLookup("[AADT]", "[VcValueQry2]")

I keep getting an error in my TextBox. Am I not phrasing this correctly in
VBA? The Dlookup doesn't need a Criteria, as it's a one record table. But I
tried it anyways with a Criteria, still getting the same error. Thanks.

 

[Guest]

Try removing the square brackets around your table name:
Me!txtAADT1.ControlSource = DLookup("[AADT]", "VcValueQry2")

 

[Guest]

If you want to assign the value from the Dlookup to the text box, then drop
the ControlSource

Me!txtAADT1 = DLookup("[AADT]", "[VcValueQry2]")

 

[sebt]

Hi

Controlsource is a string property - whatever you set as the
Controlsource is evaluated at runtime (every time you move to a new
record, assuming the text box is in the Detail section - and every time
you do a Form.Reqery) to give the text box a Value.

What I _think_ you're trying to do here is set the text box up so that
whenever the text box is recalculated, from this moment on (or until
you change the Controlsource again in code, if you do), it'll get its
value from the DLookup function.
If this is what you're trying to do, you need to set the Controlsource
to the STRING
"DLookup(""[AADT]"",""[VcValueQry2]"")"
(NB double " ("") must be used within quotes to stand for ")

If, on the other hand, you just want to set the Text box's value to the
result of the DLookup, just now, just once, without it being ever
recalculated, then you should set the
Value property:
Me!txtAADT1.Value = DLookup("[AADT]", "[VcValueQry2]").

The code as you've posted it:

Me!txtAADT1.ControlSource = DLookup("[AADT]", "[VcValueQry2]")

will evaluate the DLookup function at the time this line executes, find
that the result is e.g. "Some words" or 6, and then set the text box's
Controlsource to "Some words" or "6". Then Access will evaluate the
Controlsource to work out the text box's Value. I don't think this
(two evaluations) is what you're meaning to do - unless the DLookup
actually does return a function or expression such as "Now()",
"[NameOfAFieldInTheRecordsource]" etc.

When the COntrolsource is evaluated:
a) If the ControlSource is numeric, (e.g. "6") it'll just set the Text
box to that value.
b) If the Controlsource is nonnumeric, it must be a function, or an
expression referring to a control on an open form or to a field in the
form's Recordsource. I suspect that whatever your DLookup is returning
is nonnumeric, which is why you're getting #ERror# in the text box.

cheers


Seb

 

[Guest]

This is so strange. I tried dropping the control source, I still get the
'#NAME?' error in my textbox. But when I write the Dlookup into the control
source of the textbox properties, it does work? But I can't figure out why
it doesn't work when using VBA to do the exact same thing?

If you want to assign the value from the Dlookup to the text box, then drop
the ControlSource

Me!txtAADT1 = DLookup("[AADT]", "[VcValueQry2]")

--
\\// Live Long and Prosper \\//

Is there a reason this code isn't working?

Me!txtAADT1.ControlSource = DLookup("[AADT]", "[VcValueQry2]")

I keep getting an error in my TextBox. Am I not phrasing this correctly in
VBA? The Dlookup doesn't need a Criteria, as it's a one record table. But I
tried it anyways with a Criteria, still getting the same error. Thanks.

 

[sebt]

What is the result of the Dlookup? Is it text or a number? It sounds
like it must be text. What happens when you set the Controlsource to
the result of the DLookup is that Access tries to understand that value
as a reference to a field, an object or a formula.
For it to work when you put the Dlookup into the Control Source in the
Properties window, you must be putting an "=" in front of it (or maybe
Access does this for you?) - meaning
"to get this text box's value, evaluate this Dlookup function", rather
than "to get this text box's value, look for a field or object
referenced by the phrase "Dlookup( etc etc)" - which is how Access
understands it if you don't put an = in front of it.

If you want to set the Controlsource in code to this DLookup function,
put the "=" in front of it. i.e. .ControlSource="=Dlookup(etc etc)"

cheers


Seb

 

[Guest]

I knew it was something small and seemingly oblivious, but I figured it out.
I had:

Me!txtAADT1 = DLookup("[AADT]", "[VcValueQry2]")

But of course, the Control Source needed an extra = sign. I'll have to
thank Sebt, because he got me into thinking about the = sign. What I needed
instead in the VBA was:

Me!txtAADT1 = "=" & DLookup("[AADT]", "[VcValueQry2]")

I knew it was something stupid! AARRGGHH!!

This is so strange. I tried dropping the control source, I still get the
'#NAME?' error in my textbox. But when I write the Dlookup into the control
source of the textbox properties, it does work? But I can't figure out why
it doesn't work when using VBA to do the exact same thing?

If you want to assign the value from the Dlookup to the text box, then drop
the ControlSource

Me!txtAADT1 = DLookup("[AADT]", "[VcValueQry2]")

--
\\// Live Long and Prosper \\//

Is there a reason this code isn't working?

Me!txtAADT1.ControlSource = DLookup("[AADT]", "[VcValueQry2]")

I keep getting an error in my TextBox. Am I not phrasing this correctly in
VBA? The Dlookup doesn't need a Criteria, as it's a one record table. But I
tried it anyways with a Criteria, still getting the same error. Thanks.