Table and Form not updating

[Voyager2122]

Hi,

Here is a simple problem, yet I can't find the solution and would like someone's help please:

I have a single form linked to a single table. There are no subforms involved and no dates either. On my table, I have 5 columns. Each column is a Currency field with 1 Decimal field. I then created a form with 5 text boxes linked to the table. I had no problems manually inputting my data using the forms text boxes which then auto updated into the table.

I then modified one of the text boxes to use a formula instead of me manually adding the information. Since I have added this formula, my table no longer updates with the new information nor existing information.

Is there a way to have the table update from the form based on both existing information and new information and how do I go about setting this up?

If this helps, here is how I set it up:

Table with 5 columns (named UnitPrice, Quantity, Subtotal, Tax, GrandTotal)
Form with 5 controls (named Unit Price, Qty, STotal, Tax, GTotal)

I modified the forms "Tax" control, to use a formula instead of manually entering the data. This results in the table not updating for existing and new records.

Any help with this, would be greatly appreciated.
Thanks in advance.

 

[ansentry]

Welcome to the forum.

1) As a rule you should not store calculation in a table.
2) Most of what you want (re calculations) can and should be done in the query that is the Record Source for the form.

Have a look at the sample I have attached, you should have a look at;
* The query that is the source for the form (qryData)
* Have a look at what is stored in the table ( not as much as the query shows)
* Have a look at how the table tblTaxRate is linked to the table tblData.
* Note that in the tblData only the Primary Key number is stored from the tblTaxRate.

Hope this is a help.

 

[boblarson]

Welcome to the forum.

1) As a rule you should not store calculation in a table.
2) Most of what you want (re calculations) can and should be done in the query that is the Record Source for the form.

Mostly that is true. In the case of tax, storing that information is very acceptable as tax rates can vary over time.

 

[ansentry]

Bob,

In the sample I posted I have a table with the Tax Rate and then placed a combo on the form, the primary key from the Tax Rate table is saved in the "Main" table.

In your option is that OK or should you store the "Rate" and not the key. I was just thinking that my method would not be accurate if someone changed the rates in my rate table.

 

[boblarson]

You could do one of two things -
Either
1. You could save the calculated tax in the table
2. You could store the tax rate with an effective date range and then base your calcs on the date the tax was charged.

While #2 is more normalized, #1 is simpler and more efficient. So, in this case, storing the calculated value is probably better.

"Denormalization" is sometimes okay.

 

[Voyager2122]

Hey John,

Firstly, thank you very much for responding. Secondly, awesome database!!! I used your theory and applied it to my problem. I never thought to use a query to append the data and then link my reports to the query. I'm able to get the data that I need just by modifying the query's parametres.

Thanks for all your help !!!......Greatly appreciated.