Query a record when a change is made

[jp3scr]

Hello.

I have 2 tables PT and PT History. Project Name is the primary key on the PT table and an autonumber ID is the primary key on the PT History Table.

Users update records in the PT table via form. When they complet their update they click a save button which saves the record and runs an append query from the PT table to the PT History table, making a new record in the PT History table every time the record is updated.

My Issue: When Record A is updated a new record is appended to the PT History table, however if Record B isn't updated a new record is also appended to the PT History Table.

My Desire: I only want the record to append to the History table if a change is made to any of the fields.

Thoughts??

 

[Pat Hartman]

Creating history is a two part operation. In the BeforeUpdate event of the form, you need to build the history record using the OldValue properties of all the controls and save that for use in the AfterUpdate event. In the AfterUpdate event, you know the record has been updated and the update was successfully saved so you can take the saved data and append it to the history table.

You appear to be doing this in the click event of your save button but since the user can click that whether or not he changes the record, it is not the correct place to create the history log. You also don't know at that point if the record will actually pass the validation and RI rules so that it really gets saved.

 

[llkhoutx]

A change may not mean a change. That is, a feild can be changed, then changed back to its original value. The OnCurrent event indicates new record; the dirty property also indicates a change.

You might have to save and check each field for a change. I presume that the PT History table is has a one to many relationship with the PT table.