Way to create a copy of a record before it gets modified

[zach9208]

Hello Access Experts! I have a database with several tables. I have a form that I have setup based on a data table called tblForecastMaster. I need to be able to create a copy of each record that gets modified.

The reason I need to do this is because we need to show the original record that we had with the original forecast, however, there are times that we need to make an adjustment to this record such as a changing some date fields and/or amount. I need to capture both the original as well as the new modified record

I am looking to get ideas how I could best do this so the process is user friendly and they do not have to copy the original record manually and pasting, then make change. I am hoping we can do something with the form but I could not think how that would work or maybe I need to add a macro. Or maybe an a append query, but I am unsure how it could figure out which record needs modified.

Any ideas of a good way to handle this? I am really looking for the easiest and most user friendly solution since several novice users will be using this database each month.

Thanks in advance!

 

[Micron]

Is this a properly split db where each user has their own front end? I'm guessing there's something about the record that would permit other records that are almost an exact duplicate (save for the altered fields) yet still be unique as a record. It could be done with an fe temp table, but to explain the process would require knowing more about the form. Does it open with only one record? Or perhaps it's a datasheet or continuous form? Or maybe it shows one record but has navigation buttons?

I presume this

I need to capture both the original as well as the new modified record

means store in a table.

 

[zach9208]

So the database has not been split. I am still unsure if it is necessary to split. I am willing to split it it would be helpful. Once I get the database in working order, I am going to have our IT group create a backend SQL copy of each of the tables. It sounds like they can make a daily copy and store it into our datawarehouse.

To answer your question about the form;I used the form creation wizard and it is currently showing 1 record at a time with the typical navigation arrows to move on to the other records. The wizard is using using the table as it’s direct source.

Is this a properly split db where each user has their own front end? I'm guessing there's something about the record that would permit other records that are almost an exact duplicate (save for the altered fields) yet still be unique as a record. It could be done with an fe temp table, but to explain the process would require knowing more about the form. Does it open with only one record? Or perhaps it's a datasheet or continuous form? Or maybe it shows one record but has navigation buttons?

I presume this means store in a table.

 

[Micron]

You run the risk of corruption using a single shared file to say the least. More on splitting at the end.
As for your quest, it's doable but isn't a real easy task to cover all the potentials. The problem with your current design is that you've bound the form to a table. If you edit any control on that form, then navigate to a new record, the change has been saved on the original data. You have to exert more control over the user and process if you want to retain original data.

The simplest for you would be to have 2 forms. Your original, but either based on a query whose read only property is set to yes, or opens with all the controls disabled if you must base it on a table. Many advanced designers do not base forms on tables. A button on the form would open a second form which is of the single record type; i.e. it has no navigation controls and is not a datasheet or continuous form & Allow Datasheet view is set to no. The form would have all the same controls as the first and would load with the single record that was on the first form when the button was clicked, but this record would be editable. I would also make the first form invisible when the 2nd is open, to prevent any issue that could arise if they go back and navigate to another record at this point. After the edit, closing the 2nd form writes the record to a table and makes the second form visible again.

How to load the second form or where to save the record? You didn't comment regarding any unique field, so I don't know if you're storing the edited record in the same table or a different one linked to the original by the original's primary key. There's also more than one way to create the record for the 2nd form, but I think for you the easiest would be a temp table that you load with the record using an append query. You'd flush this table (delete all records) either before and after the edit process (before in case there's anything left over from a prior incomplete or failed attempt), append the desired record and do the edits. When the 2nd form save/close button is clicked, you'd append the record somewhere, delete the temp, close the edit form and show the 1st form again. The other would be using a recordset clone of the 1st form but it's more involved. As for appending that edited record, you might need protection against a process by which an append fails and the temp is deleted. Using the CurrentDb.Execute method (along with dbFailOnError) would permit you to prevent that.

I see problems in trying to do any of this without splitting whereby each user has their own front end (fe) copy connected to one back end set of tables. Most likely you'd get one or more of about three possible system messages advising that there are conflicts of one sort or another. Having concurrent users in a split db situation where they all access the same fe isn't really an option either. This would create a situation where they are all writing to a temp table (assuming that's the design solution) thereby mixing up record edits and deletions.

 

[zach9208]

Thanks, Micron for the advice. I am trying to absorb your recommendations as I am kind of a novice developer. I am not very experienced with temp table creation. So I will see if I can carry out your suggestions tomorrow when back at work.

So here what I think you are saying but I am sure I am misunderstanding some of this.
1) Create a new query that is based on tblForecastMaster. Set this to read only.
2) Recreate the form to be based on this newly created query instead of the table itself
3) Create a separate form and query to actually be used for editing records. Embed a button in the view-only form that takes users to the specific record to be edited in the modify version of the form
4) Create another query (this time an append query) that returns only the records that mismatch between the two queries based on the two forms. This append query will then append the results back to the master.

To answer your question about the key. I have an autonumbering primary key that access is assigning. The data I am working with does not have a field that is populating consistently enough to be used as a unique identifier.


I will look into splitting out the tables to a backend. Any good website or video that explains how to best split a database?

You run the risk of corruption using a single shared file to say the least. More on splitting at the end.
As for your quest, it's doable but isn't a real easy task to cover all the potentials. The problem with your current design is that you've bound the form to a table. If you edit any control on that form, then navigate to a new record, the change has been saved on the original data. You have to exert more control over the user and process if you want to retain original data.

The simplest for you would be to have 2 forms. Your original, but either based on a query whose read only property is set to yes, or opens with all the controls disabled if you must base it on a table. Many advanced designers do not base forms on tables. A button on the form would open a second form which is of the single record type; i.e. it has no navigation controls and is not a datasheet or continuous form & Allow Datasheet view is set to no. The form would have all the same controls as the first and would load with the single record that was on the first form when the button was clicked, but this record would be editable. I would also make the first form invisible when the 2nd is open, to prevent any issue that could arise if they go back and navigate to another record at this point. After the edit, closing the 2nd form writes the record to a table and makes the second form visible again.

How to load the second form or where to save the record? You didn't comment regarding any unique field, so I don't know if you're storing the edited record in the same table or a different one linked to the original by the original's primary key. There's also more than one way to create the record for the 2nd form, but I think for you the easiest would be a temp table that you load with the record using an append query. You'd flush this table (delete all records) either before and after the edit process (before in case there's anything left over from a prior incomplete or failed attempt), append the desired record and do the edits. When the 2nd form save/close button is clicked, you'd append the record somewhere, delete the temp, close the edit form and show the 1st form again. The other would be using a recordset clone of the 1st form but it's more involved. As for appending that edited record, you might need protection against a process by which an append fails and the temp is deleted. Using the CurrentDb.Execute method (along with dbFailOnError) would permit you to prevent that.

I see problems in trying to do any of this without splitting whereby each user has their own front end (fe) copy connected to one back end set of tables. Most likely you'd get one or more of about three possible system messages advising that there are conflicts of one sort or another. Having concurrent users in a split db situation where they all access the same fe isn't really an option either. This would create a situation where they are all writing to a temp table (assuming that's the design solution) thereby mixing up record edits and deletions.

 

[xenou]

Before you get to far, think about what you are going to do with the results (i.e., the copied records). Frankly, I don't see any easy way to make use of that data unless single rows you can look back at are of usefulness to you. And what will you do once you have hundreds of "changed" records? Will that become a swamp of unpleasant uselessness - seems like it would pretty quickly become awkward to work with all that data. In short, what's the game plan for the saved copies of changed records?

Also btw you could probably just put an sql statement into your before_save event to save an audit trail of changes, but still don't see where you would go with this.

Note that a split database just means your tables (and only tables) are in one database (the Backend version - BE) and all your forms, reports, and queries are in another database (the Frontend version - FE). This keeps your data safe in case there is a snafu in one of the forms, reports, or queries. Or another way of putting it is that with a split database, the risks of corruption will mainly be in the FE, not the BE, which helps protect your data, among other advantages.

 

[zach9208]

So let me explain the process because it is pretty unique. The database I am creating is managing sales data. Most of the data is provided directly from sales rep. Most of the data is guestimates of how much they expect to fund and what quarter/year. These sales forecasts are run in a weird time schedule (out of my control) Say in the July Forecast, the sales rep says they are bringing in 10M this quarter. They may make a revision if they learn that only part of this commitment funds in the corresponding quarter say 50M, so they will want to adjust both the amount and the funding quarter corresponding. The remaining 50 will fund in the next quarter We want to maintain a record of the original forecasted row so we can see how the forecast changes over time. The data in the master table will be queried to pull in certain forecasting periods and dumped into excel to be chopped up into a nice report using sumifs. We want to see how forecasts trend over time. It is helpful to see where reps are making bad estimates, ect.

I know it is really messy of a concept. I would provide you the schedule for when the forecast each forecast is run, but I do not have it on hand but Jan, Apr, July, Oct are when the forecasts are run and so depending on what month the data is provided there is always one month where adjustments would need made and again they want to see forecast over forecast.

Before you get to far, think about what you are going to do with the results (i.e., the copied records). Frankly, I don't see any easy way to make use of that data unless single rows you can look back at are of usefulness to you. And what will you do once you have hundreds of "changed" records? Will that become a swamp of unpleasant uselessness - seems like it would pretty quickly become awkward to work with all that data. In short, what's the game plan for the saved copies of changed records?

Also btw you could probably just put an sql statement into your before_save event to save an audit trail of changes, but still don't see where you would go with this.

Note that a split database just means your tables (and only tables) are in one database (the Backend version - BE) and all your forms, reports, and queries are in another database (the Frontend version - FE). This keeps your data safe in case there is a snafu in one of the forms, reports, or queries. Or another way of putting it is that with a split database, the risks of corruption will mainly be in the FE, not the BE, which helps protect your data, among other advantages.

 

[xenou]

I was just saying "okay you have saved a record... now ... how you are you going to create the reports that show changes over time?" Basically, think about the end goal. Imagine you have all your saved records (in some form). Will it work for your reports that show changes? Will it be something you can do quickly and efficiently? If not, then what way of saving those changes will work better?

Note: the idea here is just to avoid working on some solution to save the copies and then finding out later that you could have done it differently and better, or worse that you have to start all over again because it's not what you really need. If necessary, create some dummy "changed" records just from scratch, manually, and see how the reporting will work with those samples. Then imagine you have 50 times more records than your sample set - does it still do what you need? Picture next quarter, next year also - how does that affect the reports?

 

[Micron]

Xenou's last paragraph is particularly sound in its suggestion. I focused on a possible solution rather than questioning the need, which is not always best. If there is one characteristic of the group of records that you can consider to be a constant, then a better solution might be to have a table for initial forecasts, which isn't allowed to be edited. You'd store edits in a separate table linked via that constant (perhaps it is a project number or salesperson id.

The closest I've ever come to dealing with such deviation data is an initial estimated cost of an order release in one table, and every change order was written to another table, but there was a common denominator (order number). Such an approach seems to mimic what you're after, but whether or not it becomes unmanageable as Xenou seems to be speaking of probably depends on the number of edits you guys intend to allow.

In post 5, the first 3 points are pretty much right. The 4th, no. Once the edit form is opened to the record to be edited (which was loaded into the temp table) the edit is made (against the temp table) and an append query writes it back to where I don't know. My suggestion would be to keep original forecast records separate from edits, but as I mentioned, there has to be a commonality between them. That could be the forecast record id, where that value is a foreign key in the edits table. So it might look like
[TABLE="width: 223"]
<tbody>[TR]
[TD]tblForcast[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ForecastID[/TD]
[TD]SalesRep[/TD]
[TD]ForecastAmt[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 224"]
<tbody>[TR]
[TD="colspan: 2"]tblForecastEdits[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EditID[/TD]
[TD]ForecastID_FK[/TD]
[TD]EditAmt[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]60[/TD]
[/TR]
</tbody>[/TABLE]
So forecast 1 by salesperson 11 went from 50 to 55 to 60. I figure you have a table for personnel, so their record id would be used here, not their names. BTW, a temp table is just a regular table. It's considered temporary because data isn't kept in it longer than for some operation like your request.
Whatever you do, don't consider adding extra fields in some table to hold revisions.

 

[zach9208]

I am still struggling with this. I cannot figure out how to setup my read-only form to open the currently viewed record in another form. Can you explain the specific steps involved in design view to create this functionality?

Xenou's last paragraph is particularly sound in its suggestion. I focused on a possible solution rather than questioning the need, which is not always best. If there is one characteristic of the group of records that you can consider to be a constant, then a better solution might be to have a table for initial forecasts, which isn't allowed to be edited. You'd store edits in a separate table linked via that constant (perhaps it is a project number or salesperson id.

The closest I've ever come to dealing with such deviation data is an initial estimated cost of an order release in one table, and every change order was written to another table, but there was a common denominator (order number). Such an approach seems to mimic what you're after, but whether or not it becomes unmanageable as Xenou seems to be speaking of probably depends on the number of edits you guys intend to allow.

In post 5, the first 3 points are pretty much right. The 4th, no. Once the edit form is opened to the record to be edited (which was loaded into the temp table) the edit is made (against the temp table) and an append query writes it back to where I don't know. My suggestion would be to keep original forecast records separate from edits, but as I mentioned, there has to be a commonality between them. That could be the forecast record id, where that value is a foreign key in the edits table. So it might look like
[TABLE="width: 223"]
<tbody>[TR]
[TD]tblForcast[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ForecastID[/TD]
[TD]SalesRep[/TD]
[TD]ForecastAmt[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 224"]
<tbody>[TR]
[TD="colspan: 2"]tblForecastEdits[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EditID[/TD]
[TD]ForecastID_FK[/TD]
[TD]EditAmt[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]60[/TD]
[/TR]
</tbody>[/TABLE]
So forecast 1 by salesperson 11 went from 50 to 55 to 60. I figure you have a table for personnel, so their record id would be used here, not their names. BTW, a temp table is just a regular table. It's considered temporary because data isn't kept in it longer than for some operation like your request.
Whatever you do, don't consider adding extra fields in some table to hold revisions.