Well, one thing I noticed:
Dim db As CurrentProject ' Current database
This line seems odd. CurrentProject refers to just that.
Set db = DBEngine(0)(0)
DBEngine(0)(0) is used to return a DAO databse object.
These two are not compatible.
I would get rid of the dbvariable, and replace it with
CurrentDB.
CurrentDB.Execute strSQL
Chris Nebinger
-----Original Message-----
I have this code as part of my audit log as well:
Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
On Error GoTo Err_AuditEditBegin
'Purpose: Write a copy of the old values to temp table.
' It is then copied to the true audit table in AuditEditEnd.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sKeyField = name of the AutoNumber field.
' lngKeyValue = Value of the AutoNumber field.
' bWasNewRecord = True if this was a new insert.
'Return: True if successful
'Usage: Called in form's BeforeUpdate event. Example:
' bWasNewRecord = Me.NewRecord
' Call AuditEditBegin
("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID,
bWasNewRecord)
Dim db As CurrentProject ' Current database
Dim sSQL As String
'Remove any cancelled update still in the tmp table.
Set db = DBEngine(0)(0)
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL
' If this was not a new record, save the old values.
If Not bWasNewRecord Then
sSQL = "INSERT INTO " & sAudTmpTable & " (
audType, audDate, audUser ) " & _
"SELECT 'EditFrom' AS Expr1, Now() AS Expr2,
NetworkUserName() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "."
& sKeyField & " = " & lngKeyValue & ");"
'db.Execute sSQL, dbFailOnError
db.BaseConnectionString sSQL, dbFailOnError
End If
AuditEditBegin = True
Exit_AuditEditBegin:
Set db = Nothing
Exit Function
Err_AuditEditBegin:
Call LogError(Err.Number, Err.Description, conMod
& ".AuditEditBegin()", , False)
Resume Exit_AuditEditBegin
End Function
and then the form calls the code with this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'audit procedure for frmClient
bWasNewRecord = Me.NewRecord
Call AuditEditBegin
("tblClient", "audTempClient", "ClientID", Nz(Me.ClientID,
0), bWasNewRecord)
