Building Access Form

[chalupabatman]

I am new to building access forms. I have a table that has a few fields in it
saleID
saleAmt
saleTax
SaleTotal



On my form - the saleID is not displayed, it is an auto-generated number (primary key)
the saleAmt is manually input
the saleTax is calculated by the control Source ..... =NZ([saleAmt],0)*.25
the saleTotal is calculated by teh control Source .... = NZ([saleAmt],0)+NZ([saleTax])

Now on my form this all displays exactly as it should, BUT when I go back to review the entries in my table saleTax and saleTotal are never updated to the appropriate values. I am thinking maybe as I have the control source set to be a calculation and not a field? How do I get those calculated values into the table fields?

[ranman256]

yes, calculated fields should never be saved into a field.
They should be calculated in a query.

[chalupabatman]

So what would be the appropriate way to get that data from the field into my table?

[chalupabatman]

Should I build a query to run to update these records when the "Save" button is pressed on the form, or what would be the best way to handle this?

[ItsMe]

You might want to store tax amounts invoiced in your tblInvoices. Also, you may want to break down State and local taxes in their own columns. I will typically use the double data type in tables to store dollar amounts. I believe the Currency data type in Access uses bankers rounding.

[chalupabatman]

You might want to store tax amounts invoiced in your tblInvoices. Also, you may want to break down State and local taxes in their own columns. I will typically use the double data type in tables to store dollar amounts. I believe the Currency data type in Access uses bankers rounding.

Thank you for that information. I am still stuck on how to actually get the calculated values back into my table tho.

[ItsMe]

It sounds as though you are using a calculated field at the table level. In other words, you chose "Calculated" under the data type while in design view of your table.

If you want to store that value somewhere, you will need to retrieve it and write it elsewhere. I would guess using some VBA behind a form would be easiest. I would caution that the value stored may not always be the value displayed when dealing with fractions. This can cause issues down the road when calculating sums of multiple records.

[chalupabatman]

It sounds as though you are using a calculated field at the table level. In other words, you chose "Calculated" under the data type while in design view of your table.

If you want to store that value somewhere, you will need to retrieve it and write it elsewhere. I would guess using some VBA behind a form would be easiest. I would caution that the value stored may not always be the value displayed when dealing with fractions. This can cause issues down the road when calculating sums of multiple records.

The fields are actually Number in my table. What I am trying to do is have as little user intervention as possible. So that is why I am calculating and displaying the tax and total for the user, so they do not have to do such. I just am trying to figure out the best way to get it back into my table since I have the control source set to a formula instead of the actual field itself.

[chalupabatman]

BTW - my save button is auto-created by access as a Embedded Macro which leaves me to now knowing how to alter that VBA to write any further changes to the table

[ItsMe]

Can you recreate what you did in a sample DB and upload it here? I know there are a lot of components to this puzzle that have not been communicated. Also, can you provide a description of what your "save" button does for you?

[JoeM]

As ranman said, usually there is no need to save these amounts in your table. Anything that can be calculated in a query shouldn't be stored in a table field.
With normalized data (which is the goal of database design), fields should not dependent upon other fields in the table.
Usually, there is no need to store values which can be calculated at the table level. You can use a query in Access for anything that you can use a table for.
So, the question you need to ask yourself is why do you think you need to store these at the table level?

The only time it might make sense to store these values at the table level is for historical purposes if the tax rate can change.
Even then, some might argue that you should have a table with the tax rates and effective dates, and the date of the transaction should drive with tax rate to use (so there still would not be a need to store the calculated tax at the table level).

[chalupabatman]

That may be best practice but unfortunately I need to do the calculations and have them stored in the table. I have been playing with the SaveRecord button click event, but my data just is not saving as I expected. This is what I have thus far and maybe someone can help me iron out the wrinkles

Private Sub savebutton_click()
    DoCmd.RunCommand acCmdSave
    DoCmd.RunSQL "UPdate [SalesTable] Set [SaleTotal] = NZ([saleAmt],0)+NZ([SaleTax],0) Where [saleid] = CSTR(" & Me.[Saleid] & ") "

But when I view the actual calculation it always shows either a 0 or -1 which is not accurate.

[JoeM]

That may be best practice but unfortunately I need to do the calculations and have them stored in the table.

Please explain why you think this is the case.
I am not asking to be difficult, I am asking because more times than not, someone says something like I need it to be in an Export or Report, and then I tell them that Queries can be used for that, just as easily as Tables can, and they say "Oh, I didn't know that!".

So can you explain why you think they need to be stored at the table level?

[chalupabatman]

Please explain why you think this is the case.
So can you explain why you think they need to be stored at the table level?

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.

[ItsMe]

Looking at post #12, you probably have an issue with your Where clause. Try using this as a kicking off point for editing your where criteria. Try using the query designer and then view the resulting SQL using SQL view.