table not updated

[stephendeloach via AccessMonster.com]

i have a form/subform that i have added a new field to, netamt. I added the
field to the table, then added it to my query, then added it to my subform
that is in my form. The field is set to currency and has =Nz([Quantity],0)*Nz
([UnitPrice],0) as the ControlSource. The number in the subform is not
updated in the table? Why is this? I dont need this number to show on the
form, just on the Report.

 

[Amy Blankenship]

i have a form/subform that i have added a new field to, netamt. I added the
field to the table, then added it to my query, then added it to my subform
that is in my form. The field is set to currency and has
=Nz([Quantity],0)*Nz
([UnitPrice],0) as the ControlSource. The number in the subform is not
updated in the table? Why is this? I dont need this number to show on the
form, just on the Report.

There is no Nz([Quantity],0)*Nz ([UnitPrice],0) field in your table TO
update, nor should there be. Since you don't need the field anywhere but
the report, why not just calculate it there?

-Amy

 

[George Nicholson]

You have an unbound textbox. (It's unbound because you have a formula as the
control source rather than a field name.)

If you want the value from an unbound control to be stored in a field, you'd
need to write some code to do that. Access only updates bound fields
automatically.

Having calculated fields in tables is almost always very bad database design
for a number of reasons. Any calculations necessary for reports or forms are
better done in queries or on the reports/forms directly (which is what your
unbound textbox is doing). No need to store the results in a table, just
duplicate that textbox on reports, etc.

If you do need to store calculation results for stringent audit purposes,
then you'd need to add some code to do that *and* consider all the
maintenance ramifications: how many ways can the dependent data be changed,
now or in the future, that could make this field contain an incorrect
value?.

HTH,

 

[John W. Vinson]

I dont need this number to show on the
form, just on the Report.

Then just show it on the Report, not on the form; use the expression as the
control source of a textbox on the Report.

John W. Vinson [MVP]

 

[stephendeloach via AccessMonster.com]

OK now on the Invoice Summary report under the Amount I have =Nz([Quantity],
0)*Nz([UnitPrice],0) which shows the Amount fine. But sometimes the amount
will have Tax added into it. If it DOES have Tax added into it, the
calculation is =Sum(IIf([taxablechk]=True,[quantity]*[unitprice],0)) which
is in the single invoice report. (this is a report with ALL of the invoices)
How would i make the Invoice Summary Report have both Tax and Non-Taxable
Amounts?

 

[John W. Vinson]

OK now on the Invoice Summary report under the Amount I have =Nz([Quantity],
0)*Nz([UnitPrice],0) which shows the Amount fine. But sometimes the amount
will have Tax added into it. If it DOES have Tax added into it, the
calculation is =Sum(IIf([taxablechk]=True,[quantity]*[unitprice],0)) which
is in the single invoice report. (this is a report with ALL of the invoices)
How would i make the Invoice Summary Report have both Tax and Non-Taxable
Amounts?

Just use the IIF to wrap the expressions:

=IIF([tasablechk], <expression if taxable>, <expression if not>)

John W. Vinson [MVP]

 

[stephendeloach via AccessMonster.com]

Sorry, Im not that great with this stuff.. here is the controlsource for non-
taxable =Sum(IIf([taxablechk]=False,[quantity]*[UnitPrice],0)) and here
is taxable =Sum(IIf([taxablechk]=True,[quantity]*[unitprice],0))
could you tell me what would be the expression? Everytime I enter it, it
gives me an error... Thanks

OK now on the Invoice Summary report under the Amount I have =Nz([Quantity],
0)*Nz([UnitPrice],0) which shows the Amount fine. But sometimes the amount

[quoted text clipped - 3 lines]

How would i make the Invoice Summary Report have both Tax and Non-Taxable
Amounts?

Just use the IIF to wrap the expressions:

=IIF([tasablechk], <expression if taxable>, <expression if not>)

John W. Vinson [MVP]