VBA before update problem

[grahamsyuk]

Hi all I wish to create a VBA function that will write details of any form item to a db table for auditing purposes.

I had planned that I could call my function in the beforeUpdate event on every form item ( passing in the form and the activeControl. )


Private Sub Text21_BeforeUpdate(Cancel As Integer)

Call Functions.Audit_before(Me, Me.activeControl)

End Sub

The function is as follows and it almost works apart from the Change_Before field has problems.

1) it places the new value of the text box in the audit table
2) if the previous value was null then an error is thrown.



Public Sub Audit_before(objForm As Form, activeControl As Control)

Dim db As DAO.Database
Set db = CurrentDb()

Dim Change_BorrowerID As Integer
Dim Change_Table As String
Dim Change_Date As String
Dim Change_Time As String
Dim Change_Username As String
Dim Change_FieldName As String
Dim Change_Before As String

Change_BorrowerID = [Forms]![Borrower_MasterForm_Display]![fld_Borrower_ID]
Change_Table = objForm.RecordSource
Change_Date = Format(Now, "yyyymmdd")
Change_Time = Format(Now, "H:MM am/pm")
Change_Username = CurrentUser
Change_FieldName = objForm.activeControl.Name
Change_Before = objForm.activeControl.OldValue

db.Execute ("INSERT INTO AuditLog_ChangeNEW (Change_BorrowerId,Change_Table,Change_Date,Change_Time,Change_Username,Change_FieldName,Change_Before ) VALUES ('" & Change_BorrowerID & "','" & Change_Table & "','" & Change_Date & "','" & Change_Time & "','" & Change_Username & "','" & Change_FieldName & "','" & Change_Before & "');")

End Sub


Any help would be appreciated. Im pretty new to VBA but hopefully I am not to far off. Thanks