Archiving records which have been edited/added/deleted

[zmbd]

Here's somethng I've whipped up in my test database.

I have a table named "tbl_events "
I made an empty copy of "tbl_events " and renamed it "tbl_audit" , change the [eve_id] from an autonumber to a number
Added two new fields [Audit_ID] as autonumber and primary key and [Audit_date] as a date field... should have added a third for the currently logged in user; however, that should be easy for you to do...

I have a form that is based on a query against the tbl_events with a control named "EVE_ID" and in that form the following code... :

Option Compare Database
Option Explicit
'
Private Sub Form_BeforeUpdate(Cancel As Integer)
'declare
Dim zdb As dao.Database
Dim zrs As dao.Recordset
Dim zssql As String
Dim zieid As Integer
Dim zddate As Date
'
'setup
Set zdb = CurrentDb
Set zrs = zdb.OpenRecordset("tbl_audit", dbOpenDynaset)
'
'set error trap
On Error GoTo z_bad_error_nonono
'
'set var
zddate = Format(Date, "mm/dd/yyyy")
zieid = Me![Eve_ID]
'
'build sql
zssql = _
    "INSERT INTO tbl_audit ( Eve_ID, Eve_Inv_ID, Eve_Sta_ID, Eve_Date, Eve_memo, Eve_Inls, Eve_WinUserID ) " & _
    "SELECT tbl_events.[Eve_ID], tbl_events.[Eve_Inv_ID], tbl_events.[Eve_Sta_ID], tbl_events.[Eve_Date], tbl_events.[Eve_memo], tbl_events.[Eve_Inls], tbl_events.[Eve_WinUserID] " & _
    "FROM tbl_events " & _
    "WHERE (((tbl_events.[Eve_ID])= " & zieid & "));"
'
'copy the current record to the audit table undo changes if there is an issue...
zdb.Execute zssql, dbFailOnError
'
'move to the last record in tbl_audit... should be the just created record
zrs.MoveLast
'
'open the record for edit and add the current date
zrs.Edit
zrs!audit_date = zddate
zrs.Update
'
z_clean_up_your_database:
zrs.Close
zdb.Close
Set zdb = Nothing
Set zrs = Nothing
Exit Sub
z_bad_error_nonono:
MsgBox Err.Number & vbCrLf & Err.Description
GoTo z_clean_up_your_database:
End Sub

Debugged and ran on 10 records in the test db w/o problems.

Now you have a copy of the old record with date changed... the rest is up to you :)

-z

[zmbd]

The one problem I have is that the "Reason for Editing" bit is entered using a InputBox. I don't like the look of it and the user can click Cancel which I do not want as it's necessary data.

Sorry missed that...
Either make a form that checks for null entry and locks the ok button until an entry is made...
or
place the input box in an infinate loop that checks for null or "space" and continues to loop.

I prefer the form as I have a listbox with the standard reasons; however I often use a combobox with the limit to list property set to false so that the user can enter a comment... in either case, there has to be a change in the control before the ok button unlocks and that change can not be a space charactor nor a null value or the button stays locked.
-z

[LeighW]

That looks great Z and a hell of a lot tidier I'll try your method now. The only thing is could I make the input form pop-up like the inputbox did as the update is about to occur? I take it it would be like a small data entry form which just writes to the other table (which would be great if it could do that as I could customise it a lot more).

[zmbd]

Yes,

With the form, you have to get the value back from it and have the code pause until the user supplies the information... this can be done by opening the form in dialog mode. Or you could have the form use a custom event, then we'd have to move some other code around... doable; However, If an input box will work, then I would use that... no need to re-invent the wheel. You can test the return from the input box in a loop and check for a non-zero length entry... even if it's just a few spacebar presses... at least the user had to press a key.

[LeighW]

After thinking about what we need archived and audited for future reports, we need need more than one text box to explain the reason for modification or adding a record. One for Reason (necessary), one for the Document used (Optional), one for the signifiance (yes/no) and if yes is entered then one for Impact.

This will then allow us to view why the changes that were made and the significance of the changes for audits and reports on how it is progressing.

Thinking about that data (which will essentially be a one to many with the main form) a dialog form that allows the text and yes/no fields to be tagged to the end of the record entered into the archive table (which is occuring on the BeforeUpdate event of the main form as above) would be great. I've created a dialog form like that bound to the archive (audit) table but at the moment it is entering the data into a second record rather than the same record as the BeforeUpdate process on the main form.

So after a modified record is saved in the table "Audit" I get:
First Row: FormName, DateChanged, TimeChanged, ClaimNo, Claim, ClaimDescrip, Permit, CurrentUser
Second Row: Mod_Reason, Mod_Doc, Mod_Significanc e, Mod_Impact

Obviously what I want to happen is for all the data to get saved into one row rather than two rows. It's occuring cause the BeforeUpdate process is happening before the Dialog Form.

I have no idea how to call the data into same row of the table when the form updates.

The other way as it is a one-many would be a subform (that is only accesible when you are editing or adding a record) which writes to the same record in the audit table. The problem as I said before is that the main form updates when the subform gains focus so again it would save to a different row...

I'm close but this is starting to ache my brain!

[LeighW]

I thought of a way of doing it and that is using an unbound dialog form which has the required textboxes, etc in. Once you press submit it makes the dialog form invisible.

On the form there are hidden textboxes linked to the dialog form via the expression Forms!frm_Claim QA!Reason. The form then has a value which is saves to the audit table on update. After update the dialog form closes and resets. Works alright actually! Users will have to enter data into the dialog form as I'll disable the submit button until the controls have been changed.

[zmbd]

I'll keep an eye on the thread for little while to see how you progress.
-z