Building Access Form

[JoeM]

It is the set-up that my boss is requiring me to use. I agree and understand the reasoning as to not do such, however I must do what I am told at this point.

I am guessing your boss doesn't know much about Database Design or the Rules of Normalization.
Would they even be aware that they were looking at a Query and note a Table?

Even if you could make the argument about storing the Tax, you should never store the Total, as that just adds up those two fields.


The danger is doing so is not only does it violate the rules of Normalization and Database Design, it undermines the dynamic nature of the database, and data integrity!

For example, what if there was an error and you needed to go back and fix it. If someone fixes the Tax amount, but not the Total, you could have numbers that do not add up to the Total you show! That is very bad!!!

However, if you really want to do it that way, the easiest way is to put VBA code on the AfterUpdate event of your saleAmt field. If all your amount fields are bound to the underlying table, it will update these calculation back to your table:

Private Sub saleAmt_AfterUpdate()
    Me.saleTax = Me.saleAmt * Round(0.25, 2)
    Me.saleTotal = Me.saleAmt + Me.saleTax
End Sub

By the way, you will also want to set the Locked property on the saleTax and saleTotal fields on this Form to True, so users can only see these values and not override the calculations.

[chalupabatman]

My syntax keeps giving me an argument not optional error and highlighting the

 CSTR(" & Me.[saleid] & ") "

What is incorrect with this?

DoCmd.RunSQL = "Update [SalesTable] SET [SaleTotal] = Nz([SaleAmt], 0) + Nz([saleTax], 0) WHERE [saleid] = CSTR(" & Me.[saleid] & ") "

[JoeM]

Did you try the method I proposed?
It will update those two values directly without any need for SQL code or the need to call it manually.
It will automatically populate those two values once the Amount field is updated.

[chalupabatman]

Did you try the method I proposed?
It will update those two values directly without any need for SQL code or the need to call it manually.
It will automatically populate those two values once the Amount field is updated.

Yes, I like that method. Will go that route for sure. Just curious what is ailing my syntax above

[JoeM]

Not sure. Might be because CSTR is an Access function, and not a SQL one.
I would also be curious as to whether or not those fields are updated while the record is still "dirty" and has not been added yet.

[ssanfu]

I am thinking maybe as I have the control source set to be a calculation and not a field?

You are correct. Effectively, the controls "saleTax" and "saleTotal" are unbound controls (unbound = not bound to a field in the recordset) because the Control source is a calculation instead of a field.

How do I get those calculated values into the table fields?

One way would be to have the controls bound to their respective fields. Then, using the after update event of the control "saleAmt", you would use VBA to calculate the Tax and Total and push the values into the controls.

Private Sub salesAmt_AfterUpdate()
    Me.saleTax = Nz(Me.saleAmt, 0) * 0.25
    Me.saleTotal = Nz(Me.saleAmt, 0) + Nz(Me.saleTax,0)
End Sub

Realize this code is at the most basic level. More advanced would be to have a table for sales tax rates and date fields. And have a lot more validation code.


This does not counter the advise about not storing calculations in table unless you have to. Sometimes it is necessary for various reasons..