Archive memo fields

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

Guest

Could you give me an advice on how to achive this? In the form I have a
couple of memo fields that user would like to "archive/save" whenever or each
time those field values changes. Is this even a good idea?
 
Whether it's a good idea depends on whether there's a need for this kind
of audit trail.

The way to achieve it is by using a separate table for each of the
present memo fields. Assuming:

-the existing table is tblMain
-tblMain's primary key is the field PK
-the two memo fields are M1 and M2

you need a table tblM1 with fields like these:
-PK* (foreign key into tblMain)
-MemoTimestamp* (date/time field)
-M1

The primary key of M1 is the two fields marked *.

On your form, instead of the present arrangement of (I guess) a textbox
bound to M1, have an unbound textbox, with code in the form's Current
event procedure that uses DLookup() to fetch from tblM1 the most recent
record whose PK field matches the PK now displayed in the form.

To create a new record in tblM1 whenever the memo is changed, use code
in the textbox's BeforeUpdate event (which will only run if the
textbox's contents have been altered). You'll need to think carefully
about the sequence of events and exactly what behaviour you want, but
off the top of my head I'd start by trying something like this:

1) Use the textbox's BeforeUpdate event to set a form-level boolean
variable if the memo has been edited.

2) In the form's BeforeUpdate event, include code that clears this
variable if the (form's) update is cancelled.

3) In the form's AfterUpdate event, have code that tests the boolean
variable and (if it is set) appends the current comments of the textbox
as a new record to tblM1 (with the current value of PK, and Now() for
the timestamp).
 
John,

Thank you very much for your great tips. I have been testing all day. I'm
getting it there but not quite. Next step is to set the flag whether it has
been edited or not.
 
For the flag, I suggested the textbox's BeforeUpdate event procedure.
That's wrong, I fear: an unbound textbox doesn't have anything to
update.

Instead, you could set the flag in the textbox's Change event procedure;
or - in the *form's* BeforeUpdate procedure - compare the actual value
of the textbox with the latest value stored in the table.

Using the CHange event is simpler, but has the disadvantage that if the
user makes a change and then undoes it the flag will still be set.
 
Back
Top