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.