Continuous Form not displaying query results

[Strike_Eagle]

I have a Form set to continuous. The first textbox on the left properly displays a list of all supervisors. there are 16 text boxes to the right of that.

Each one of those will relate to a query for counting purposes and will display a number, based on the number of results, for the particular supervisor.

For instance, if supervisor 2 has three evaluations waiting a '3' will be displayed, but supervisor 7 may have none, so a blank textbox is displayed.

My problem is that, while my count query correctly displays the number of records for that particular supervisor, when I add it to the continuous form, I get the #Name? error. I have tried all different formats to get it to display correctly, but can't seem to. I even tried reating a query for all 16 boxes as one, but got an error saying the query was too complex.

I have attached the databse for you viewing pleasure. When you open it, the Outstanding evaluations form will display with what I am speaking of.

How can I get this error to show the numbers instead?

Ohh, I am using Access 2007 on a Windows XP machine.

Thank you in advance, I tried searching for an answer, but nothing seemed to quite work.

 

[missinglinq]

I run 2003 and so cannot look at/run your 2007 file, but you cannot use a Query as the Control Source of a Control on a Form.

You need to assign the Control Source for this using the DCount() Function against your underlying Table/Query with the Supervisor being used in the Criteria Clause for the Function.

Linq ;0)>

 

[Strike_Eagle]

Hi, thank you for your response! Here are my two queries:

The First Query, since you can't do distinct, lists every record with a column that counts that row (displaying a 1) For example: OS2 1 OS2 1 OS3 1

SELECT QA_Supervisors.OS_Name, Comments.recordCompleted, Comments.customer_service_comments, QA_NTIDLookup.OS_ID, QA_AFSSLookup.NT_ID, Pm_Data.Specialist, Count(QA_Supervisors.OS_Name) AS CountOfOS_Name
FROM (QA_Supervisors INNER JOIN (QA_NTIDLookup INNER JOIN QA_AFSSLookup ON QA_NTIDLookup.NT_ID=QA_AFSSLookup.NT_ID) ON QA_Supervisors.OS_ID=QA_NTIDLookup.OS_ID) INNER JOIN (Pm_Data INNER JOIN Comments ON Pm_Data.pm_data_id=Comments.comments_id) ON QA_AFSSLookup.Specialist=Pm_Data.Specialist
GROUP BY QA_Supervisors.OS_Name, Comments.recordCompleted, Comments.customer_service_comments, QA_NTIDLookup.OS_ID, QA_AFSSLookup.NT_ID, Pm_Data.Specialist
HAVING (((Comments.recordCompleted)=False) AND ((Comments.customer_service_comments) Is Not Null))
ORDER BY QA_Supervisors.OS_Name;

This query then counts, totals, the rows based on the above query. For example: OS2 2 OS3 1

SELECT qryCSnyd.OS_Name, Count(qryCSnyd.CountOfOS_Name) AS CountOfCountOfOS_Name
FROM qryCSnyd
GROUP BY qryCSnyd.OS_Name
ORDER BY qryCSnyd.OS_Name;

These two queries work fine. I then want the continuous form textbox, which is unbound, to display the 2 on the OS2 row and 1 on the OS3 row.

How do I get DCOUNT to work? I assigned the control source of the textbox to this second query, but all I get is the #NAME? even though my formatting is correct.

 

[Strike_Eagle]

Ok, so after paying around a bit, and trying my hardest to get everything to work properly, I decided to create a whole new database just for experimentation purposes.

The results: Everything works fine. Count queries and subqueries; two seperate queries, DCOUNT function, it wall works great! So I started thinking.....

There are a few things that I cannot create with this database because fo changed data. For instance, when you look at some of my tables, the supervisors name in specific, I cannot do a search that comes up correct because of the limitations of the link and the changes that were made, for propietary information reasons.

THAT has been the entire problem! I created whole new tables and ran the queries just fine in the same database, just pointed at my new tables and all was good.

This is an extremly long winded way of saying, Thank you! The problem is not fixed, but cannot be. If the table structure was a little different, then it could be fixed, but that is for someone else to worry about!