Updating main form

  • Thread starter Thread starter Allie
  • Start date Start date
A

Allie

I have a main form and a subform (which is a datasheet)
The main form shows the totals from the subform and the
data is stored in a summary table. The subform shows each
transaction processed by an individual on a particular
date.

As a transaction is added or deleted to/from the sub form
the totals on the main form are updated. The problem is
when a transaction is changed. When the transaction is
changed it adds the value of the new transaction to the
totals on the main form and does not delete the old value.

How do I correct this?

To add to the totals on the main form I am using the
following in the after-update event of the transaction on
the subform:
[Forms]![frmMainForm]![txtQQ] = [Forms]![frmMainForm!
[txtQQ] + 1

thanks
 
Allie, not to answer your question, but- in a relational database like
Access, it is generally not good practice to store calculated totals. Better
to recalculate them at runtime using queries etc. Then the issue of having
to synchronize the totals, with the transactions, will goe away completely.

HTH,
TC
 
The problem with that is the details/transaction table
will archived to CD every month, that is why the summary
table is needed.

Any help will be appreciated.

Thanks
-----Original Message-----
Allie, not to answer your question, but- in a relational database like
Access, it is generally not good practice to store calculated totals. Better
to recalculate them at runtime using queries etc. Then the issue of having
to synchronize the totals, with the transactions, will goe away completely.

HTH,
TC


I have a main form and a subform (which is a datasheet)
The main form shows the totals from the subform and the
data is stored in a summary table. The subform shows each
transaction processed by an individual on a particular
date.

As a transaction is added or deleted to/from the sub form
the totals on the main form are updated. The problem is
when a transaction is changed. When the transaction is
changed it adds the value of the new transaction to the
totals on the main form and does not delete the old value.

How do I correct this?

To add to the totals on the main form I am using the
following in the after-update event of the transaction on
the subform:
[Forms]![frmMainForm]![txtQQ] = [Forms]![frmMainForm!
[txtQQ] + 1

thanks
Click to expand...


.
Click to expand...
 
Ok, understood.

You say: "To add to the totals on the main form I am using the following in
the after-update event of the transaction on the subform:
[Forms]![frmMainForm]![txtQQ] = [Forms]![frmMainForm![txtQQ] + 1"

What is txtQQ? I don't see how adding 1 to a "total amount" field, would
ever update a "total amount" correctly. Are you >counting< the transactions,
or summing the transaction >amounts<?

Maybe an alternative approach would be to add an unbound textbox to the
footer section of the subform. Set the controlsource of that textbox to
"=Sum(Blah)" or somesuch - without the quotes - where Blah is the subform
field that you want to keep summed. This should automatically keep an
accurate sum of that field, without using any code. Then use the
Form_AfterUpdate of the subform to copy that sum to the main form. You could
set the new textbox'es Visible property to false, so it did not actually
display on the subform.

I don't have Access here to check, so bear that in mind if you try that
suggestion.

HTH,
TC


Allie said:
The problem with that is the details/transaction table
will archived to CD every month, that is why the summary
table is needed.

Any help will be appreciated.

Thanks
-----Original Message-----
Allie, not to answer your question, but- in a relational database like
Access, it is generally not good practice to store calculated totals. Better
to recalculate them at runtime using queries etc. Then the issue of having
to synchronize the totals, with the transactions, will goe away completely.

HTH,
TC


I have a main form and a subform (which is a datasheet)
The main form shows the totals from the subform and the
data is stored in a summary table. The subform shows each
transaction processed by an individual on a particular
date.

As a transaction is added or deleted to/from the sub form
the totals on the main form are updated. The problem is
when a transaction is changed. When the transaction is
changed it adds the value of the new transaction to the
totals on the main form and does not delete the old value.

How do I correct this?

To add to the totals on the main form I am using the
following in the after-update event of the transaction on
the subform:
[Forms]![frmMainForm]![txtQQ] = [Forms]![frmMainForm!
[txtQQ] + 1

thanks
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top