Let me start by saying thank you for taking the time to read my question.
I have an audit trail module called from my forms.
Code:
Sub AuditChanges(IDField As String) On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = Environ("USERNAME")
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![AuditDateTime] = datTimeCheck
![AuditUser] = strUserID
![AuditForm] = Screen.ActiveForm.Name
![AuditRecord] = Screen.ActiveForm.Controls(IDField).Value
![AuditField] = ctl.ControlSource
![AuditOld] = ctl.OldValue
![AuditNew] = ctl.Value
.Update
End With
End If
End If
Next ctl
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChanges_Exit
End Sub
It works great, but when I tried to add a multi-select combo box, I get a type mismatch error.
Basically the form is for customers, and there's a field for status (Customer, Prospect, Suspect) and the customer can be 1 or more of these... the field works fine and as expected, but my audit trail code doesn't like it... any ideas?
Let me know what else you would need.