Adding values from fields in table1 to fields in table2.

[BPBP]

Hello,
I have 2 tables namely "tblUpdate" and "tblRecord". These 2 tables are
joined by a primary key known as Job_No with referential integrity set.
(tblUpdate - Many, tblRecord - One)

I have a form named "frmUpdate" where the user key in Job_No, followed by
manhours into 7 different fields named A, B, C, D, E, F, G into tblUpdate.

After this form is fully keyed, the user exits the form by clicking an exit
button. On exit the values of these 7 fields has to be ADDED to the value of the existing 7 fields of the row(reference by Job No) on "tblRecord".

How do I do this?
I figured I need to put in 1 or 2 lines of code on the on_exit(or is it after-update) event of the form.


Thanks! BP

 

[RuralGuy]

So you have two tables that each have these 7 fields and you want them to be the same? What type of values are in these fields? It sounds like they should be records rather that fields.

 

[BPBP]

They are not records. The fields(columns) are numbers. I have controls (textboxes) in the form where these numbers are keyed into the 7 fields. Upon exit of the form, the values in this 7 controls has to be added to the 7 fields in another table.

They are ADDED to the 7 fields in another table.

 

[RuralGuy]

By golly that is exactly what you said in your first post. Sorry about that. Not enough coffee yet. Assuming the two tables are joined in the RecordSource of your form, you can add the fields together in the BeforeUpdate event of the form.

 

[BPBP]

Right, that was what I was trying to ask. How do I add the fields together... in vba code ?

[tblRecord].[A] = [tblUpdate].[A] + [tblRecord].[A] ???


Doesnt seem right though...As they are joined by the Job_No, how do i get the values to be inputted into the right row(job_No) and column(A) on another table (tblRecord)

And I couldnt use the forms event, i need to put it in the event of the save button. Becos i dun't want the values to be added if the form is closed in some other manner.
Is it better to do this in the after update of the button?

Thanks, hope i'm not confusing u any further.

 

[RuralGuy]

How do you plan to stop the save if the form is closed some other way? If the record is saved then there will *always* be a BeforeUpdate FORM event just before the record is saved.

 

[BPBP]

lol, i dun't know how to explain it any clearer. If the form updates it doesnt matter. The entries into the form controls go into Table A which is the source of the form. I'm talking about adding the values of these controls into another table B fields.

Lets ignore the above and start afresh.

I think i have to make the question really simple(i try haha)

How do I code to add the value of this form Control named "A" to a field named "B" on another table named "C" where the common field named "D" is the same.

Does this even make sense?

 

[RuralGuy]

Join the two tables in a query for the RecordSource of the form so that both tables are available to the form. Then in the BeforeUpdate event of the FORM put:
[TableBFieldA] = [TableBFieldA] + Me.ControlA
[TableBFieldB] = [TableBFieldB] + Me.ControlB
...
Using your control names and field names of course. You are going to want to make sure there is a valid value to add in the control.

 

[BPBP]

Join the two tables in a query for the RecordSource of the form so that both tables are available to the form. Then in the BeforeUpdate event of the FORM put:
[TableBFieldA] = [TableBFieldA] + Me.ControlA
[TableBFieldB] = [TableBFieldB] + Me.ControlB
...
Using your control names and field names of course. You are going to want to make sure there is a valid value to add in the control.

Thanks. That works great.

 

[RuralGuy]

Glad I could help.