Audit Trail (1 Viewer)

I am working with this code to do an Audit Trail
What I want is to get the TEXT for a combo box and not the (first) column.

http://articles.techrepublic.com.com/5100-10878_11-6166807.html

What I want is to get the TEXT for a combo box and not the (first) column.

Click to expand...

Me.MyComboBoxName.Value

I don't see a reference to a ComboBox within your code

.

I am WANTING to put in ComboBox in the code above I can get value but that would only give me the ID and not the text. I guess I can try .value.Column(1) and .oldValue.Column(1)

a combo box is zero base

sosay

dim v as variant 'as an example

v = mycombobox 'actually dereference the first column, column(0)
v= mycombobox.column(0) 'does the same thing
v = mycombobox.column(1) 'deferences the second column

etc

now the columns are as ordered in the underlying query, so that columns with a width of 0 in the list box, are nevertheless included in the column count.

--------
the concept of oldvalue applies to the value of the combo box. The combo box value actually is determined by the bound column (generally first column (ie column(0)), but not necessarily.

so combobox.oldvalue stores the previous value of the combobox BEFORE it is written (ie it is available for inspection in beforeupdate), but this does not mean that the other columns can also be examined directly by referring to the oldvalue.

if you need them, you ought to save them in variables in the current and afterupdate events of the combo box. (easiest, i would think)

This is my solution based on the one by Martin Green

I have changed things to
1. Save additions the same way as edits
2. To save old and new values in memo fields as concantenated strings of "[fieldname1]:[value1];.....[fieldname]:[value]"
3. To save ID of deleted record correctly (the original solution seemed to have a problem)
4. Gets combo displayed text instead of bound column (unless they are the same!)
5. Includes reference to fOSMachineName() code from Dev Ashish for computer name

Modified code: (See Green's solution for full explanation)
Sub AuditChanges(IDField As String, UserAction As String, Optional DeletedID As String)
'On Error GoTo AuditChanges_Err

Dim rst As Recordset
Dim ctl As control
Dim datTimeCheck As Date
Dim strUserID As String
Dim prp As Property
Dim updatectl As Boolean
Dim OldStr As String, NewStr As String
Dim colx As Integer, colwidth As String, lenx As Integer, str As String, valx As Integer, visiblecol As Boolean
Dim i As Long, OldID, NewID

Set rst = CurrentDb.OpenRecordset("Audit")

datTimeCheck = Now()
strUserID = fOSMachineName()
Select Case UserAction
Case "DELETE"
With rst
.AddNew
![DateTime] = datTimeCheck
![Username] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = DeletedID
.Update
End With
Case Else
OldStr = ""
NewStr = ""
For Each ctl In Screen.ActiveForm.Controls
updatectl = False
For Each prp In ctl.Properties
If prp.Name = "Controlsource" Then
If IsNull(ctl.ControlSource) = False Then updatectl = True
End If
Next prp

If ctl.ControlType = acComboBox Then
colx = 0
visiblecol = False
colwidth = ctl.ColumnWidths
lenx = Len(colwidth)

For i = 1 To lenx
str = Mid(colwidth, i, 1)
If visiblecol = False Then
If IsNumeric(str) = True Then
valx = Val(str)
If valx > 0 Then visiblecol = True
Else
If str = ";" Then colx = colx + 1
End If
End If
Next i

End If

If updatectl = True Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
If OldStr > "" Then
OldStr = OldStr & " ; "
NewStr = NewStr & " ; "
End If
If ctl.ControlType = acComboBox Then
For i = 0 To (ctl.ListCount - 1)
If Trim(ctl.Column(ctl.BoundColumn - 1, i)) = Trim(ctl.OldValue) Then
OldStr = OldStr & ctl.ControlSource & " : " & ctl.Column(colx, i)
End If
Next i

NewStr = NewStr & ctl.ControlSource & " : " & ctl.Column(colx)
Else
OldStr = OldStr & ctl.ControlSource & " : " & ctl.OldValue
NewStr = NewStr & ctl.ControlSource & " : " & ctl.Value
End If
End If
End If
Next ctl
If UserAction = "NEW" Then OldStr = ""
With rst
.AddNew
![DateTime] = datTimeCheck
![Username] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
![OldValue] = OldStr
![NewValue] = NewStr
.Update
End With

End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChanges_Exit
End Sub

Call from each form with:
assumption is your PK id called ID

Dim DelID As String

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Call AuditChanges("ID", "NEW")
Else
Call AuditChanges("ID", "EDIT")
End If
End Sub

Private Sub Form_Delete(Cancel As Integer)
DelID = [ID]
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then Call AuditChanges("ID", "DELETE", DelID)
End Sub

Do you have a question, or just reviving a 5 year-old thread with your solution?

No question just think it is a better solution than any other here and problem still exists after 5 years

John

Got this audit trail working with a minor change as it kicked up errors. The old code of

If UserAction = "NEW" Then OldStr = ""

was changed to

If UserAction = "NEW" Then OldStr = "New Record"

I have not been able to get the code to work on a subform of my main form. I believe that I need to reference the subform using Screen.ActiveForm!SubformControlName.Form, but I am not sure how to implement that in this code to make it work. Any suggestions?

TIA