unbound control source returning #NAME?

[tjones]

I have a form with unbound controls that I set the control source as a query =[qryUnitTotalRequired]![TotalCreditsReq] which just returns #NAME?

I have tried this several different ways with no luck. http://www.access-programmers.co.uk/forums/showthread.php?t=233369
the other problem is that this makes my cascading combo boxes (field 2 and 3) unstable for some reason and it drops the information but retaing (field 1 and 4)???

the other ways i tried was a subform (worked but subform does not display) and as a dlookup (no luck there either)

 

[boblarson]

You can't refer to a query like that in a control source. You can use a Dlookup:

=Nz(Dlookup("[TotalCreditsReq]", "qryUnitTotalRequired"), "")

 

[tjones]

Thank You!!!!! That worked perfectly....after 2 days of trying various solutions.

I was at least getting closer with this solution just not quite there as far as the coding went.

 

[tjones]

Bob,

Ran into a small hitch. the formula works great, the problem is that it is not limiting the numbers to the student ID, but is totaling all credits (units) in the table per that status.

I have included a graphic of the query it may be something I set up to do with that. Each query is the same but differ only on the status to what the total is.

 

[tjones]

If you only have one record this works. But when you have more than one record it just gives you the total for the first recore entered. Say Record1 has 3 and record2 has 5. Even before opening record2 and entering the 5 the "3" displays.

=Nz(DLookUp("[TotalCreditsReq]","qryUnitTotalRequired"),"")

I have tried adding the 790# which is what it should seperate them on (total per 790#), but that did not work and just gives me #Error.

=Nz(DLookUp("[TotalCreditsReq]","[790ID]","qryUnitTotalRequired"),"")

This is the query to get the totals.

SELECT Sum(tblCourseTaken.Units) AS TotalCreditsReq, tblCourseTaken.[790ID]
FROM tblCourseTaken
GROUP BY tblCourseTaken.[790ID], tblCourseTaken.CourseStatus
HAVING (((tblCourseTaken.CourseStatus)="Completed-Required"));

 

[boblarson]

You need criteria on the DLookup. So it would be:

==Nz(DLookUp("[TotalCreditsReq]","qryUnitTotalRequired", "[790ID]='" & [790ID] & "'"),"")

That is, assuming that you have the same field name of 790ID on the form.

 

[tjones]

Yes I have the same field name of 790ID.

However it just gives me the error message of:

"The expression you entered contains an invalid syntax

You may have entered a comma without a preceeding value or identifer."

 

[tjones]

Nope got it working. had to remove the extra = at the beginning that I did not notice when I copied it.

Thank you so much.

 

[boblarson]

Sorry, didn't see that I had the extra = sign in there. Glad you got it.

 

[modo]

Thanks to all staff in this forum so so so so so much