Text Box ControlSource "apparently" invalid

[GraeagleBill]

In the footing of a continuous form, I have a text box whose ControlSource is:

iif(intNewCatID = 0,"",DLookup("[CategoryName]","tblCategories","CatID = " & intNewCatID))

The form displays with an apparent error:


To view the situation in debug mode, I inserted the code in the OnOpen event with the appropriate stop:


As you can see in the above screenshot, the IIF statement returned the appropriate text string given the current value of intNewCatID.

What am I missing when the IFF is used as the ControlSource property of the textbox?

[pbaldy]

An = at the beginning?

[GraeagleBill]

Fails even when I add brackets:

IIf([intNewCatID] = 0, "", DLookup("[CategoryName]", "tblCategories", "[CatID] = " & [intNewCatID]))

[GraeagleBill]

"=" already there:

[pbaldy]

If intNewCatID is a VBA variable, you can't refer to it from the control source. You'd either have to use a public function that returned the value of the variable or maybe a TempVar.

[GraeagleBill]

That's kind of subtle, to say the least. Anyway, I now see how to fix the issue.
Thanks,
Bill

[pbaldy]

No problem. We've never been able to access VBA variables outside VBA.