COUNT Fields from Subform in Main Form

[PolQueen]

I am trying to count the number of parcels in a field called [ParcelQty] in
my main form [Field Packs] from a field called [SBLPID] in my subform
[Query.QrySBLFP] (Note: the table name is SBL FP). The Master and Child
fields are FPackID. I used an example from the Help index and came up with
this formula:

=ParcelQty: (SELECT COUNT([SBLFPID]) FROM [SBL FP] WHERE [SBL FP].[FPackID]
= [Field Packs].[FPackID])

It does not work in the form (I tried with and without the begining equal
sign), so I tried leaving the form field unbound and using the ParcelQty
field in QryFieldPacks. I get this error message: “The expression you have
entered has an invalid .(dot) command, or ! operator or invalid parenthesis.
You may have entered an invalid identifier or parentheses following the Null
constant.â€

I would prefer to place the actual number of parcels in each field pack in
the ParcelQty field in the actual table [Field Packs]. Is that possible?
Note: this information is also used in various reports.

Please help! Thank you!

 

[Sprinks]

PolQueen,

Calculated fields do not belong in tables--calculate them on the fly as
needed. Google "Database Normalization" for an explanation why.

The easiest way to do this is to place a summary field in your subform's
footer. It need not be visible:

=Count([SBLFPID])

then refer to it in the ControlSource of the main form control:

= [MySubform].Form![YourSummaryControl]

Hope that helps.
Sprinks

 

[PolQueen]

I had to do a few slight modifications, but that worked. Here is my actual
formula:
=[SubformSBL].[Form]![CountSBLFPID].

Thank you!

**********

PolQueen,

Calculated fields do not belong in tables--calculate them on the fly as
needed. Google "Database Normalization" for an explanation why.

The easiest way to do this is to place a summary field in your subform's
footer. It need not be visible:

=Count([SBLFPID])

then refer to it in the ControlSource of the main form control:

= [MySubform].Form![YourSummaryControl]

Hope that helps.
Sprinks

I am trying to count the number of parcels in a field called [ParcelQty] in
my main form [Field Packs] from a field called [SBLPID] in my subform
[Query.QrySBLFP] (Note: the table name is SBL FP). The Master and Child
fields are FPackID. I used an example from the Help index and came up with
this formula:

=ParcelQty: (SELECT COUNT([SBLFPID]) FROM [SBL FP] WHERE [SBL FP].[FPackID]
= [Field Packs].[FPackID])

It does not work in the form (I tried with and without the begining equal
sign), so I tried leaving the form field unbound and using the ParcelQty
field in QryFieldPacks. I get this error message: “The expression you have
entered has an invalid .(dot) command, or ! operator or invalid parenthesis.
You may have entered an invalid identifier or parentheses following the Null
constant.â€

I would prefer to place the actual number of parcels in each field pack in
the ParcelQty field in the actual table [Field Packs]. Is that possible?
Note: this information is also used in various reports.

Please help! Thank you!