counting certain elements of a subform?

[Jerome]

Hi,

I've got the following situation (using Access 2003):

A form containing the data of a given activity and a subform with the
data of the participants.

The participants have different attributes ('accepted', 'waiting list',
'refused').

What I now need on my form is to have the exact number of accepted
participants (based on the subform) or, if there are no accepted
participants yet, I need to display '0' instead of just an error message.

With Access 2000 I used separate queries with IIFs,
RecordSetClone.RecordCount and the like, but importing this gets me an
error in Access 2003.

So what's the best way to address this?

Thanks a lot,

Jerome

 

[Graham R Seach]

Jerome,

You need to do the count against the table that the subform is based on. For
example, if the subform is based on a table called, let's say, tblMyTable,
and the main form's primary key is, say PrimaryKeyID, then the following
query will give you an idea of how to do it:

intCount = DCount("[Attribute]", "tblMyTable", _
"[PrimaryKeyID] = " & Me!txtPrimaryKeyID & _
" AND [Attribute] = ''accepted")

The above assumes your code is running from the main form, and that the
LinkMasterField and LinkChildField is "PrimaryKeyID".

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Marshall Barton]

I've got the following situation (using Access 2003):

A form containing the data of a given activity and a subform with the
data of the participants.

The participants have different attributes ('accepted', 'waiting list',
'refused').

What I now need on my form is to have the exact number of accepted
participants (based on the subform) or, if there are no accepted
participants yet, I need to display '0' instead of just an error message.

With Access 2000 I used separate queries with IIFs,
RecordSetClone.RecordCount and the like, but importing this gets me an
error in Access 2003.

Can't you just use a text box in the subform footer section:

=Sum(IIf(attributes = "Accepted", 1, 0)

 

[Jerome]

Thanks, that works fine!

Jerome,

You need to do the count against the table that the subform is based on. For
example, if the subform is based on a table called, let's say, tblMyTable,
and the main form's primary key is, say PrimaryKeyID, then the following
query will give you an idea of how to do it:

intCount = DCount("[Attribute]", "tblMyTable", _
"[PrimaryKeyID] = " & Me!txtPrimaryKeyID & _
" AND [Attribute] = ''accepted")

The above assumes your code is running from the main form, and that the
LinkMasterField and LinkChildField is "PrimaryKeyID".

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi,

I've got the following situation (using Access 2003):

A form containing the data of a given activity and a subform with the data
of the participants.

The participants have different attributes ('accepted', 'waiting list',
'refused').

What I now need on my form is to have the exact number of accepted
participants (based on the subform) or, if there are no accepted
participants yet, I need to display '0' instead of just an error message.

With Access 2000 I used separate queries with IIFs,
RecordSetClone.RecordCount and the like, but importing this gets me an
error in Access 2003.

So what's the best way to address this?

Thanks a lot,

Jerome

 

[Jerome]

That works as well indeed! Thanks.

Can't you just use a text box in the subform footer section:

=Sum(IIf(attributes = "Accepted", 1, 0)