Form & Subform Calculation

[Guest]

Hello Everyone!

I have a Main Form that has an Amount field in it and also a Subform with an
Amount Filed in it. The main form's amount is the total amount for an
invoice. The Subform amount breaking down the invoice amount to different
cost centers. I would like to be able to do one of two things:

1. Create a field some where on the form to total the amount fields in the
subform without interupting the working of the form and not being part of the
table but part of the reord on the form

Or

2. Somehow link the two fields somehow so when the user is inputting amounts
in the subform, a message will pop up tell the user the total exceeds the
invoice amount(amount in the main form) if they mistakenly try to enter more
than the invoice amount.

If anyone could help me, I would greatly appreciate it as I am probably just
past the novice stage with Access but want to learn to be a guru. Therefore,
please simplfy answer to make it easier for me create.

Thank you everyone for your patience, help and expertise!

 

[Guest]

Hi,

in The subform you could make a calculation field which you put in the Form
Footer if not visible make them visible through the view menu item.

the field you add is a textbox e.g. txtCalculation and you change the
control source property to e.g. =Sum(Table_AmountField)

Now this field you can reference from you parent form
First you have to add another textbox e.g. txtSubCalculation for which you
set the control source property to SubFormName.Form.txtCalculation

This was your number 1

To do Number 2 you should add an afterupdate event to your Table_AmountField
(or a field which is changed and causes the amounts to change)
This event should first of all refresh the txtCalculation and then verify
it's amount vs the amount on the parent form.
e.g.

Private Sub Table_AmountField_AfterUpdate()
txtCalculation.Requery
if txtCalculation > parent.form.Parent_AmountField then
msgbox "alert"
end if
End Sub

- Raoul

 

[Guest]

Kuya,

I see no reason to save this calculation to a field. You can always
calculate it on the fly. See Al Camp's post below under "Calculated Fields".

Sprinks

 

[Guest]

Hi JaRa,

Thank you for your reply. I put a textbox in the subform form footer but it
is not visible. In the proerties section where it says Visible, it is set to
Yes. Also, I am not sure what you mean by "Table_AmountField"?

In #2, I want a third box to show that performs a running total so as
figures are being added to the sub form, it calculates the total. When the
total exceeds the invoice amount in the parent form in the "Amount" field, I
want a pop up to show as a warning. I guess have this warning box run off of
the third amount box that is performing the running total. Does this make
sense? I sure appreciate your help.

Thanks!

 

[Guest]

the textbox is used to calculate a value on the fly.
It makes a sum of a field (Table_AmountField should be set to your
fieldname) on your subform

- Raoul