Tracking Date changes

[Homegrownandy]

When we have a 'cancelation' this needs to be entered into the system. I have everything in place to deal with this but I have a question...



How do I deal with the old scheduled date? I want it to be put in a new field automatically.

I want Old_Scheduled_Date in NotesT table to be populated automatically from Scheduled_Start in JobsT table.

This can happen when the user clicks to update a cancelation note.

additionally I would like Scheduled_Start to be cleared. So the job no longer has a start date.
Thanks in advance, Andy,

[ItsMe]

...
How do I deal with the old scheduled date? I want it to be put in a new field automatically.
...

Sounds like you have a field/column in a table that stores a date. When a cancelation occurs for a particular record, the field is updated. When a subsequent cancelation occurs, the same field is updated. In order to log all history, you will need a separate table to log the events. Each cancelation would create a new record within the new table.

This separate table might have four columns or less. I would recommend an autonumber field/column even if you do not see an immediate need for it. Other columns would be FK(s). You might want to include a key value that indicates who edited the record.

[ranman256]

run an append query. This query will copy the [old date] to [new] date.

[Homegrownandy]

its me - thanks man, this s already set up. Cancelation in my system is type of "note" because the user can comments. This is where the data will be stored, I just didn't know how to move it on an event.

Ranman - I never knew what an append query was. I couldn't find anything on google relating to my issue but that's because I was looking for the wrong things most likely. Ill look into append querys over the weekend.

I'm out of office now but thanks for the replies guys it gives me something to think about or look at.

[ItsMe]

I may need a better understanding of the structure in order to be able to help. I am still on the idea you have a single record and there is a field for Cancelation (Date type, Note type, Text type, etc.) within this single record. If an update to this field is causing historical data to be erased, you will need to log the historical data in another table. If the item you are struggling with is how to append a record via an Event, you would probably want to include an SQL statement within a VBA string to Execute an Append query.