Storing a calculated field on a form

[nwardle]

Hi there everyone,
I hope someone can point out the obvious to me as I have exhausted my limited knowledge of access to perform what I would have thought to be simple task!!
I have a form called "scoreform" linked to a table called "scoretable", in this table there are numerous fields that hold values entered in on the form via combo boxes to speed up repetitive data input. Also in the table there is a field for total..... now on the form I have a txt box that calculates the total score by setting the control source property to "= [combo1]+[combo2]+[combo3] etc" now this is fine as it gives the total score displayed on the form, but how on earth do I get it to store that value in the table field "total score"!!!

I hope I have managed to describe the situation well enough and would be most greatful for any suggestions.
Many thanks
Nic

 

[Shadez]

Create an update query and run it

 

[Rich]

You shouldn't store the value, it can be re-calculated at any time

 

[nwardle]

hmmm

Thanks guys for the prompt reply.
I really need to have the value stored in the table as the data from the table is used to produce reports - so calculating the total on the form will not help.
As for an update query - would that not require me to "run" it everytime the total is calculated by habd - or could I make the query run on closing the form??

Thanks again
Nic

 

[Mile-O]

Re: hmmm

Thanks guys for the prompt reply.
I really need to have the value stored in the table as the data from the table is used to produce reports - so calculating the total on the form will not help.

You can, of course, calculate it on a report too.

 

[nwardle]

Thanks

Thanks everyone
Seem to have it working by using an update query as suggested which is run everytime the form is closed (though it actually updates ALL the total scores fields - doubt I can stop that though?)
Still can't decide whether (again as suggested) I actually need the value permanently stored if I am able to calculate it the report itself!!
Shall keep pondering
Thanks
Nic

 

[Mile-O]

Storing values in a table is a violation of 3rd Normal Form.

 

[Shadez]

Storing values in a table is a violation of 3rd Normal Form.

If he wants to do it let him, its his program.

Seem to have it working by using an update query as suggested which is run everytime the form is closed (though it actually updates ALL the total scores fields - doubt I can stop that though?)

yes you can, you just need to add a where condition to the update query. you normaly base it on the primary key of the row you want updateing.

Note: Even if you do calculate in your reports (this is the best way), its probably a good idea (as an execise) to finish of your update query.

 

[nwardle]

Eh?

Whats 3rd Normal Form then??
I don't want to get into trouble if I can help it!!!
Is this a true entity, or am I being councelled in the way of the masters???!!!!!
Nic

 

[Mile-O]

3rd Normal Form is where the table is in 2nd Normal Form and all of its non-key columns are mutually independant (meaning are not dependant upon values in other columns.

Basically, all fields in a table must depend on the primary key (which is 2nd Normal Form) and not on other fields in the table.

i.e

storing a field called Total where the total is dependant upon two other fields called Price and Quantity, and the Total is the Price multiplied by Quantity does not make a table 3rd Normal Form


Before third normal form there is first and second. I've described second above.

First: For each row-by-column there shoudl exist only one value meaning that you shouldn't have a field like Inventory and then one of the cells in that field contains data such as: "one apple, seven bananas, and a lychee."



Once your database is in 3rd Normal Form it should be simple enought to do almost everything you intend with your database. There are further Normal Forms, Boyce-Codd, Fourth, Fifth, but I think you can forget about them.

 

[Autoeng]

Never store totals is the 3rd Normal form. If it can be calculated, calculate it at time of need. On your report add a field that calculates the fields that you need.

 

[nwardle]

OMG!

Ah, this is where I see the void that lies between you guys and me!!!
It all makes sense (described nicely thanks) but I would never have even contemplated it if you hadn't taught me!!!
Thats why people like me, need help from people like you!!!!
Cheers
Nic

 

[Mile-O]

You'll also get loads of more in-depth explanations and examples on line by using Google or whatever.

 

[nwardle]

Daft question again

Thanks Pat
Thats sounds an easier way to do it - though I really get the point now that its not cool to store calculated fields!!!
So I went off to try your suggestion, but guess what, I can't even fathom that out!!
I understand the principle behing it but do not get your last argument in the code:
In my database=
Me.[Total Score] (this is the table field name like you say) = Me. now what is this unbound calc control you refer too??

My calculation is performed very basically by "suming" all the combo box values in the "control source" propertie of a text box on the form called Total Score. Does that mean the code should read:
Me.[Total Score]=Me.[Forms].[Score Form].[Total Score]
where Score form is the name given to this blasted form!!

Please put me out of my misery as this all helps me understand what is going on. Then I promise I won't mention storing a calculated field again!!! LOL

Nic

 

[nwardle]

Ermmm - its no good, need new brain

Sorry about this.
Still can't get this to work. I shall try to attach a cut down table and form so you can see what I am trying to do and maybe (after a few slaps with a wet fish to my face) make me see sense!!!

 

[Mile-O]

Okay, here's where you are going wrong.

Me is a keyword used in code to explicitly reference the current form's class.

You were putting the suggested expression into the event but not in the code.

I've changed a few things in your database too - made it more relational and fixed control names, field names, etc

As a general practice, not enforced though, it's better to give a control a prefix pertaining to its type. i.e. a textbox (txt), a combobox (cbo), listbox (lst).

 

[Autoeng]

Wow Mile. You are a nice guy after all. I'm still using one of my original db's with all of the poor practice things in it before I knew better. Too lazy to fix them. If I post it would you?

 

[nwardle]

A real belated thanks!!

Many thanks to everyone who helped me on this, particularly Mile-o-phile who took out surely more than a minute or twi to correct my shoddily composed database, a BIG THANK YOU I realise that it is unfair to expect people to use their own time to correct others peoples mistakes but was very glad for the assistance on this one.
Many apologies for the tardy reply to this, but my real job (an Orthopaedic Surgeon) unfortunately has taken me away from my "hobby" for too long!!
Thanks again all.

Now as a little "PS" is it possible (yes I know its not good practice!) to store more than one calculated field from a form using this trick?? (ie multiple Me![Field Name] = Me![txtbox name]??? - I have tried but don' t seem to get the option of the fields/txt boxs in "auto complete" mode and it doesn't work if typed in manually. Sure I'm pushing my luck here, but have to ask!!!

Nic

 

[nwardle]

thanks again

Quick reply Pat!
Unfortunately have tried that myself - I thought it should be possible but it just doesn't seem to want to rock with me!!
If anyone has the chance or Inclination, then just comment (not expecting rewrites!!!) on this amateur effort for me!
THanks if able
Nic