Count records on subform

[Tanya]

Hi I am trying to count the number of records in a subform where the value of the field [work] is "None", "Poor", "Good", ... etc

I wondered if DCount would work but can't get it right.

Note: I am trying to calculate this in the header of the sum form and pick up the value in the main form.

Could someone please point me in the right direction?

Cheers
Tanya

 

[John Big Booty]

Check this link for the correct Syntax for DCount, if your Criteria is a string the syntax is DCount("FieldName" , "TableName" , "Criteria= 'string'") note the string expression is enclosed by apostrophes, whist the whole of the criteria, including the string is enclosed in quotation marks.

To refer to your filed on the sub form check this link

 

[Tanya]

Hi John
Thank you for this post.
I have looked at the links you gave me and played with the recordsource but with no joy.

These are just a couple of the combinations I have tried...

=DCount("[Work]","Me!SubfrmAttComments","[Work]='Good'")
=DCount("[Work]","Me!RecordSource","[Work]='Good'")

The expression I am trying to create is in the header of the 2nd subform named SubfrmAttComments

I was aware of the DCount expression when looking at tables, not sure however if this is going to be possible looking at a form... Perhaps I should be looking at the query behind the subform? >>> Tried this, without success!

cheers
tanya

 

[John Big Booty]

Your Dcount should look something like this

=DCount("[Work]","TBL_YourTblName","[Work]='Good'")

Above you said this was to be in the header of your Sub Form.

Then on your main form use

Me!Subform1.Form!ControlName

to pick the result

 

[Tanya]

Hi John
Please excuse my ignorance, but I don't follow with the code for main form, where do I place the line of code? i.e. do I place it under the event 'On Open'???

Also the control name, is this the field name or name of the 2nd subform?

kind regards
Tanya

 

[John Big Booty]

On your subform put an unbound text box and call it something like TBX_Count, then in it's Control Source put

=DCount("[Work]","TBL_YourTblName","[Work]='Good'")

Now on the main form put another unbound text box and in it's Control Source put

=Me!Subform1.Form!TBX_Count

where Subform1 is the name of your Sub Form.

 

[Tanya]

Thank you for your patience.

This is what I have

Sub form 2 Named subfrmAttComments
text field named 'GoodTotal' and following statement

=DCount("[Work]","tblAttendance","[Work]='Good'")

Sub form 1 Named frmAtt
text field named 'txtGood'
=Me!SubfrmAttComments.Form!GoodTotal

The error I am getting is #Name? So it looks like it can't find something.

Any thoughts?

 

[John Big Booty]

Is GoodTotal returning a proper value or an error?

 

[Tanya]

Hi
GoodTotal appears to be displaying the correct value, however it doesn't help me because the 2nd subform is related to another subform, i.e. select student and subfrm 2 relates to record in other.
I have since decided to try a new tack with this problem.
This is what I eventually want to achieve....
My main form has tabs... one of which is for attendance [see jpg file]
You will notice in the jpg image that I have % for days students did not have equipment or book and I wish to do the same with attendance [Att] and participation [work]
In this thread I have been attempting to get a count of 'good' under the field [work] for the given student.
Now I am exploring running a query whereby I can take the total counts directly.
I hope this makes more sense?
Thank you for your patience.
Regards
Tanya

 

[John Big Booty]

If GoodTotal is returning the correct result it sound as if you just have to sort out the syntax for referencing that in the field on the main form.

Certainly us the count option of a Sum query will also achieve what you want.

P.S. It would make more sense to post a version of your DB rather than an excel table.

 

[Tanya]

Hi
I have a attached a copy of my db, hopefully it will all make a lot more sense now.

Cheers
Tanya

PS. This is not a school assignment! I am a school teacher and this db is intended for my own use.

 

[John Big Booty]

Did you realise that Dcount will simply count ALL the occurrences of "Work" in the record set and not just the ones specific to a particular student? So you may need to rethink your approach.

 

[Tanya]

hi John
I realised that in its present format based on tblAttendance it would not allow me to define records to count and this was ultimately my question. Is it possible to use Dcount on results of form rather than a table.
Can you suggest a better method? I would really appreciate any ideas here.
cheers
Tanya

 

[John Big Booty]

Dcount will only work on tables or queries.

You could use a Totals query and group and count the Work field. and then use that in one of you queries linked by the student ID.

 

[Tanya]

Hi John
I am having problems creating a Totals Query. No problem counting for one though. i.e. I created a query to count Good from work field and that works fine. As soon as I try to create a query which brings in all the counts of various performances I get into strife.
Here is my current query named qryWorkGood

SELECT qryAttendances.StudentClassID, qryAttendances.Work, Count(qryAttendances.Work) AS CountOfWork
FROM qryAttendances
GROUP BY qryAttendances.StudentClassID, qryAttendances.Work
HAVING (((qryAttendances.StudentClassID)=[Forms]![frmClasses]![frmAtt]![SubfrmAttClassList].[Form]![StudentClassID]) AND ((qryAttendances.Work)="Good"))
ORDER BY qryAttendances.Work;

You will notice I have added having which refers to SubfrmAttClassList thereby allowing me to relate to certain students by selection.

Any suggestions?

cheers
Tanya

 

[John Big Booty]

Have a look at the structure of Query3

Also try and avoid using symbols like "&" in object names.

 

[Tanya]

Magic!
Thank you John. I have been stumped on these queries for days!

Now.. query3 will allow me to get a count for None, Poor, Good, Ave, Exc but how do I get these totals into text boxes on my form frmAtt? In the copy you sent me, the total of good for instance is correct for the class but not for the student?

Kind regards
Tanya

PS: Thank you for the advise on using & in names of objects, I will look at this next.

 

[John Big Booty]

Sorry I thought you want a student specific result rather than a class total.


You can use

=DCount("Work","qryAttendances","Work='Good'")

Have a look at your original form.

 

[Tanya]

I do want a specific student result!

 

[John Big Booty]

Sorry my bad. I mis-read your previous post

The results for Query3 appear to be correct for the data in tblAttendance.

Have a look at your Original form again. I think I got it doing what you are after.