create a history of changes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I suspect the answer exists somewhere but after half-an-hour I haven't found
it yet, so if it's a duplicate point me to the right place. :-)

I need to keep a history of changes to a record. I can do this in Visual
Basic with an unbound form but the overall process is complex and my time
frame is tight so I'm hoping I can do it with a bound form. In examining my
design I've got to check for 5 scenarios on my save, in looking at it a bound
form with 3 bound sub-forms could do the same job and be conceptually simpler
and take a day or so less to produce.

What I need to do is display the current record for the user to edit. When
they are done editing I need to mark the former current record as a history
record and save the edited record as the current record. Essentially making
the edited record a new record and keeping the original record intact. To
tell the records apart I have a current record flag (boolean) and I use a
sequence number. So all records in a history sequence share the same primary
ID and each record created over time increments the audit number field. So
record 1 comes in and is record 1.1, it's edited and adds record 1.2, and so
on. Yes I could dispense with the flag and just look for max audit number,
I've found the flag to be a convenience when I just want the current record.

So, can this be done with a bound form? So it saves when the customer clicks
the next or previous buttons or closes the form. Or should I slog through the
VBA method?

Thanks
Mark
 
MarkInSalemOR said:
I suspect the answer exists somewhere but after half-an-hour I
haven't found it yet, so if it's a duplicate point me to the right
place. :-)

I need to keep a history of changes to a record. I can do this in
Visual Basic with an unbound form but the overall process is complex
and my time frame is tight so I'm hoping I can do it with a bound
form. In examining my design I've got to check for 5 scenarios on my
save, in looking at it a bound form with 3 bound sub-forms could do
the same job and be conceptually simpler and take a day or so less to
produce.

What I need to do is display the current record for the user to edit.
When they are done editing I need to mark the former current record
as a history record and save the edited record as the current record.
Essentially making the edited record a new record and keeping the
original record intact. To tell the records apart I have a current
record flag (boolean) and I use a sequence number. So all records in
a history sequence share the same primary ID and each record created
over time increments the audit number field. So record 1 comes in and
is record 1.1, it's edited and adds record 1.2, and so on. Yes I
could dispense with the flag and just look for max audit number, I've
found the flag to be a convenience when I just want the current
record.

So, can this be done with a bound form? So it saves when the customer
clicks the next or previous buttons or closes the form. Or should I
slog through the VBA method?
Click to expand...

VB is what you will have to do and you need to check for a new record and
decide how to handle a situation where the user makes a change, then changes
it back to the original.
You might also ask up frount if they want a "secret button" that allows a
change without creating a new record.
Be aware that if a subform is involved moving to the subform saves the form
record.

I ended up using two fields as the key and displaying them with the period
for the additional records.
It makes it easier to look at all records with the same primary key.
 
Thanks Mike I was afraid that was going to be the answer. And thanks for the
tip about it updating when jumping to sub-forms I can think of another app
where that would not be desired.

Mark
 
Back
Top