Solved Saving a revised version of a record and keeping the original

[Dag Solder]

Hi all,

This is my second post on the forum, and I am very much a novice. I hope that the following explanation is sufficient.

I have a project where a form allows the user to record a series of events. Many of these events are chosen by cascading combo boxes, whilst others are just text input boxes. This side of the project is now up and running with the kind help received from this forum.

The next step of the project would be to allow the user to modify certain aspects of the initial record, probably only the text input boxes.

I had thought of duplicating the original input form and saving it with a different name and then disabling / removing the selections I don’t want the user to touch, however, this would not really serve my purpose.

What I would like to achieve is to retain the original information, but to have a revised record with a revision number, so that it is possible to review all changes to the record.

I don’t want to record usernames or computer information, but just updates (revisions) to the original entry.

I have attached a much stripped down version of the project, please excuse the data, but it is just for illustration.

As you will see, I have 6 records in the table, if for instance I wanted to change the details in record ID 4, I would like to retain the original record and also a revised version. The unique identifier would be “Report Number” so for record ID this would be: “Nuisance Report 2”

Another part of the project concatenates the manually entered report number and the manually entered revision number, but ideally, I would like the revision number to be auto generated each time a revision is saved.

Is this possible and how would you go about it??

As before, any help would be gratefully received.

All the best, Dag

 

[arnelgp]

the problem with my sample is that it saved automatically to
new revision and take you to that record.

 

[Uncle Gizmo]

There is a common process applied to many databases called "Audit Trail". This consists of some code in your form which monitors for changes to records (it operates on the controls "Old Value") and stores the changes in a separate table. The code and everything to do this is readily available and discussed in many threads on the forum. I think it might be an ideal solution to your problem.

 

[Uncle Gizmo]

Here's a link to an "Audit Trail" solution:- Audit Trail table to work on both the Main form and Subform

 

[Dag Solder]

@arnelgp & @Uncle Gizmo, thank you both for your response, much appreciated

I will see how I can work your suggestions into my project

Stay Safe

Dag

 

[Deleted member 147267]

A minor point - an unbound control has no OldValue property IIRC so if something you end up doing doesn't seem to work, keep that in mind and maybe ensure what I said is accurate.

 

[The_Doc_Man]

Absolutely true, Micron, and you DO recall correctly. However, as the OP's question was about saving the original record, I must point out that if a field is unbound, it would not be in the original record anyway.

 

[isladogs]

Another approach is possible if your field is long text / memo datatype. If so, you can save different versions of the field data using the column history property. There is no limit to the number of items that can be saved for that record
See http://www.mendipdatasystems.co.uk/column-history-memo-fields/4594523656 for more information

NOTE the approach does have some drawbacks in that the column history is saved in a deep hidden sysem table

 

[Deleted member 147267]

However, as the OP's question was about saving the original record, I must point out that if a field is unbound, it would not be in the original record anyway.

There could still be a calculated field on an bound form so my comment was meant to be general in nature. I'm merely saying that if such an issue arose, keep it in mind.

 

[Dag Solder]

Thanks All!

I am still looking at the various methods offered, although I am having a couple of problems with the code for the audit trail.

Still researching, but again, thanks for all of the input.

 

[jdraw]

You may find this thread on Data Macros helpful. There are limitations re attachment, multi-valued, long text fields that are not handled with data macros.
Good luck. An interesting project.

 

[Manicduck]

the problem with my sample is that it saved automatically to
new revision and take you to that record.

Hello,

please excuse me if jumping into someone’s post is not allowed....
I would like to do something similar to what the OP has asked, I have already looked at the Audit trail and this is not what I want.

@arnelgp has suggested some code which would suit my project, however, from my limited understanding of VBA, in this example, it seems as if the [Revision Number] is driven by the [Report Number], such that when a revision is submitted, it also changes the [Report Number].I am trying to achieve something that is almost the same as @arnelgp has suggested, except that my report number must remain as entered (or appended with a dash number) and all revisions to it should change so that all revision to original report could be sorted by that revision number.

for instance, with the supplied code, there are several Nuisance reports, each with a different report number; Nuisance 1, Nuisance 2 etc. When submitting a revision, the code then adds a new entry in the table with the next new Nuisance
Report number, so if there were 3 existing reports, a revision would return Nuisance 4. I am trying to make my project as user friendly as possible and will be using separate forms for the initial report and editing (revision) function.

I have experimented with using different inputs for the report number I.e. Nuisance Report 1 1, Nuisance 1 - 1, but each of the ways I have tried returns an error, amongst those received: Run time error 94. The report number would in my case refer to a manually entered value derived from a register, I have approximately 10 different report (injury) headings with a three digit numerical value, eg head injury 031. I am really new to Access, so please excuse the poor explanation and sorry for hijacking the post OP

 

[Manicduck]

Sorry, code referenced is in post #2 of this thread

Quackers

 

[Dag Solder]

Hey Manicduck,

No worries, I don't mind as long as the Mods don't!

Dag

 

[Dag Solder]

Hey Manic,

I just looked at the code posted by arnelgp and commented out the following line in the form code:

' strMaxReport = Replace(strMaxReport, intMaxNum & "", (intMaxNum + 1) & "")

It seems to work how you describe and has given me some fresh ideas

 

[arnelgp]

so, we only need to change the Revision Number?

 

[Manicduck]

@arnelgp Thank you very much for you answer, I have tried to modify your code into my project.

The serial number and report number work correctly, so thank you!, but there seems to be a problem with the info in the boxes named Text 1, Text 2 and Text 3. I entered the info using the new record form and put Info 1 in all of the text boxes. When I updated using the Revision Form, it seems as if the info has been overwritten, at revision 2 txt was changed by me to info 2.

do you have any ideas? Once again, thank you for your very kind help!

 

[arnelgp]

see the revision on the Click event of "Add Revision" button.

 

[Manicduck]

Hey @arnelgp, thank you so much for your answer and for taking the time to look at this for me. I really appreciate your efforts.

I will do some testing later.

once again, thank you

 

[Manicduck]

I am afraid that there still seem to be an Issue, in that each time revision is posted, it overwrites the previous info as well as copying itself as intended.
I have replaced the data in the op’s table to more easily understand what is happening, I made 6 entries, Accident 1 through Accident 6. I revised accident 2, 4 and 6 to rev 2, then revised accident 4 & 6 to rev 3 and finally accident 6 to rev 4. You will see from the image what happened.