Audit Trail Date Problem

[fergler]

I have been setting up an audit trail for my database, and I'm having a bit of a problem with dates. I have date fields where the date can either be entered manually or by a pop up calendar. If the date is entered manually, the change is added to my audit table. If the date is added via the pop up calendar, the change is not recorded. I've added both the date fields and the command buttons for the calendar to my audit code, hoping that would work, but it doesn't.

Does anyone have any thoughts on this?

 

[CraigDolphin]

Are you using the after_update event of the date fields to do something necessary for your code? If you assign the value of the datefield using vba in a command button or popform etc the after_update event won't be triggered as it would have been by manual entry.

 

[fergler]

Craig:

Below is a sample of the code I'm using. I don't know a lot about developing code, so I generally find what I can on the internet, which is where I found this. What happens is a temp database is created every time a form is opened, and the changes are saved to the temp table. Once the changes are confirmed, they are moved to the audit table, and deleted from the temp table. I'm not quite sure how to incorporate the after_upate event for the cmd buttons into this.

Option Compare Database
Option Explicit
Const txtTableName = "FacultyEffort"
Dim Subsumed_EffortOldValue As Variant
Dim cmdStartDateOldValue As Variant
Dim cmdEndDateOldValue As Variant

Private Sub Subsumed_Effort_BeforeUpdate(Cancel As Integer)
WriteAuditUpdateToTemp txtTableName, Me.EntryID, "Subsumed Effort", Me.[Subsumed Effort].OldValue, Me.[Subsumed Effort].Value
End Sub

Private Sub cmdStartDate_BeforeUpdate(Cancel As Integer)
WriteAuditUpdateToTemp txtTableName, Me.EntryID, "cmdStartDate", Me.cmdStartDate.OldValue, Me.cmdStartDate.Value
End Sub

Private Sub cmdEndDate_BeforeUpdate(Cancel As Integer)
WriteAuditUpdateToTemp txtTableName, Me.EntryID, "cmdEndDate", Me.cmdEndDate.OldValue, Me.cmdEndDate.Value
End Sub

WriteAuditUpdateToTemp txtTableName, EntryIDOldValue, "Subsumed Effort", Subsumed_EffortOldValue, "#Deleted#"
WriteAuditUpdateToTemp txtTableName, EntryIDOldValue, "cmdStartDate", cmdStartDateOldValue, "#Deleted#"
WriteAuditUpdateToTemp txtTableName, EntryIDOldValue, "cmdEndDate", cmdEndDateOldValue, "#Deleted#"
WriteAuditUpdateToTemp txtTableName, EntryIDOldValue, "", "", "RecordDeleted"

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
WriteAuditUpdateToTemp txtTableName, EntryIDOldValue, "Subsumed Effort", Subsumed_EffortOldValue, "#Deleted#"
WriteAuditUpdateToTemp txtTableName, EntryIDOldValue, "cmdStartDate", cmdStartDateOldValue, "#Deleted#"
WriteAuditUpdateToTemp txtTableName, EntryIDOldValue, "cmdEndDate", cmdEndDateOldValue, "#Deleted#"
WriteAuditUpdateToTemp txtTableName, EntryIDOldValue, "", "", "RecordDeleted"

Private Sub Form_Delete(Cancel As Integer)
Subsumed_EffortOldValue = Me.Subsumed_Effort.OldValue
cmdStartDateOldValue = Me.cmdStartDate.OldValue
cmdEndDateOldValue = Me.cmdEndDate.OldValue
End Sub

Thanks.

Jennifer

 

[gemma-the-husky]

i havent looked through all this in detail, but if you use a calendar popup to pick a date, you wont get the text box update events firing for the date text box - you will have to run appropriate code manually.

hope this helps

 

[fergler]

Gemma:

I did finally find something on the internet that indicated that changing data by using VB or a macro won't trigger the BeforeUpdate/AfterUpdate events. I've been trying tirelessly today to figure out what code might need to be added to make this work. I was using the AfterUpdate event on the date fields, but nothing work. Do you have any suggestions?

Thanks.

Jennifer

 

[RuralGuy]

Jennifer,
Why are you using control BeforeUpdate events to write to your AuditTable? The AfterUpdate event would be more appropriate. Interesting naming convention you are using. I suspect cmdEndDate is a TextBox since a CommandButton does not have a BeforeUpdate event.

 

[RuralGuy]

You could actually complete almost everything in the BeforeUpdate event of the Form by comparing .Value to .OldValue. and write to the Audit Table when they are different. You *only* get a Form BeforeUpdate event when a change has been made to a bound control.

 

[fergler]

RuralGuy:

cmdEndDate was actually the command button that triggered the pop-up calendar, which I finally figured out was wrong to have in my audit code, so it's been removed. The code for the audit table is something I picked up from the internet at http://www.rogersaccesslibrary.com/download3.asp?SampleName=AuditTrail2.mdb.
Since I'm not too experience with creating code, I'm running with this. I believe the rationale is that before any updates take effect, the changes are written to the temp database, and once the changes are confirmed, they move from the temp database to the audit table.

Jennifer

 

[RuralGuy]

The logic behind the Audit code is fine and Roger knows what he is doing. Just for the record, nothing is really changed until *after* the BeforeUpdate event of the FORM. How do you invoke the PopUp calendar now? Maybe the Click or DoubleClick event of the TextBox? If so then Pop Up the Calendar modal and save the Audit detail when the code gets back. The acDialog WindowMode of the OpenForm command halts the current code until the next form closes or is made invisible.

 

[fergler]

I use a command button to trigger the Pop Up calendar. A command button on the calender is used to close it. I'm not sure what you mean by "If so then Pop Up the Calendar modal and save the Audit detail when the code gets back." Should I be doing an Onclose event on the calendar form?

 

[RuralGuy]

Post the code behind your CommandButton that puts up the Calendar.

 

[fergler]

RG:

Sorry to be so dense, but I'm still not sure what code I should be writing. The cmdenddate button triggers the calendar form to open, the cmdok button on the calendar form closes the calendar. If I put the code behind the cmdenddate button, my choices seem to be only OnEnter, OnExit, OnGotFocus,OnLostFocus, etc. No after update events.

 

[RuralGuy]

The cmdenddate button probably has code in the OnClick event. Please post it in this thread so we can see it.

 

[fergler]

The code currently in the Onclick event is =CalendarFor([Effective End Date],"Select the Ending Date")

The rest of the code for opening the calendar is:

Option Compare Database
Option Explicit

'Calendar form variable:
Public gtxtCalTarget As TextBox 'Text box to return the date from the calendar to.

Public Function CalendarFor(txt As TextBox, Optional strTitle As String)
On Error GoTo Err_Handler
'Purpose: Open the calendar form, identifying the text box to return the date to.
'Arguments: txt = the text box to return the date to.
' strTitle = the caption for the calendar form (passed in OpenArgs).

Set gtxtCalTarget = txt
DoCmd.OpenForm "frmCalendar", windowmode:=acDialog, OpenArgs:=strTitle

Exit_Handler:
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation, "CalendarFor()"
Resume Exit_Handler
End Function