counting certain elements of a subform?

  • Thread starter Thread starter Jerome
  • Start date Start date
J

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
 
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
 
Jerome said:
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.
Click to expand...


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

=Sum(IIf(attributes = "Accepted", 1, 0)
 
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
---------------------------

Jerome said:
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
Click to expand...
Click to expand...
 
Back
Top