I need to be able to display the results of a query in a control on a form... I have a master form with two subforms (employeeinfo and traininginfo). I have a query that will tell me how many employees have enrolled in a certain training course. Because these courses have a maximum capacity I want to be able to show how many people are currently enrolled.
I was thinking I needed to do something like this for the control sourse...

[Training Course Query]![Count Of EmployeeInfo] WHERE [Forms]![Master]![CourseTitle] And [StartDate]=[Forms]![Master]![StartDate]

I've linked the Master table fields (coursetitle and startdate) to the respective fields in the query and still haven't been able to get it to work.

Does anybody have any ideas?


Edited by: surenecker on Fri Jun 8 12:27:35 EDT 2007.

Edited by: surenecker on Fri Jun 8 12:27:57 EDT 2007.

Your WHERE clause is incorrect: you don't have a field name to compare to [Forms]![Master]![CourseTitle]. However, you can't set a text box's control source to a SQL statement.

Try using DLookup (or even DCount).

Continuing with Doug's suggestion:

If you are just doing a count of records you can use the DCOUNT() function as the control source. The general syntax is DCOUNT("fieldtocount","tableOrQuery","criteria")

If you want to reference form controls as the criteria:

DCOUNT ("fieldtocount","tableOrQuery", "courseTitlefieldoftable='" & forms.formname.nameofcontrolholdingcoursetitle & "' and startdatefieldoftable=#" & forms.formname.nameofcontrolholdingstartdate & "#")

Text fields have to be bounded by single quotes '; date fields by # signs. numeric fields do not require any additional syntax

Thanks! I'm fairly new at Access and didn't know that I couldn't use an SQL statement in the control source.

Your help is greatly appreciated!

You are welcome. Post back if you have trouble with the DCOUNT() function.

ok, so I tried this:

=DLookUp(CountOfEmployeeInfo,trainingcourse,'CourseTitle=''& Forms.master.coursetitle &' And "startdate=# & Forms.master.startdate & #")

Does that look right? It didn't work, but my problem could lie elsewhere.

Edited by: surenecker on Fri Jun 8 13:47:22 EDT 2007.

Edited by: surenecker on Fri Jun 8 14:12:32 EDT 2007.

The syntax is a little tricky; I marked a few things in red; see if this helps.

=DLookUp("CountOfEmployeeInfo" , " trainingcourse" , "CourseTitle=' '' & Forms.master.coursetitle & "' And startdate= #" & Forms.master.startdate & "#")

I keep getting the message: The expression you have entered has an invalid string.

I've checked over everything that I can think of to make sure there are no typos, etc... does it make a difference that CountOfEmployeeInfo is an expression in the query?

I didn't even catch that you were using Dlookup. You can count directly with the DCOUNT function on the table using the values in textboxes on your form as criteria.

You can use the dlookup on an expression of a query, so that is not the issue.

The trainingcourse query that does the count, must also have the fields called courseTitle and startdate. Does it?

Is startdate of the date/time datatype or is it text?

Can you zip and post your DB with any confidential data removed, so we can take a closer look to see what is going on?

Yes, the query does have both the CourseTitle and StartDate. The startdate is of the date/time datatype.
I attached the DB to the previous post I made.

Should I be using DCount instead of DLookUp? Does it make a difference?

Since you already had the query built, you can use it. I got the dlookup working; the DB is attached.

Thank you so much! I can't even begin to express how much you've helped. I'm so glad that there are resources, and especially people like you, to help.

Thanks again. :-)