Revision History for Forms

[banpreet]

Hey guys,

My manager wants me to have a button on the main menu that will allow users to edit forms. I made a search form after the button is clicked and then the user opens up his/her form they want to edit. I want the users to edit it the information but when they make changes and after closing out the form, that administrators using the database will have access to the old form and the same form with revisions, so they can see the history and new changes. Is there a way this can be done? All forms are basically linked through an ID to look up, so when they look up the same ID, they would see two or maybe three of the same form (depending on how many times they changed information on the form) and the adminstrators can see all the changes on form, and then open up the same form but to see what information was put before it was changed.

Thanks,

All help is appreciated!

[orange]

I'm not sure I understand "allow users to edit forms"....
I would think you may want to provide a list of forms and options which the users may wish to use.
But allowing users to EDIT forms is a not common.

Perhaps an example of what your manager is describing would help readers.

[Micron]

I think the meaning is to edit records (using a form) and keep a version history of the record. We'll have to wait and see.

[banpreet]

Yes Micron is correct, users fill out a form and sometimes they will make a mistake so once they go back to that form and make a change, administrators will have access to see the old form and the new revised form the user filled out. Thanks! I have the form linked to a parent ID, so they can just look up the form through that ID. I was thinking I should add another field that is auto number with a button right next to it on the form so when the user clicks on it, the number will go to 1 to 2, so when they look up the parent id, they will see two forms with the number 1 and 2 and look at the history, I haven't tried this out but let me know your thoughts!

[Micron]

administrators will have access to see the old form

It is one form. In your case, there is no New/Old. It is the record that is new or old or edited. It may sound like a minor point but it does cause confusion here.

If every field has the potential to be altered and admins want to see any and every such alteration, you'd need to keep the entire record again, with the variances. Your table design would have to allow many fields to contain duplicate data. This can cause query issues later, such as not being able to perform some updates via forms, or slow performance. If only certain fields could be altered, you could keep only those parts in a revision history table (tblRevHistory).

In either case, at least one field in the record would have to remain constant or else it will never work (this might be what you refer to as parent ID). This field should be indexed as well (and no dupes), to try to avoid the issues I just mentioned. You'd need to lock up the form control for this 'constant' field during the edit process to prevent alteration of it and so that all of the versions of the record could be presented in a manner of your choosing by grouping on this constant.

One way would be to use the same form (it would need record selectors and navigation controls enabled in order to cycle through the variations) but this may not be apparent to some users that there are multiple versions of the record. Another method would be a subform datasheet, either in a new form or by adding this to the one you already have. Without being able to see the database, I'm not sure if either is really suitable and others may have better ideas now that we know what we're dealing with. Regardless of the approach, the relevancy of the records must be accomplished by grouping on the aforementioned constant.

Of course there are cases for wanting to keep a revision history, but to do this simply because someone made an error and someone else wants to be able to track it sounds kind of Draconian. As you may be realizing, providing this process capability can be a lot of setup work for which I've only scratched the surface (especially for a novice) so there ought to be a compelling reason, such as an audit trail to satisfy some financial, environmental or quality system compliance.
Edit:
Perhaps it is enough to record who made a change to a record rather than capture everything that changed? This would be far simpler.