Results 1 to 3 of 3
  1. #1
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231

    Form Actions Not being Recorded

    I'm using Martin Green's audit table code which I gleaned from the internet. Unfortunately, the code doesn't seem to track what sort of action is actually happening while I'm in one of my edit forms. The line ![Action] = UserAction does not populate the audit_trail with any actions, but rather just leaves it blank.
    What can I do to track which action item was used? I'm interested in the edit and add new function.

    The following code is a snippet of what I'm using:

    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 Audit_Trail", 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 ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
    'Debug.Print ctl.Name
    'Debug.Print ctl.Value
    'Debug.Print ctl.OldValue

    If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
    With rst
    .AddNew
    ![DateTime] = datTimeCheck
    ![UserName] = strUserID
    ![FormName] = Screen.ActiveForm.Name
    ![Action] = UserAction -------------------line that doesn't produce anything.

    CementCarver

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I don't see UserAction variable declared or populated. There is only one case in the Select Case and it is not within quote marks - "Edit". Your code is incomplete and not like the example "An Alternative Audit Trail Routine for Recording Additions, Edits and Deletes" which uses the UserAction value. http://www.fontstuff.com/access/acctut21.htm

    Implementing this code requires two procedures as shown in the tutorial. The first is:

    Sub AuditChanges(IDField As String, UserAction As String)

    The second which will call the first is:

    Private Sub Form BeforeUpdate(Cancel As Integer)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    Thanks June7 for bringing this to my attention.

    I will review the link/tutorial and make the proper corrections.

    CementCarver

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

Similar Threads

  1. User Actions
    By CementCarver in forum Forms
    Replies: 2
    Last Post: 06-15-2013, 06:22 PM
  2. Macro-How get Show all Actions
    By gg80 in forum Macros
    Replies: 4
    Last Post: 05-25-2013, 08:17 AM
  3. Replies: 19
    Last Post: 04-23-2013, 10:08 AM
  4. Help with actions
    By xdeimusx in forum Access
    Replies: 7
    Last Post: 09-12-2011, 10:34 PM
  5. Macro with 3 actions
    By seweryng in forum Access
    Replies: 4
    Last Post: 01-20-2011, 10:15 AM

Tags for this Thread

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