I've made a multi user (4 end users) database in Access 2000. I've made data tables, reference tables etc which sits on a shared drive and I've made individual front ends for the end users which gives them specific views of the data.
I'd like to track all the changes made to a record which works find with the following code :
basAuditTrail:
Expand|Select|Wrap|Line Numbers
- Option Compare Database
- Const cDQ As String = """"
- Sub AuditTrail(frm As Form, recordid As Control)
- 'Track changes to data.
- 'recordid identifies the pk field's corresponding
- 'control in frm, in order to id record.
- Dim ctl As Control
- Dim varBefore As Variant
- Dim varAfter As Variant
- Dim strControlName As String
- Dim strSQL As String
- On Error GoTo ErrHandler
- 'Get changed values.
- For Each ctl In frm.Controls
- With ctl
- 'Avoid labels and other controls with Value property.
- If .ControlType = acTextBox Then
- If .Value <> .OldValue Then
- varBefore = .OldValue
- varAfter = .Value
- strControlName = .Name
- 'Build INSERT INTO statement.
- strSQL = "INSERT INTO " _
- & "Audit (EditDate, User, RecordID, SourceTable, " _
- & " SourceField, BeforeValue, AfterValue) " _
- & "VALUES (Now()," _
- & cDQ & Environ("username") & cDQ & ", " _
- & cDQ & recordid.Value & cDQ & ", " _
- & cDQ & frm.RecordSource & cDQ & ", " _
- & cDQ & .Name & cDQ & ", " _
- & cDQ & varBefore & cDQ & ", " _
- & cDQ & varAfter & cDQ & ")"
- 'View evaluated statement in Immediate window.
- Debug.Print strSQL
- DoCmd.SetWarnings False
- DoCmd****nSQL strSQL
- DoCmd.SetWarnings True
- End If
- End If
- End With
- Next
- Set ctl = Nothing
- Exit Sub
- ErrHandler:
- MsgBox Err.Description & vbNewLine _
- & Err.Number, vbOKOnly, "Error"
- End Sub
in event procedure : Before Update:
Expand|Select|Wrap|Line Numbers
- Private Sub Form_BeforeUpdate(Cancel As Integer)
- Call AuditTrail(Me, [Shipper ID])
- End Sub
"The expression Before Update you entered as the event property setting produced the following error: A problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control.
* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro."
Any help on this would be GREATLY appreciated!
Thanks