Access 2010 Form, Receiving #Name? Error when trying to retreive data from a query

[paulh_36]

Hello, This is my first post. I've been trying to resolve a #Name? error in a calculated field on form I have. The form displays registration information for workshops that my organization provides. For a specific person I have a sub-form that displays all the workshops that a the person has taken. In that sub-form I have a calculated field that should return the total number of registrants for any particular workshop listed that this person has taken. The calculated field references a query that returns only one record, the total number of registration records for that specific "EventID" which it in turns gets from the sub-form. If I bring up the form, or the sub-form by it's self then run the query the query results are fine. The problem is I can't get the query results to display in the calculated field (so the #Name? error). There appear to be no naming conflicts as others have suggested. The underlying expression in the calculated field is "=[qryEventsCount].[CountOfRegistrationID]" (query.fieldname). I was wondering if I had some sort of circular logic going on. Thank so very much for any insights ....

[ranman256]

On the subform, is this entire form connected to the query: qryEventsCount?

If not, then you must do =Dlookup("[CountOfRegistrationID]","qryEventsCount")
to look at a query different from the subform.

You cant put query.field as a calculated field. The function Dlookup does this.

[June7]

As ranman noted, cannot refer to tables and queries directly in textbox expression - table or query must be part of the form's RecordSource or use a domain aggregate expression in textbox to pull data from table or query.

[paulh_36]

Thank you very much ranman & Moderator I have been looking at Dlookup but with no success so far, but I need to explore further as I have never used it before with Access. I will look into the domain aggregate expression.. don't know too much about it though.

[paulh_36]

Ranman & Moderator, Thank you very much for your assistance. After some experimentation I did get Dlookup to work like a charm. Thank you again !!