CODEWITHSUNDEEP
ms-access
Jamsandwich
Jamsandwich

Reputation: 634

MS Access - #Name? Error on DLookup (Textbox on Form, Lookup From Query)

I'm having a bit of an issue with an MS Access database that I'm working on. I'll try to give as much detail as I can. All of my SQL code is what MS Access has generated for me.

First of all, I have a query that tells me how many hours a person is putting into project work -

SELECT Functions.f_Person, Sum(Functions.f_Hrs) AS SumOff_Hrs
FROM Functions
GROUP BY Functions.f_Person;

f_Person is Text in a combo box (linked to e_person in 'Employees' table which is a primary key), f_Hrs & Sumoff_Hrs are numbers. I realise that I am not using best practice for that primary key. This database will be rebuilt after proof of concept.

From this query I have created a second query that takes all of a person's contracted time and subtracts the previous total -

SELECT q_TotalHrs.f_Person, Employees.e_Hrs, q_TotalHrs.SumOff_Hrs, [Employees]![e_Hrs]-[q_TotalHrs]![SumOff_Hrs] AS HrsAvailable
FROM Employees, q_TotalHrs
GROUP BY q_TotalHrs.f_Person, Employees.e_Hrs, q_TotalHrs.SumOff_Hrs, [Employees]![e_Hrs]-[q_TotalHrs]![SumOff_Hrs];

This gives me something along these lines -

Person    Contract Hrs    Sumoff_Hrs    HrsAvailable
----------------------------------------------------
Ali       37              12            25
Al        37              1             36
Andy      37              1             36
Dave      37              13            24 
Gaz       37              1             36
John      37              1             36
Paul      37              6             31
George    37              2             35
Ringo     37              1             36

I also have a form (frm_Projects) which is used for creating new projects which has a subform (frm_Functions) for adding people to these projects. This subform populates the 'Functions' table referenced in the first query.

So the user would add a function (f_Function) and then the number of hours required (f_Hrs) and selects a name from a combo box (f_Person). The subform view is set to 'Continuous Forms'.

Now my problem:

I have created a text box at the end of the input line on the subform which should show the hours available for the selected person. In the text box properties I have added this code into the Control Source -

=DLookUp("[HrsAvailable]","[q_HrsAvailable]","[f_Person] =" & [Forms]![frm_Functions]![f_Person])

This gives me the #Name? error on the form view.

I realise that this is a common problem, but can't seem to solve it on my own database using solutions that I've found. Could somebody help me to fix this?

----------- Edit ----------------

New formula as provided by @Andre

=DLookUp("[HrsAvailable]","[q_HrsAvailable]","[f_Person] =" & [Forms]![frm_Projects]![frm_Functions].[Form]![f_Person])

Upvotes: 1

Views: 1515

Answers (1)

Andre
Andre

Reputation: 27634

I'd say you need 

Forms!frm_Projects!frm_Functions.Form!f_Person

assuming that the subform control (!) on frm_Projects has the same name as its source form, i.e. frm_Functions. That's normally the case, but not mandatory.

Edit

If you still have the combobox name change, it's 

Forms!frm_Projects!frm_Functions.Form!cbx_Person

and since I now realize that f_person is a string, you need single quotes around the parameter:

=DLookUp("[HrsAvailable]", "[q_HrsAvailable]", 
         "[f_Person] = '" & [Forms]![frm_Projects]![frm_Functions].[Form]![f_Person] & "'")

Upvotes: 1

Related Questions