Results 1 to 10 of 10
  1. #1
    TenOc is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    94

    Audit Trail code error ??? Frist line error

    Access 2019

    I down loaded this code from website https://blueclawdb.com/access-databa...s/audit-trail/

    I do not understand the first line SubAuditChanges (IDField As String, UserAction As String) and it is giving an red error

    Any help is welcomed




    Code:
    SubAuditChanges (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 * FROMtblAuditTrail”, cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ(“USERNAME”)
    Select Case useraction
    Case “EDIT”
                 'For Each ctl InScreen.ActiveForm.Controls
    If ctl.Tag = “Audit” Then
    If Nz(ctl.Value) <>Nz(ctl.OldValue) Then
    With rst
    .AddNew
    ![FormName] =Screen.ActiveForm.Name
    ![RecordID] =Screen.ActiveForm.Controls(IDField).Value
    ![FieldName] =ctl.ControlSource
    ![OldValue] = ctl.OldValue
    ![NewValue] = ctl.Value
    ![UserID] = strUserID
    ![DateTime] = datTimeCheck
    .Update
    End With
    End If
    End If
    Next ctl
            Case Else
    With rst
    .AddNew
    ![DateTime] = datTimeCheck
    ![UserID] = 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
    EndSub
    

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    It needs a space

    Sub AuditChanges

    as does the last line

    End Sub
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    TenOc is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    94
    Thanks so simple

  4. #4
    TenOc is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    94
    One more question???

    How to add an addition field to the tblAuditTrail table. I have added "KeyName as a short text field to the table. In the subroutine, I Dim KeyField as string. Then I add this statement in the CASE loop ![KeyName] = ctl.KeyName. Does not work. I commented out the DIM statement and still does not work.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by TenOc View Post
    One more question???

    How to add an addition field to the tblAuditTrail table. I have added "KeyName as a short text field to the table. In the subroutine, I Dim KeyField as string. Then I add this statement in the CASE loop ![KeyName] = ctl.KeyName. Does not work. I commented out the DIM statement and still does not work.
    Don't need any of those changes.
    Simply type the new control's tag = "Audit" for the textbox holding KeyName in your form. That's all.

  6. #6
    TenOc is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    94

    I do not understand your comment

    Quote Originally Posted by davegri View Post
    Don't need any of those changes.
    Simply type the new control's tag = "Audit" for the textbox holding KeyName in your form. That's all.
    I do not understand your comment. The form has about 20 fields ( controls) where the tag = audit. If I make a change to anyone of these fields I do get the audit report. See attached Screen shot.


    Click image for larger version. 

Name:	Audit 2021-10-18_17h04_20.png 
Views:	13 
Size:	27.3 KB 
ID:	46439


    Note: The field name SKU is what I want to add. It is the KeyName field

    I am sorry if I do not explain my problem correct I am a newbie

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I do not understand your comment. The form has about 20 fields ( controls) where the tag = audit. If I make a change to anyone of these fields I do get the audit report. See attached Screen shot.
    Have you added the new field to a table? Not the audit table, but probably the products table.
    Then you add a textbox for your new field to the Products form. That's where you set the tag for the new textbox to "Audit", just like the other textboxes on that form.
    The new textbox must be bound to the new field in the products table.

    That's all. The new field should appear in the audit table just like all the others.

  8. #8
    TenOc is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    94
    I found my problem. All is WORKING NOW

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Quote Originally Posted by TenOc View Post
    I found my problem. All is WORKING NOW
    Well please say what it was and what you did to fix it?
    These forums are here to help people and what problem you had, someone else might have in the future.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    TenOc is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    94
    Quote Originally Posted by Welshgasman View Post
    Well please say what it was and what you did to fix it?
    These forums are here to help people and what problem you had, someone else might have in the future.
    I removed the subForm from the parent summary form (which I wanted to be a read only form -- no edit or changes) and made it a stand alone for adding and editing . This makes my work flow much better and makes the programing simple

    See https://www.accessforums.net/showthread.php?t=84617

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Audit Trail "Type mismatch" error
    By rrowsam in forum Programming
    Replies: 8
    Last Post: 04-29-2019, 08:32 AM
  2. Replies: 2
    Last Post: 02-06-2019, 08:32 AM
  3. Replies: 17
    Last Post: 04-28-2017, 09:18 PM
  4. Audit Trail Code - Error 3251
    By Juans in forum Access
    Replies: 3
    Last Post: 01-04-2015, 02:21 PM
  5. Replies: 2
    Last Post: 02-09-2013, 12:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums