Update existing From record with changes on new To record

Update existing From record with changes on new To record

Post by rharpe » Wed, 30 Jul 2003 02:04:34


I need to create a history record and update a current record from one
input form.
The two tables are joined with a query and the table names and fields
are as follows:
One - Many
tblPartsCurrent tblPartsHistory
Color
Material
Finish
Department ToDepartment
Division ToDivision
SKU ToSKU
PartDescription ToPartDescription
GeoLoc ToGeoLoc
WhslPrice ToWhslPrice
RetPrice ToRetPrice
ReasonForChange
RecordDate

I have an input form which is designed to show changes to one or more
of the fields. (Color, Material, and Finish can not be changed.) After
input on the 'To' side of the input form, I print it once then I need
to update the appropriate fields on the 'From' side in tblPartsCurrent
record from the new record in tblPartsHistory.

As an example, the input form may show all fields for a part in
tblPartsCurrent but only 3 fields are input as changes in a new record
in tblPartsHistory (Let's say, ToDepartment, ToDivision, and SKU - all
other "To" fields are left blank in this example).

I then print out the form which shows the From and To listed as
columns. Now I need to update tblPartsCurrent with the changes. I'd
like to place an 'Update Current Record' button on the form. I've
envisioned an 'On_Click' event which writes something like:
[tblPartsCurrent]![Department] = [tblPartsHistory]![ToDepartment]
[tblPartsCurrent]![Division] = [tblPartsHistory]![ToDivision]
[tblPartsCurrent]![SKU] = [tblPartsHistory]![ToSKU]

However, I don't know which fields will be updated on any one form.
The history record could contain null or real values for any of the
'To' fields.

I am a novice when it comes to code. Can anyone give me advise as to
how to determine which fields are not null, and update those into the
table tblPartsCurrent?

Any help would be most apreciated.

Rick