COMPLICATED CALCULATED FIELDS ON A FORM

[kaniz]

I have form with a calculated field on it. It should calculate the values from the subform.

The way the tables are stored are as follow
It is one to many relationship
ID TOTAL TIME (FORM)
12345 35


ID CODE TIME (SUBFORM )
12345 A 15 record 1 on subform
12345 B 20 record 2 on subform
**** notice is same id because of the relantionship***


As I keep entering records on the subform, I would like the totaltime on the form to keep updating and the last value on this box on the form to be stored in the table.
Is this possible to do without writing some very lenthy procedure.

thank you all you that have helped in the past

 

[Rohdem]

Using the 'on insert' event of the subform, couldn't you just recalculate the value and assign it to a field on the main form... something like (this is not real code)

on insert

me.parent.totalbox = dsum("time", "sometable", "id = " & me.id

end sub

Then if you had 'totalbox' bound to a field in the main table, it would store this value with the record.
Mike Rohde
rohdem@marshallengines.com

 

[kaniz]

thank you for your reply I will give this a try. I just run into a litle problem with my formula which I thought was working so I can really try the aboe until get this going maybe someone can help figure this out or maybe it should be a new thread please let me know if it should be for next time

On my main form a have to two text boxes. one is bound to the field on the table,
the other one has the following calculation and because I did not know how to do it this is how I have the formula

Status is a subform

=[Form].[STATUS]![TOTAL1]+
[Form].[STATUS]![TOTAL2]+
[Form].[STATUS]![TOTAL3]+
[Form].[STATUS]![TOTAL4]+
[Form].[STATUS]![TOTAL5]+
[Form].[status]![total6]+
[Form].[status]![total7]+
[Form].[status]![total8]+
[Form].[status]![total9]+
[Form].[status]![total10]

The box that I have on the main form with the calculation works, but the box that is bound to the table field does not work. Someone on this forum told me to do this way and it worked for one of the fields but this other one does not. is it because the calculation is too long. What is wrong with it

 

[Rohdem]

You can't have the control source of a text box be bound to a table and to a calculation at the same time. You need to have your text box bound to a field in the table and update the value each time you change one of the values that your calcuation is based on.

You could possibly use the 'after update' event of the form to reassign the value to the text box


Private Sub Form_AfterUpdate()
me.textbox =[Form].[STATUS]![TOTAL1]+ .......
End Sub
Mike Rohde
rohdem@marshallengines.com

 

[JerryDennison]

With very few exceptions you should never store calculated results in your table. The calculations should be recalculated on demand based on the latest data. This is the only way you can be 100% sure of getting accurate results.

 

[kaniz]

thanks Mike I appreciate your help. It has given me and idea or a few things to try but so far I have not been able to get a calculation going.

what is wrong with this statement

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim intcounter As Integer
For intcounter = 1 To 1
Me.Parent.TotalStatus = DSum("total" & intcounter, "run time", "[id] = " & Me.[ID])
Next
End Sub

totalstatus is a bound box on the form(to the table runtime)

the problem is that the table does not get updated with the last value. Right now everytime I entered a record on the subform it overwrites the previous value and it should keep adding as long as the id is the same.
Anyone help please.

 

[kaniz]

thanks Jerry, I know you should not store values in the table but some strange reasons the manager wants to see in there.

 

[JerryDennison]

The manager won't want to see it there if it's incorrect, I can assure you of that. Trust me, you DO NOT want to store the calculated results in your tables. This goes against the Forms of Data Normalization. Add the calculations to a query based on the table then use this query in the forms and reports that need the calculated results. This way, you need only create the calculations one time and can use them as you would any other field from the table yet still maintain the integrity of the db.

 

[Quehay]

Kaniz: The loop above won't run because it starts at the end point.

 

[kaniz]

it should be 1 to 10,

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim intcounter As Integer
For intcounter = 1 To 10
Me.Parent.TotalStatus = DSum("total" & intcounter, "run time", "[id]
= " & Me.[ID])
Next
End Sub

when I cut I pasted the zero was deleted. It still does not keep adding. Ok so If I don't store it on the table is there a way that I can display on the form. The problem right now is that it does not even display on the form the grand total value. I have the above code on insert, before update, after update, I am running out of options. Any other suggestions
Thank you so very much all of you.

 

[Rohdem]

Jerry is, of course, right. But I know that there are times when the best database design is not possible because of 'what management wants'. I also know that explaining to them the proper way to do it and convincing them to do it that way can be quite difficult.

Kaniz, I don't understand the purpose of your DSum statement. Your loop is simply reassigning a different value to the totalstatus box each time it goes through the loop. If you are trying to sum ten boxes on the current record, you need just a simple addition statement like you had in your initial post.

If you use the after update event of the form, I don't beleive your code will fire until the record is saved. If you want it to update immediately after you change a total, you need to use the after update event of the total box.
Mike Rohde
rohdem@marshallengines.com

 

[kaniz]

the problem is that for 1 ID there can many record codes

ID TOTAL TIME (FORM)
12345 35


ID CODE TIME (SUBFORM )
12345 A 15 record 1 on subform
12345 A 20 record 2 on subform
12345 A 20 record 3 on subform
12345 B 20 RECORD 1 ON SUBFORM
12345 B .5 RECORD 2 ON SUBFOR


So how do I tell the program to add all the codes from a to b to add it on the total box that is on the main form and not overwrite it as soon as I enter another code

sample form
id 12345
total box 240 minutes


sample subform

id 12345
code A
start 1 end 2
start 2 ebd 3

ID 12345
CODE B
START 2 END 3
STAR 3 END 4