Save Form Fields to Table 1

[Jima330]

Ok. Let me start by getting the controversy part out of the way. I want to save calculated fields from a form to a table. I know, I know....never save calculated fields as they may change and why would you want to when you can recalculate by running the form, query, report.....

Well let me take a few lines to explain and you tell me if I need to and how I can if you are convinced. I am not married to any of this so offer suggestions and alternatives if you like.

I have a Table for events that occur during an Employees day in a call center.

This table contains codes for Operator # and event type as well as date, start, end.....etc.

These events need to be sorted into 4 groups and totaled for a given date.

These 4 totals are then either added or subtracted from 2 constants (Log Time and Work Time....basically the difference between when you were here and when you were on the phones)

These 2 adjusted constants are then compared for Percentage of Log Time to Work Time.

I created a form to gather info from other tables (Employees, supervisors, Event codes....) to input into the Events table. No problem. I even got it to do Time Differentials and populate the table with whole numbers (no easy task for me and lots of help from this forum).

I then created 4 queries to return only the codes for each of the 4 groups. I then created a Form with 4 subforms based on these 4 queries. I then created text boxes on the main form to do the calculations from the 4 subforms. I was really on a roll here....doing great. I then created a form to Open the New form with it's subforms and tied the query parameters to that by Operator Code and Date. Still great ...I get only the info I need and the main form does the calculations (there are 7) and shows them right on the form. I am god at this point and dancing around the office making Tarzan noises......Then the boss says "Can you save those results by date into another table so we can query a running or monthly total by day and supervisor?" Tarzan shrank back into the forest and I was feeling considerably less divine. So that is my quandry. I need to save the Calculated Text boxes to a new table. I have succesfully tied the main form with the subforms to the new table without screwing up my calculations but cannot for the life of me figure out how I am going to save them to the table when I used the Control Source to do the calcs. I have tried to set the control source for the field in the table I want to save to and using a Macro to do the calcs in an On Current event, but this has not worked and only given me zeros instead of the calculated figure. So.....any thoughts folks? I have been at this for weeks and I just can't think anymore.

Thanks
Jim

 

[Trendsetter]

Right Jim let's get you back to being Tarzan!!!
Just leave your form as it was...working fine and we'll add some code to take the figures to the extra table without the aid of a safety net!
You need to identify a "trigger event" - that is something that will be done that is the end of the transaction so that we can run the code from that point - this could be a "save" button or a figure is altered/entered and in the after_update event, you need something like this:
Dim rst as Recordset
Set rst = CurrentDb.OpenRecordset("TblNewTableName&quot
rst.AddNew - this starts a new row in the table
rst!Field1 = Me.txtFigure1
..all the others go here.........
rst!Field7 = Me.txtFigure7
rst.update
rst.close
Set rst = nothing
rst.Fieldx is the name of your receiving field in the new table.
txtFigurex is the name of the field on the form that is donating the figure
TblNewTableName is the name you have given to your new table - I bet that was a surprise!


Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[Jima330]

Frank,

This worked magnificently! I must say that your answer is what a novice comes to this site for, Specific, explained and not too technical (and it works!!). Thank you very much and here's a star for your trouble.

Jim

P.S. AHHHHHHHHHAAAAHHHHHAAAAHHHHH (that's a Tarzan yell)

 

[Trendsetter]

I thought that might be Jane after the celebration!
Thanks for your kind comments!

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk