Subform totals

  • Thread starter Thread starter Joan
  • Start date Start date
J

Joan

I have an Invoice form with a subform. The subform's record source changes
depending upon if the main form is opened in data entry mode or edit mode.
The subform will either contain pets(dogs or cats) that will be invoiced for
a particular store or pets that have already been invoiced depending upon
whether the data entry property of the main form is true or false. The link
master field is [StoreCode] and the link child field is [Store].(When the
user enters a StoreCode on the main form, the correct dogs automatically
appear in the subform when in data entry mode.) In the subform's footer, I
want the totals for the number of pets, the number of dogs, the number of
cats and the invoice total of the sale prices.

I am having problems with the total dogs and total cats controls as I get
error! messages in them instead of the totals. The subform contains a
[SpeciesCode] field which has either a "D" or "C" for each pet listed on the
subform. I've tried using the DCount function in the control source of the
totalDogs control : DCount('[SpeciesCode]", "qryInvoiceSubform2",
"[SpeciesCode] = 'D'")
I tried using this for when the Invoice form is in data entry mode. The
subform lists 3 dogs, which is correct. However, the totalDogs control has
9. The function returned the total number of dogs sold to all stores in the
query, not just the number for the particular store in the Invoice. So this
is one problem I'm having. The other is: How do I change the domain in the
function depending upon the data mode of the main form?

Any suggestion on how to fix this will be very appreciated.

Joan
 
Joan said:
I have an Invoice form with a subform. The subform's record source changes
depending upon if the main form is opened in data entry mode or edit mode.
The subform will either contain pets(dogs or cats) that will be invoiced for
a particular store or pets that have already been invoiced depending upon
whether the data entry property of the main form is true or false. The link
master field is [StoreCode] and the link child field is [Store].(When the
user enters a StoreCode on the main form, the correct dogs automatically
appear in the subform when in data entry mode.) In the subform's footer, I
want the totals for the number of pets, the number of dogs, the number of
cats and the invoice total of the sale prices.

I am having problems with the total dogs and total cats controls as I get
error! messages in them instead of the totals. The subform contains a
[SpeciesCode] field which has either a "D" or "C" for each pet listed on the
subform. I've tried using the DCount function in the control source of the
totalDogs control : DCount('[SpeciesCode]", "qryInvoiceSubform2",
"[SpeciesCode] = 'D'")
I tried using this for when the Invoice form is in data entry mode. The
subform lists 3 dogs, which is correct. However, the totalDogs control has
9. The function returned the total number of dogs sold to all stores in the
query, not just the number for the particular store in the Invoice. So this
is one problem I'm having. The other is: How do I change the domain in the
function depending upon the data mode of the main form?
Click to expand...

In the OnCurrent event of the subform, I would put in the logic to change
the data for the total field in the subform, depending upon what the value
is in the Parent form. i.e:
if me!parent!chkbox = TRUE then
me!countfield=DCount("PetID","Seattle","Invoice=TRUE and Type = 'DOG'")
else
me!countfield=DCount("PetD","Spokane","Invoice=FALSE and Type = 'CAT'")
end if

It is possible to have more than one Where clause in the dbCount function.
 
Back
Top