VBA - Change date but keep history in another table

[zipaway]

Hello I'm having a lot of trouble figuring this out.

I have two tables:

Events
Event_ID
Event_Description
Day_Number (An event can span over multiple days)
Date

Change_In_Date
Change_Number
Event_ID
Day_Number
Current_Date
New_Date



I want to be able to have a form table such as the one below, where I can see all the planned days for an event with their current date and I would just enter the new date and hit a button and the Events table would be updated.



Recap

• Have form with above table showing all Event Dates for a particular event
• Form would pick up the 'Current_Date' from the Events table and apply it to the Change_In_Date table when new change is created
• Once new dates are inputted a button is pressed and the new dates are applied to the Event table

Please give me some direction on how to do this, or if there is a better way to do this.

[John_G]

The Change_In_Date table won't (or shouldn't) have any records for an event where dates have not been changed. My suggestion would be to have a form based on the Events table instead of the Change_In_Date table. You could then add a record to the Change_In_Date table (using VBA) whenever a date is changed - the after update event of the Date field (you should rename that field - calling it "date" could cause problems) in Events would work. You can use the oldValue property to determine what the previous value was.

You could use another form to show you all the date changes for particular event.

HTH

John

[zipaway]

The Change_In_Date table won't (or shouldn't) have any records for an event where dates have not been changed. My suggestion would be to have a form based on the Events table instead of the Change_In_Date table. You could then add a record to the Change_In_Date table (using VBA) whenever a date is changed - the after update event of the Date field (you should rename that field - calling it "date" could cause problems) in Events would work. You can use the oldValue property to determine what the previous value was.

You could use another form to show you all the date changes for particular event.

HTH

John

This makes sense but it wouldn't work for my purposes, I would like to be able to review the change in date prior to hitting a button and updating the Events table with the new dates for each day_number. Any ideas?