Data calculated on forms not transferring back to Tables

[DavidGr]

I understand that any data that is inputted into a form is always transferred back to the table in question.

In the form I created, I had

4 subheading scores added together to form another subheading named function =([1]+[2]+[3]+[4])/4 was the formula that I put into the control source. The calculation worked fine on the form giving me the correct name. However, this number did not transfer back to the table simple being labelled as zero. Can someone please advice!

 

[battenberg]

first answer the question...

why do you want to store the final sum amount as opposed to working it out on the fly?

if the initial numbers change in your table, then you would always need to recalculate your average using that form to store the correct value. best practice is not to store the results of a calculation but only store the basic values and recalculate them when needed.

 

[DavidGr]

the initial numbers do not change in the table but because it is a complex questionnaire, i dont want to be spending a long period of time working out different subsections for 120+ patients. Plus it won't always be me inputting the data and in circumstances, people inputting will not be privy to the calculations. However, if it works it out automatically, they dont need to. Plus, these scores are important and therefore best practice would be to store then for large data analysis.

Plus I think you misunderstand the use of the form. It's just is not for calculating values as such but for making it visually easier for people to input the data quickly without typing it in a data-sheet like format. Plus, there won't be multiple calculations, just a one time calculation which then should be stored!

 

[battenberg]

put this in the on current event of the form:

dim intValue as integer

intValue =([1]+[2]+[3]+[4])/4

[NameOfFieldtoStoreCalculatedResults]= intValue

 

[seth_belgium]

In reply to your PM: I agree with battenberg's sollution

 

[DavidGr]

there's only before and after update under event and when that it, in said invalid syntax?

 

[battenberg]

Thankyou Seth.

Dave... look in the properties of the form, not the control, you'll find it

 

[DavidGr]

okay, i have found the event heading (under properties) but still before and after event rather than current.

Sorry about being really slow on this!

 

[battenberg]

go into the vb editor: ALT+F11

find the form and dbl click on it...

Private Sub Form_Current()

dim intValue as integer

intValue =([1]+[2]+[3]+[4])/4

[NameOfFieldtoStoreCalculatedResults]= intValue

End Sub

paste in the code with the correct field name

 

[seth_belgium]

It's also hard for us to see what you mean exactly without showing us the database

If I get it right, the value is calculated by summing 4 other values that are also present in your query? You might still want to consider calculating the field directly in your query and simply displaying that result on your form.

To do this, add a new collumn to your query, it should look like this:

TotalScore: [Field1] + [Field2] + [Field3] + [Field4]

Just make a textbox on your form and set the field TotalScore as sourceobject.

 

[DavidGr]

lol okay, right there is no query.

Right lets try this again - I actually think I know why it's not working too, I just don't know how to get around it.

I have a form that has a number of scores. Some of these scores are based on other numbers in the form

An example would be

Score X = Score A + B + C + D divided by 4 (this isn't an actual one, just an example).

In order to work out these calculations, I put in Score X's control source the required expression i.e. [= (A + B + C + D)/4]. This results in the score being shown on the form. However, this number is not transfered back to the table (i think it is because the table itself doesn't understand the control source as it formulaic rather than just number).

If you could help me resolve this issue so that my table has the correct numbers so that I could then design my queries!

Many Thanks,

David

 

[seth_belgium]

Hmmm... How about using an UPDATE query to populate the fields that you want to calculate? You'll have to do this again everytime you add a new record and everytime one of the fields change. That's why we said it would be more advisable to perform the calcultations on the form itself and not store the totals anywhere.

For the database this is a rather simple calculation and it won't take any noticeable amount of time.

 

[battenberg]

sounds like you need to check your table's for correct data types

 

[DavidGr]

sounds like you need to check your table's for correct data types

Take a look at the form ask and the table entitled asques

http://www.geocities.com/davidg031984/DatabaseDG.mdb

i've edited it sufficiently

 

[battenberg]

why dont you just post your database - stripped of data, up here
export your table without data and your form to a new database and post it here

 

[DavidGr]

thats what i've just done! (look above)

 

[battenberg]

No you haven't...do you think i'd be asking for it if that link worked?

For someone who's getting other people's time and advice for free, you have a strange temprement! I've already written the code for you...I'm out of here

 

[boblarson]

The problem lies in that you are using a control source for the calculations and if a text box has a formula for the control source then it ISN'T going to store anything in a table because it isn't bound to a field (the control source is where the binding takes place). So, you need to put the code that battenberg posted in the on CURRENT Event of the FORM (not the control), the FORM and then set the text box's record source to the field in the table you want the calculation to be stored.

The FORM's On CURRENT event is accessed by:
1. Go into the form's design mode.
2. DO NOT CLICK A CONTROL
3. IMMEDIATELY after going into design mode the form will be the object selected and if you go under the EVENTS tab of the properties window at THAT point, you will see the CURRENT event. Controls themselves do not have that event and you had selected a control which then made it so you could not see an event with that name.

 

[DavidGr]

I'm afraid the coding didn't work!

Thank you for all your time, which I do really appreciate. I can't believe i've spent a whole day trying to figure this out mind you and have gained zero progress...Battenburg, with all honesty, everytime I checked that link it worked (a person downloaded it and pmed me about it). I do apologise and appreciate the effort that you have put in. I guess i'm just not cut out for this stuff! This is all very new to me and I've basically been asked to create a complex database from scratch with minimal experience so I am sure you can appreciate that this is very difficult for me, not to mention concerning. I hope you can understand why I am so frustrated as this is very much over my head and it's annoying not to be able to do something that is very important!

Cheers

 

[boblarson]

Okay, I fixed your problem (see attached db). I did it the speed way, not the way I would have normally done it but I didn't have time to change that many text box names to ensure the calculations worked in code.

So, I just put a hidden text box on your form and then set that to do the calculation and then bound the average score to the table and then put in code in the on current event to set the avg score box equal to the hidden box, which then stores it in the table.

You will have to do something similar for the Total box. Also, I had to change the datatype for the avg from Long Integer to Double so that it would store something other than zero, because when talking percentages you are talking less than one (1) and so as an integer anything less than one (1) will be stored as zero (0).