DLookUp Referencing in Control on Subform #Name Error

[Pauline123]

Help! I am slowly losing the will to live

I have a control on a subform that uses the DLookUp function to get a numerical value from a query.

When I use the following in the subform it works fine:

=DLookUp("[TotNoDays]","qryHolEntitCalculation","[HPIDNo]=" & [Forms]![HolidayTakenForm]![ HPIDNo])

But when I use the following when the subform is nested in the main form I keep getting the #Name error:

=DLookUp("[TotNoDays]","qryHolEntitCalculation","[HPIDNo]=" & [Forms]![HolidaysMainForm].[Form]![HolidayTakenForm]![HPIDNo])

The subform is linked to the main form via the EmployeeID number.

Have attached example of problem - tried various combinations without success. Heeelllllppppp - Pauline

 

[Ranman256]

ALWAYS use the picker builder....is spells the path correctly...
yours is probably
[Forms]![HolidaysMainForm]![HolidayTakenForm].[Form]![HPIDNo]


form![formname]![subformname]!form![controlName]

 

[sneuberg]

If you check this handy web site you will find this form for the reference in this case

Forms!Mainform!Subform1.Form!ControlName

So if [HolidayTakenForm] is the name of the subform control you would want

Forms![HolidaysMainForm]![HolidayTakenForm].Form![HPIDNo]

You can verify this in the query designer by typing in a criteria field and Intellisense will help you create the reference. It's best to have all forms and subforms open when you do this.

 

[Pauline123]

Hey sorry only just getting back to you guys - your info was great but I realised that all I needed to do was just reference the text box on the sub form and it worked a dream - hate mental blocks lol but thanks for your input very much appreciated - Pauline