I use the following code to document when someone leaves our company (If they meet the right criteria that I need to note when they left).

Private Sub TerminationDate_BeforeUpdate(Cancel As Integer)

If Not IsNull([TerminationDate].Value) Then

If (Me.CertificationType = "Certified" _
Or Me.CertificationType = "Interim Certified" _
Or Me.CertificationType = "Medical Restrictions" _
Or Me.CertificationType = "Temporary Decertification") _
And Me.AccessType = "PRP" Then

CurrentDb.Execute "INSERT INTO tblChanges (EmpNum, ChangeMade, MadeWhen, LoginName, EffectiveWhen) VALUES('" & Me.EmpNo & "','" & "Termination" & "','" & Now() & "','" & [Forms]![frmUserInfo].[LoginName].Value & "','" & Me.TerminationDate.Value & "')", dbFailOnError

If Not IsNull([CertifyingOfficial]) Then

CurrentDb.Execute "INSERT INTO tblChanges (EmpNum, ChangeMade, MadeWhen, FieldOldValue, FieldNewValue, PrintCDPRCO, ListValue, EffectiveWhen) VALUES ('" & Me.EmpNo & "','" & "Termination" & "','" & Now() & "','" & Me.LastName.OldValue & "','" & Me.LastName.Value & "','" & -1 & "','" & Me.CertifyingOfficial & "','" & Me.TerminationDate.Value & "')", dbFailOnError

End If

If Not IsNull([AminOffical]) Then

CurrentDb.Execute "INSERT INTO tblChanges (EmpNum, ChangeMade, MadeWhen, FieldOldValue, FieldNewValue, PrintCDPRAO, ListValue, EffectiveWhen) VALUES ('" & Me.EmpNo & "','" & "Termination" & "','" & Now() & "','" & Me.LastName.OldValue & "','" & Me.LastName.Value & "','" & -1 & "','" & Me.AminOffical & "','" & Me.TerminationDate.Value & "')", dbFailOnError

End If

If Not IsNull([LockKeySeries]) Then

CurrentDb.Execute "INSERT INTO tblChanges (EmpNum, ChangeMade, MadeWhen, FieldOldValue, FieldNewValue, PrintKeyList, ListValue, EffectiveWhen) VALUES ('" & Me.EmpNo & "','" & "Termination" & "','" & Now() & "','" & Me.LastName.OldValue & "','" & Me.LastName.Value & "','" & -1 & "','" & Me.LockKeySeries & "','" & Me.TerminationDate.Value & "')", dbFailOnError

End If

End If

End If

End Sub

Trouble is what if they change their mind and don't leave? What if they change their leave date. How do you suggest I go about updating their record in the tblChanges.

I need to alter or remove the record in the tblChanges because it prompts the printing of several documents based on a person being here or gone.

So if there already exists a record in tblChanges for this person (empum) and it is the TerminationDate I am changing, I need to update the EffectiveDate if I just changed the date. (TerminationDate.OldValue <> TerminationDate.Value) or delete the record in tblChanges if they are not terminating anymore (TerminationDate.Value = Null)

I am probably not explaining what I need very well...Yesterday was a bad day and I still haven't mentally recovered :-(

Lena

The way I have it set is that if there is any deletion of a record from the main table, I have a separate table called History, which I capture the entire record to be deleted(as is). In that way, I still maintain the audit trail, but in the event of having to do an UNDO, one can retrieve the deleted record. The way you code your audit trail will really help in retrieving deleted records. You may want to add a field in the Audit Trail table called "Update Type" which will capture either an ADD, UPDATE or DELETE; this is more for Access to recognize the changes from a database perspective.


Hopes this helps.

Suggestion -- you may want to have a policy that no entry is made in relation to termination/ leaving until the individual is physically gone. In your form there should be a hire date, termination date and a memo field + all other fields. In the memo field you could indicated what should happen and if it doesn't, then make another note to indicate why not or something to that effect.

If the individual returns after leaving, make a new record and in the memo section make a note to refer.

Thanks for your ideas. I need to come up with a solution soon and will let you know if I have any other questions.

Thanks!
Lena