How to sum a calculate value in a child form

[tao lin]

Hi,

I am using Access 2000 under WinXP. I have a form which has a ChildForm.
The main form name is frmMain, the child form name is frmSale, and the
Childform call ChildClientCost. The ChildForm is shown as DataSheet. In the
child form, I have a text box which calculate the GST. The control source is
like this:

=IIf(([CostCodeID].[Column](5)<>-1) And
([Forms]![frmMain]![IsExport]<>-1),[SalePrice]*[Quantity]*0.125*[SaleExRate]
,0)

I know it's quite complex expression, but that's what my user want :-(
Now I need to show the total GST in my main form. I can not do this just
call
=Sum([ChildClientCost].[Form]![SaleGST])

Because Sum function just can sum the actual database field value. So how
can I show the correct sum GST in my main form when the Child form's
SalePrice or Quantity is changed?

Cheers,

Tao

 

[MacDermott]

I have sometimes done this by putting the calculation on the "child form" (a
more common term is subform, assuming it's displayed as part of the Main
Form.)
For instance, in the footer of the child form, I'd put a textbox txtSumGST,
with a controlsource like this:
=sum( =IIf(([CostCodeID].[Column](5)<>-1) And

([Forms]![frmMain]![IsExport]<>-1),[SalePrice]*[Quantity]*0.125*[SaleExRate]
,0))

Then on the main form I'd put a textbox with a controlsource like this:
=[frmSale].form![txtSumGST]

You can set the visible property of txtSumGST to false;
you can even set the Visible property of the entire footer to false if you
like.

HTH
- Turtle

Hi,

I am using Access 2000 under WinXP. I have a form which has a ChildForm.
The main form name is frmMain, the child form name is frmSale, and the
Childform call ChildClientCost. The ChildForm is shown as DataSheet. In the
child form, I have a text box which calculate the GST. The control source is
like this:

=IIf(([CostCodeID].[Column](5)<>-1) And

,0)

I know it's quite complex expression, but that's what my user want :-(
Now I need to show the total GST in my main form. I can not do this just
call
=Sum([ChildClientCost].[Form]![SaleGST])

Because Sum function just can sum the actual database field value. So how
can I show the correct sum GST in my main form when the Child form's
SalePrice or Quantity is changed?

Cheers,

Tao

 

[tao lin]

Hi, MacDermott

It looks like don't work. Because my GST expression is too complex:

,0)

Not matter I try
=sum(IIf(([CostCodeID].[Column](5)<>-1) And
([Forms]![frmMain]![IsExport]<>-1),[SalePrice]*[Quantity]*0.125*[SaleExRate]
,0)) or

=sum(IIf(([CostCodeID].[Column](5)<>-1)),[SalePrice]*[Quantity]*0.125*[SaleE
xRate] ,0))

All return #Error in the SubForm footer.

And thenI try
=sum(IIf(([SalePrice] > 0),[SalePrice]*[Quantity]*0.125*[SaleExRate] ,0))
It works. I think the problem is: in my GST expression, I use one combobox
column value, one Main From text value as the IIF condition, Ms Access just
can't understand this 'horrible' expression. I can't figure out how to make
Access can sum my GST value.

Cheers,

Tao

I have sometimes done this by putting the calculation on the "child form" (a
more common term is subform, assuming it's displayed as part of the Main
Form.)
For instance, in the footer of the child form, I'd put a textbox txtSumGST,
with a controlsource like this:
=sum( =IIf(([CostCodeID].[Column](5)<>-1) And

,0))

Then on the main form I'd put a textbox with a controlsource like this:
=[frmSale].form![txtSumGST]

You can set the visible property of txtSumGST to false;
you can even set the Visible property of the entire footer to false if you
like.

HTH
- Turtle

Hi,

I am using Access 2000 under WinXP. I have a form which has a ChildForm.
The main form name is frmMain, the child form name is frmSale, and the
Childform call ChildClientCost. The ChildForm is shown as DataSheet. In the
child form, I have a text box which calculate the GST. The control

source

is
like this:

=IIf(([CostCodeID].[Column](5)<>-1) And

,0)

I know it's quite complex expression, but that's what my user want :-(
Now I need to show the total GST in my main form. I can not do this just
call
=Sum([ChildClientCost].[Form]![SaleGST])

Because Sum function just can sum the actual database field value. So how
can I show the correct sum GST in my main form when the Child form's
SalePrice or Quantity is changed?

Cheers,

Tao

 

[Dank57]

One way I address this reference issue is to create an
invisible control on the child that is the sum of the
records you want. Then, in the parent form, I create
another control and reference it to the invisible field in
the child form.

Me![ParentControl] = Me![SubFormName]![SumControl]

Sometime Access doesn't like the Me! so I go ahead and use
the full reference. Try it both ways. I've always gotten
it to work.