The line "Case Else" is part of the Select Case Statement. The ELSE should NOT have quotes around it.
You are passing the member ID as an argument of the Sub, but you don't use it anywhere.
Then you get the max member ID using :
Code:
MaxMember_id = DMax("[MEMBER_ID]", "BUSINESS")
The MaxMember_id is not connected to the string "MEMBER_ID" because there is no criteria in the DMAX function. It will ALWAYS return the max member ID - no matter what!
Adding this line:
Code:
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox Then
does not add anything of value. The code searches every control on the form to see if the TAG property has a value of "Audit". If a control's TAG property does not have "Audit", it is skipped. So the line is unnecessary.
When UserAction = "Edit", the code for Case "Edit" executes. For all other cases (where UserAction is NEW or DELETE or BANANA or BYE, or ...) the else code executes.
I created the audit table and ran the code. Well, I ran the code I modified. 
I had to comment out 3 or 4 of your customizations. Otherwise, it ran great.
Here is my revised code:
Code:
Sub AuditChanges(MEMBER_ID As String, UserAction As String)
'On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rstAudit As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Dim MaxMember_id As Integer
Set cnn = CurrentProject.Connection
Set rstAudit = New ADODB.Recordset
rstAudit.Open "Select * from tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = CurrentUser()
MaxMember_id = DMax("[MEMBER_ID]", "BUSINESS")
Select Case UserAction
'existing records
Case "Edit"
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rstAudit
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![Record_ID] = MaxMember_id
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
'Debug.Print "Current values " & UserAction
End With
End If
End If
Next ctl
Case Else
' New or deleted records
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
' If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rstAudit
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![Record_ID] = MaxMember_id
![FieldName] = ctl.ControlSource
' ![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
' End If
End If
Next ctl
End Select
AuditChanges_exit:
On Error Resume Next
rstAudit.Close
cnn.Close
Set rstAudit = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "Error!"
Resume AuditChanges_exit
End Sub
It helps if you comment the code.....
Windows 7 64bit 
Access 2010 64bit
Reply With Quote
