Error in Dlookup formula

[Tony Williams]

I have a form (frmmain) which has a subform (frmInput) and 3 controls on the
subform.
Control 1 is a drop down combo box based on a table (not the same one as the
form)
Control source of Control 2 is Control1.column(2)
Control source of Control 3 is
=DLookUp("[ADDRESS]","[Codes]","[Unitname]=Forms![frmmain]![frmInput].Form![control2]

However I'm getting #Error in Control 3
Can anyone help?
Thanks
Tony

 

[Allen Browne]

Some alternative expressions for the 3rd argument to try, Tony:
"[Unitname]=[control2]"
"[Unitname]=Control1.column(2)"
"[Unitname] = """ & Control1.column(2) & """"

If Unitname is a Number field (not a Text field), drop the quotes and handle
the Null case:
"[Unitname] = " & Nz(Control1.column(2),0)

If the subform has no records and no new records can be added, it goes blank
and these expressions will error.

 

[Tony Williams]

Thanks Allen used the second suggestion
Tony

Some alternative expressions for the 3rd argument to try, Tony:
"[Unitname]=[control2]"
"[Unitname]=Control1.column(2)"
"[Unitname] = """ & Control1.column(2) & """"

If Unitname is a Number field (not a Text field), drop the quotes and handle
the Null case:
"[Unitname] = " & Nz(Control1.column(2),0)

If the subform has no records and no new records can be added, it goes blank
and these expressions will error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form (frmmain) which has a subform (frmInput) and 3 controls on
the
subform.
Control 1 is a drop down combo box based on a table (not the same one as
the
form)
Control source of Control 2 is Control1.column(2)
Control source of Control 3 is
=DLookUp("[ADDRESS]","[Codes]","[Unitname]=Forms![frmmain]![frmInput].Form![control2]

However I'm getting #Error in Control 3
Can anyone help?
Thanks
Tony