Solved Saving a revised version of a record and keeping the original (1 Viewer)

Dag Solder

New member
Local time
Today, 10:53
Joined
May 8, 2020
Messages
19
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
 

Attachments

  • Revise existing.accdb
    1 MB · Views: 18

arnelgp

error reading drive A:
Local time
Today, 18:53
Joined
May 7, 2009
Messages
10,855
the problem with my sample is that it saved automatically to
new revision and take you to that record.
 

Attachments

  • Revise existing.zip
    97.9 KB · Views: 24

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:53
Joined
Jul 9, 2003
Messages
12,096
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.
 
Last edited:

Micron

AWF VIP
Local time
Today, 05:53
Joined
Oct 20, 2018
Messages
3,471
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

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 04:53
Joined
Feb 28, 2001
Messages
18,334
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

CID VIP
Local time
Today, 10:53
Joined
Jan 14, 2017
Messages
14,016
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
 

Micron

AWF VIP
Local time
Today, 05:53
Joined
Oct 20, 2018
Messages
3,471
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

New member
Local time
Today, 10:53
Joined
May 8, 2020
Messages
19
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.
 

Manicduck

New member
Local time
Today, 10:53
Joined
May 22, 2020
Messages
29
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

New member
Local time
Today, 10:53
Joined
May 22, 2020
Messages
29
Sorry, code referenced is in post #2 of this thread

Quackers
 

Dag Solder

New member
Local time
Today, 10:53
Joined
May 8, 2020
Messages
19
Hey Manicduck,

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

Dag
 

Dag Solder

New member
Local time
Today, 10:53
Joined
May 8, 2020
Messages
19
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

error reading drive A:
Local time
Today, 18:53
Joined
May 7, 2009
Messages
10,855
so, we only need to change the Revision Number?
 

Attachments

  • Revise existing.zip
    95.9 KB · Views: 18

Manicduck

New member
Local time
Today, 10:53
Joined
May 22, 2020
Messages
29
@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!
 

Attachments

  • Revise existing.zip
    97.9 KB · Views: 20

arnelgp

error reading drive A:
Local time
Today, 18:53
Joined
May 7, 2009
Messages
10,855
see the revision on the Click event of "Add Revision" button.
 

Attachments

  • Revise existing (1).zip
    79.1 KB · Views: 25

Manicduck

New member
Local time
Today, 10:53
Joined
May 22, 2020
Messages
29
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

New member
Local time
Today, 10:53
Joined
May 22, 2020
Messages
29
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.
A16BF367-9797-4266-B980-3F94103C1B5D.jpeg
 

Users who are viewing this thread

Top Bottom