Archiving text Field

[SVNTY7]

I am trying to create an archive table for a text field that would capture the data prior to a change/update to the field.

example: ["Acquire CIHE accreditation..."] has been changed to
["Maintain CIHE accreditation..."].

I have tried to create an append query, that runs when there is a change to the field, but this appends all records in the table, not the specific field. I would like this action to archive the data in the field automatically anytime there is a change with out a message stating that "you are about to append records...".

Can somebody help?

Thanks

 

[Len Boorman]

Not exactly sure this will work but if you set up the append query with a criteria being the name of the field on the form brfore changing (something like Forms!DataForm.FieldName...Using the Builder) and fire the query on something like the Got Focus then with a bit of luck you will only append the record you want to append. As for shutting of the Append Message Tools/Options/Edit/Finf/Confirm action queries to be unchecked.

HTH

Len B

 

[The_Doc_Man]

Dear 77:

Unfortunately, you have stumbled across one of the ways in which Access is NOT like one of the bigger players in the database management field. It does not have an auto-journal feature. If you want archive-style journals, you have to write them yourself.

A further consideration is that you can only do this journaling from within forms (because you need to do it through Event code), so you have to restrict your users to enter through a switchboard form and they can only see the forms that are activated from the switchboard.

If you want to do general journaling, you have to scan every control on the form to compare .Value to .Oldvalue to see which ones changed, the log those changes along with the name of the field. If you want to do selective journaling, then you have to write code that tests specific fields for having been updated.

In either case, you might wish to put this in the Form_BeforeUpdate routine. (_AfterUpdate won't do because after the update, .Oldvalue and .Value are the same, I think.) If the event code detects the condition to allow the update, then you would call whatever journaling routine you write. As to how you do it, that is your call.

Resources you might need include the Now() function to provide a time/date stamp on the event and the CurrentUser() function to record who is doing the update. The latter is only valid if you are using workgroup security. Other articles in this forum describe ways of finding your network ID, which might be good enough if you aren't using the workgroup stuff.

Since this is on a form, you can also use the me.controls collection to step through all the controls that can be updated. Look up Controls Collection in the Help files. They might have some examples that would be useful.

You would have to test the .ControlType of every form, perhaps with a big Select Case statement, so you could test only the items have have values. For example, if the control type is acRectangle, you don't have to test it. But if it is acTextBox, you do. Look in the object browser, Access library, module acControlTypes for a complete list of recognized control types.

Finally, it is up to you as to how to write the journal. You could write to a recordset if you wished. That way, you can run fancy reports off the journal entries. Or, you can open an external file for Append and write to it via PRINT statements. Six of one, half dozen of the other. I use a journaling table but eventually I clean it out by exporting it to an external file anyway.