UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Audits, Access 2016    
 
   
mike60smart
post Oct 6 2019, 01:01 PM
Post#1


UtterAccess VIP
Posts: 13,421
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I am trying to run the following Code to record changes into a tblAuditTrail but get the following error:-

Attached File  Error.PNG ( 10.78K )Number of downloads: 7


Any help appreciated

CODE
' ================================================
' Code by Martin Green Email: martin@fontstuff.com
' Visit my Office Tips website @ www.fontstuff.com
' YouTube tutorials www.youtube.com/martingreenvba
' ================================================
'

Sub AuditChanges(IDField As String, UserAction 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")
    Select Case UserAction
        Case "EDIT"
            For Each ctl In Screen.ActiveForm.Controls
                If ctl.Tag = "Audit" Then
                    If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                        With rst
                            .AddNew
                            ![DateTime] = datTimeCheck
                            ![UserName] = strUserID
                            ![FormName] = Screen.ActiveForm.Name
                            ![Action] = UserAction
                            ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                            ![FieldName] = ctl.ControlSource
                            ![OldValue] = ctl.OldValue
                            ![NewValue] = ctl.Value
                            .Update
                        End With
                    End If
                End If
            Next ctl
        Case Else
            With rst
                .AddNew
                ![DateTime] = datTimeCheck
                ![UserName] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![Action] = UserAction
                ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                .Update
            End With
    End Select
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

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
GroverParkGeorge
post Oct 6 2019, 01:30 PM
Post#2


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


Sub AuditChanges(IDField As String, UserAction As String)

requires two arguments, an ID Field and a User Action

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Jeff B.
post Oct 6 2019, 01:32 PM
Post#3


UtterAccess VIP
Posts: 10,309
Joined: 30-April 10
From: Pacific NorthWet


If I'm following correctly, your code/proc requires two parameters, but the call appears to only provide one.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
mike60smart
post Oct 6 2019, 01:33 PM
Post#4


UtterAccess VIP
Posts: 13,421
Joined: 6-June 05
From: Dunbar,Scotland


Hi George & Jeff

How would I modify this "Call AuditChanges("EmployeeID")" to include the required Action Element?


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
GroverParkGeorge
post Oct 6 2019, 02:28 PM
Post#5


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


You need to add it as a second argument.

Call AuditChanges("EmployeeID", "UserAction")

However, the specific values for both need to come from controls, I assume, on the form where it is implemented. How you get those values, of course, is up to you.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
WildBird
post Oct 7 2019, 04:07 PM
Post#6


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


CODE
Call AuditChanges("EmployeeID", "Edit")


This should audit the form when any edit has been made.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
orange999
post Oct 7 2019, 05:38 PM
Post#7



Posts: 1,975
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Mike,

There are some issues with that audit routine --especially with respect to sub forms.
See this link for some recent activity.

--------------------
Good luck with your project!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 09:05 AM