I am fairly certain that whatever issue i am having will prove to have been fairly simple, but for the life of me i cannot see what i am doing wrong.

i am a text box on a form that i want to be able to have to display data from one of two different locations depending on a condition on a continuous form. as such i used

=IIf([MtgCode] Is Null,[MortgageName],DLookUp("[MortgageName]","tblMortgageData"," [MtgCode] = '" & [MtgCode] & "'"))

in the control source on the text box itself and it works perfectly. for each record it pulls the proper data and life is happy, but it cannot be edited. so the user hits the edit button and the form changes the control to source to something editable. if they need to they make their changes and the data is saved properly but my problem now comes setting the control source to the above formula for display purposes so they can see their changes immediately. since all of these other changes are happening in vba i have put the final formular in their as well and it seems to have required some changes and this is the vba version

Me.txtMortgageName.ControlSource = "=IIf([MtgCode] Is Null,[MortgageName],DLookup(""[MortgageName]"", ""tblMortgageData"", "" [MtgCode] = '" & [MtgCode] & "'""))"

now the first part of the iif works nicely, its the dlookup that's failing. i dont get an error message but i get a blank answer every time, even though i know it shouldnt be blank. it was also a copy past of the original formula but i apparently needed to add extra " around the various parts.

i removed the dlookup at one point and just put text in its place to make sure that it was at least updating and i got the proper text response telling me that the formula had indeed used the 2nd half of the iff statement, so i know its trying. but something realy small seems to be wrong in the dlookup syntax that i just cannot find.
i also tried to read the edit error when i try and edit the field iwth the formula, hoping to compare the original version and the vba version but it runs too long and i cannot see the last part of it. everything up to " & [MtgCode] matches though.

am i missing something in particular?

thank you very much for any ideas you may have.

Jim

Hi Jim

Did you try renaming the textbox control? Sometimes, Access doesn't like or gets confused about a field in a query, and a control on a form, named the same.

Kim, thank you for the reply. the text box control itself does have a unique name, however. it is now working. i retyped the formula, its the same as the original as i kept a copy of that, but all the sudden its happy? so there's something i am missing somewhere, but its work, and the subsequent fields i needed are working now as well. i am half not convinced since i gave me problems for so long, but i've run a few tests and all show me the data i would expect.

Try using the IsNull() function to test for Null in your IIf instead of Is Null ...

RDH

Rick, thank you, i am going to give that a crack next week, because the problem has crept back up again. its trying to run i know that, but in this test case it just shows a blank. i am wondering if its an update timing thing, but i've tried moving it around to different events and it just doesnt cooperate. so i'll switch to the isnull function and give that a shot.

jim