Copy Record data into Two Tables

[BShick5088]

Hi,



I have a hand-me-down DB that tracks project data for our company and I need help to push a date entered on one form attached to a table into a different table. My thinking was to add VB code to the "After Update" on the field that would then push the date entered into the Master table record with the same project number. This is bacause the DB was built as several different department specific tables using the ProjectID as a primary key and the frmStatus page is based on the tblMain where all the main data should be kept.

Let's say I have a table called tblReproduction with a date filed called txtCompletionDt, what I need to do is after the date has been entered push a copy of that date over to the tblMaster into the ProjectCompleted field, so it will show up on the reports as a completed project.

It sounds like there should be an easy way to do this in the VBA behind the form, I just can't seem to find a good example?

Thanks....!

[June7]

No good example because saving data dependent on other data is generally considered bad practice.

The AfterUpdate event of txtCompletionDt would work. But what if user aborted all data entry/edit for that record? Now the entry in tblMaster is not correct. Try instead the AfterUpdate event of the form. When tblReproduction record is committed the update to tblMaster will take place.

CurrentDb.Execute "UPDATE tblMaster SET ProjectCompleted = #" & Me!txtCompletionDt & "#"

[BShick5088]

CurrentDb.Execute "UPDATE tblMaster SET ProjectCompleted = #" & Me!txtCompletionDt & "#"[/QUOTE]

I don't quite understand how the # was able to work and pipe in the project number, but it does...

Thank you Soooo much...!
That did the trick.
You are so cool...!

[June7]

Ooops, my suggestion was incomplete and should not have worked properly.

The # is just to delimit a date type value. The code actually should have updated EVERY SINGLE RECORD in tblMaster because I forgot to show the WHERE clause.

CurrentDb.Execute "UPDATE tblMaster SET ProjectCompleted = #" & Me!txtCompletionDt & "# WHERE ProjectID=" & Me!ProjectID