Using Dcount function on subform (1 Viewer)

grenee · May 31, 2015

Good Day all.

My mainform is "CourseDays" and subform is "CoursesSubForm". The "CoursesSubForm" data source is a table "Courses" which contains a field "Subject".

I add a calculated field on the mainform to count the number of records in the subform. This is my code:

=DCount(”[Subject]”,”[CoursesSubForm]”)
An error message results.
Can anyone explain my problem?
Thanks in advance.

Uncle Gizmo · May 31, 2015

What was the error message?

grenee · May 31, 2015

Thanks for you swift intervention.

My error message is simple "#Error

vbaInet · May 31, 2015

grenee said:
My mainform is "CourseDays" and subform is "CoursesSubForm".

=DCount(”[Subject]”,”[CoursesSubForm]”)

This is the format of a DCount() function:

Code:

DCount("[Field]", "[[COLOR="red"][B]Table/Query[/B][/COLOR]]", "Criteria")

As you can see, the second parameter only takes the name of a table or a query and not that of a subform.

If you want to count the number of Subject records there are in the subform and return/display it in your main form, then:
1. place a textbox in the header or footer of your subform. Call it txtCountSubjects
2. enter the expression =Count("[Subject]") into the Control Source
3. place a textbox in the header/footer of your main form
4. in its Control Source use the Expression Builder to locate the txtCountSubjects textbox and the expression should look something like this:

Code:

=[B][COLOR="Blue"]SubformControlName[/COLOR][/B]!txtCountSubjects

OR

=[B][COLOR="blue"]SubformControlName[/COLOR][/B].Form!txtCountSubjects

... note that I'm using a Count function here, not a DCount.

grenee · May 31, 2015

Thanks VBAInet.
That was a wonderful Sunday morning treat.

All's well. I can get going now.

vbaInet · May 31, 2015

It can't be better than a good Sunday roast... but you said "morning" so you must be in another part of the equator from me

murray83 · Jun 17, 2018

vbaInet said:
This is the format of a DCount() function:

Code:

DCount("[Field]", "[[COLOR="red"][B]Table/Query[/B][/COLOR]]", "Criteria")

As you can see, the second parameter only takes the name of a table or a query and not that of a subform.

If you want to count the number of Subject records there are in the subform and return/display it in your main form, then:
1. place a textbox in the header or footer of your subform. Call it txtCountSubjects
2. enter the expression =Count("[Subject]") into the Control Source
3. place a textbox in the header/footer of your main form
4. in its Control Source use the Expression Builder to locate the txtCountSubjects textbox and the expression should look something like this:

Code:

=[B][COLOR="Blue"]SubformControlName[/COLOR][/B]!txtCountSubjects OR =[B][COLOR="blue"]SubformControlName[/COLOR][/B].Form!txtCountSubjects

... note that I'm using a Count function here, not a DCount.

this is exactly what i was looking for and very simply laid out and explained ( which helped ) cheers

Using Dcount function on subform (1 Viewer)

grenee

Registered User.

Uncle Gizmo

Nifty Access Guy

grenee

Registered User.

vbaInet

AWF VIP

grenee

Registered User.

vbaInet

AWF VIP

murray83

Games Collector

Similar threads

Users who are viewing this thread