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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Adding An Auditing Module To A Subform, Access 2016    
 
   
Povolish
post Nov 28 2018, 02:22 PM
Post#1



Posts: 29
Joined: 28-November 18



Hi! I created a database recently and added austin06's Auditing module from a Youtube tutorial. I decided to reformat my database and place all of my front end data on a Navigation Form. Since I am not very knowledgeable with the coding yet, I found that I was not able to remake the code to include the auditing module on the new version of the database.
Here is the module I used to create this auditing function:
CODE
Option Compare Database

Public Function AuditChanges(RecordID As String, UserAction As String)


Dim DB As Database
Dim RST As Recordset
Dim clt As Control
Dim UserLogin As String

Set DB = CurrentDb
Set RST = DB.OpenRecordset("select * from audittrail", dbOpenDynaset)

UserLogin = Environ("Username")
Select Case UserAction
      Case "New"
          With RST
          .AddNew
          ![DateTime] = Now()
          !UserName = UserLogin
          !FormName = Screen.ActiveForm.Name
          !Action = UserAction
          !RecordID = Screen.ActiveForm.Controls(RecordID).Value
          .Update
          
          End With
          
      Case "Delete"
         With RST
          .AddNew
          ![DateTime] = Now()
          !UserName = UserLogin
          !FormName = Screen.ActiveForm.Name
          !Action = UserAction
          !RecordID = Screen.ActiveForm.Controls(RecordID).Value
          .Update
          End With
          
      Case "Edit"
        For Each clt In Screen.ActiveForm.Controls
        If (clt.ControlType = acTextBox _
                Or clt.ControlType = acComboBox) Then
                If Nz(clt.Value) <> Nz(clt.OldValue) Then
                With RST
                    .AddNew
                    ![DateTime] = Now()
                    !UserName = UserLogin
                    !FormName = Screen.ActiveForm.Name
                    !Action = UserAction
                    !RecordID = Screen.ActiveForm.Controls(RecordID).Value
                    !FieldName = clt.ControlSource
                    !OldValue = clt.OldValue
                    !newValue = clt.Value
                    .Update
                End With
            End If
        End If
    Next clt
End Select
RST.Close
DB.Close
Set RST = Nothing
Set DB = Nothing



End Function



The code works when each individual form is updated. However, since all of my forms are now on a Navigation form, that makes them all subforms now. So I assume that I need to "repath," my code in a sense. That is the part I am not too clear about.

Here is the code I had originally applied to each actual form on my navigation form. I also assume that this code will need to be modified and placed on all of the subforms.
CODE
Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Call AuditChanges("VendorID", "New")
Else
    Call AuditChanges("VendorID", "Edit")
End If
End Sub


Any help you can provide would be appreciated. This is my first database, and I've managed to fix every issue with it myself but the actual coding has been difficult. Thank you very much!
Go to the top of the page
 
theDBguy
post Nov 28 2018, 02:26 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,606
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

At first glance, I don't see anything that will stop it from working. Are you saying it is not working now?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Povolish
post Nov 28 2018, 03:40 PM
Post#3



Posts: 29
Joined: 28-November 18



So basically when I apply the code to the subforms, I get the notification that is attached.
Thanks for your reply!
Go to the top of the page
 
Povolish
post Nov 28 2018, 03:43 PM
Post#4



Posts: 29
Joined: 28-November 18



Attached File  Screenshot__21_.png ( 124.2K )Number of downloads: 16

Sorry, here is that attachment of my notification.
Go to the top of the page
 
theDBguy
post Nov 28 2018, 04:22 PM
Post#5


Access Wiki and Forums Moderator
Posts: 75,606
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Are you auditing any MVF or Attachment fields?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Povolish
post Nov 28 2018, 05:07 PM
Post#6



Posts: 29
Joined: 28-November 18



There is one multivalued field, however, it is locked from users and will not need to be audited.
Go to the top of the page
 
theDBguy
post Nov 28 2018, 05:30 PM
Post#7


Access Wiki and Forums Moderator
Posts: 75,606
Joined: 19-June 07
From: SunnySandyEggo


So, are you saying the code is not auditing the MVF field? I was just wondering if the issue was when you audit the MVF.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Povolish
post Nov 29 2018, 09:11 AM
Post#8



Posts: 29
Joined: 28-November 18



No, it is not auditing anything. It used to tell me the old value vs new value, what field was edited, etc. Now it gives me that notification or doesn't do anything.
Go to the top of the page
 
Povolish
post Nov 30 2018, 12:17 PM
Post#9



Posts: 29
Joined: 28-November 18



Okay, so I actually tampered with my code a bit and found out how to get my normal subforms to audit correctly. HOWEVER, there is one navigation form on my main navigation form. So these subforms on this subform are not showing up in my audit table. I'm currently trying to figure out how to apply the same process to a subform on a subform.
Go to the top of the page
 
theDBguy
post Nov 30 2018, 12:34 PM
Post#10


Access Wiki and Forums Moderator
Posts: 75,606
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Did you figure out what was causing the error? If so, would you mind sharing? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Povolish
post Nov 30 2018, 01:40 PM
Post#11



Posts: 29
Joined: 28-November 18



Basically, what was happening was that I could not get my code to give information about an audit change when I was editing a subform. However, when I went to the actual form of that subform, it would work.

This meant that somewhere in my audit module, I had to change the code to reflect changes to the subform, not the actual form.

In my original code, I had these, which were the fields referencing the primary key and the name of the form that was being edited:
!FormName = Screen.Activeform.Name
!RecordID = Screen.ActiveForm.Controls(RecordID).Value

In my case edit, I also had :
For Each clt In Screen.ActiveForm.Controls

I changed these to:
!FormName = Screen.ActiveForm.ActiveControl.Form.Name
!RecordID = Screen.ActiveForm.ActiveControl.Form(RecordID).Value
For Each ctl In Screen.ActiveForm.ActiveControl.Form

So for all my subforms on my Main navigation form, it works now. HOWEVER, on one of the tabs of my main navigation form, it leads into another navigation form, with more subforms. Although I put the same code on the BeforeUpdate
property of these subforms, it does not record the changes like it does on my main subforms.

It's probably pretty hard to follow, as it was hard to write. I'll try to simplify it:
My layers and code are:

Main Navigation Form
-Main Tabs -Tab With Another Navigation Form
-SubForms -Sub-Navigation Form
-SubForms

(Auditing Works Here) (Auditing does NOT work here)

Hopefully that helps.
Go to the top of the page
 
Povolish
post Nov 30 2018, 01:42 PM
Post#12



Posts: 29
Joined: 28-November 18



Sorry, my tree did not format the way I wanted it to.

So I'll retry to simplify below:

Nav Form - Tabs - Subforms (audit works)
One of the Tabs - Sub-Nav Form - Subforms of Nav SubForm (audit does NOT work)
Go to the top of the page
 
theDBguy
post Nov 30 2018, 01:44 PM
Post#13


Access Wiki and Forums Moderator
Posts: 75,606
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Thanks for the explanation. When and where are you executing the audit code?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Povolish
post Nov 30 2018, 01:50 PM
Post#14



Posts: 29
Joined: 28-November 18



So I have my auditmodule, and the following code is in the BeforeUpdate property of EVERY form, including my main and sub navigation form.
CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Call AuditChanges("VendorID", "New")
Else
    Call AuditChanges("VendorID", "Edit")
End If
End Sub


This code is calling my AuditModule, which I updated from the original post, so I'll go ahead and put that here.
CODE
Option Compare Database

Public Function AuditChanges(RecordID As String, UserAction As String)


Dim DB As Database
Dim RST As Recordset
Dim clt As Control
Dim UserLogin As String

Set DB = CurrentDb
Set RST = DB.OpenRecordset("select * from audittrail", dbOpenDynaset)

UserLogin = Environ("Username")
Select Case UserAction
      Case "New"
          With RST
          .AddNew
          ![DateTime] = Now()
          !UserName = UserLogin
          !FormName = Screen.ActiveForm.ActiveControl.Form.Name
          !Action = UserAction
          !RecordID = Screen.ActiveControl.Parent.Form(RecordID).Value
          .Update
          
          End With
          
      Case "Delete"
         With RST
          .AddNew
          ![DateTime] = Now()
          !UserName = UserLogin
          !FormName = Screen.ActiveForm.ActiveControl.Form.Name
          !Action = UserAction
          !RecordID = Screen.ActiveControl.Parent.Form(RecordID).Value
          .Update
          End With
          
      Case "Edit"
        For Each ctl In Screen.ActiveForm.ActiveControl.Form
        If (ctl.ControlType = acTextBox _
                Or ctl.ControlType = acComboBox) Then
                If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                With RST
                    .AddNew
                    ![DateTime] = Now()
                    !UserName = UserLogin
                    !FormName = Screen.ActiveForm.ActiveControl.Form.Name
                    !Action = UserAction
                    !RecordID = Screen.ActiveForm.ActiveControl.Form(RecordID).Value
                    !FieldName = ctl.ControlSource
                    !OldValue = ctl.OldValue
                    !newValue = ctl.Value
                    .Update
                End With
            End If
        End If
    Next ctl
End Select
RST.Close
DB.Close
Set RST = Nothing
Set DB = Nothing



End Function
Go to the top of the page
 
theDBguy
post Nov 30 2018, 02:04 PM
Post#15


Access Wiki and Forums Moderator
Posts: 75,606
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Thanks for the additional information. I could be wrong because I can't try out your database, but it seems you may be overcomplicating the process. In my humble opinion, if you're using the Form's BeforeUpdate event to create the audit log, then the code should work by simply referencing the current object, since the BeforeUpdate of the subform fires separately from its Parent's.

Maybe what you need to add is a check if the control is a subform, and if so, perform a recursion to go through its controls.

For example, if the user updated the data in the first subform, are you trying to write an audit log about the entire Navigation Form or just what was changed in the first subform? If subform 1 had a subform and the user made a change in there, do you want an audit of the inner subform plus the outer subform? If you say just the inner subform, then I think there's a simpler way to do this.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Povolish
post Nov 30 2018, 02:12 PM
Post#16



Posts: 29
Joined: 28-November 18



I would want the inner subform. Originally, when I added a record, no matter what field or form, it would just tell me "Navigation Form" and the record changed was "1".

With the new code I added to the module I posted, It tells me the subform I edited and the correct record added/edited.

However, I get nothing in my table for my Sub-NavForm and the sub forms attached to it.
Go to the top of the page
 
Povolish
post Nov 30 2018, 02:13 PM
Post#17



Posts: 29
Joined: 28-November 18



But you are correct, I don't want the navigation form's changes, I want the changes of the individual subforms attached to it.
Go to the top of the page
 
theDBguy
post Nov 30 2018, 02:21 PM
Post#18


Access Wiki and Forums Moderator
Posts: 75,606
Joined: 19-June 07
From: SunnySandyEggo


Hi,

The reason why you kept getting the name of the Navigation Form is because you're using Screen.ActiveForm. If a user make changes to a subform with a subform, the BeforeUpdate event of the outer subform will fire before they can even make any changes to the inner subform, so I don't think you really need to audit the inner subform at this time. But if you really do, then like I said, it's probably better to check if the control is a subform and then recurse to perform the audit again using the inner subform as the "form." If you use recursion, then it wouldn't matter how many levels deep you nest your subforms, you'll be able to audit all of them.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Povolish
post Nov 30 2018, 02:25 PM
Post#19



Posts: 29
Joined: 28-November 18



So could you explain how I would go about doing that? I changed my code to just include sub forms on my nav form, and those sub forms audit correctly.

But from what you're saying, I could add some sort of recursion function to make it simply not matter which form I'm on, no matter if i'm on a main form,
sub form, or sub-sub form. It will track the audits regardless.

So how should I do that?
Go to the top of the page
 
theDBguy
post Nov 30 2018, 02:36 PM
Post#20


Access Wiki and Forums Moderator
Posts: 75,606
Joined: 19-June 07
From: SunnySandyEggo


Hi,

As I was saying earlier, the main sticking point in your code is the use of the Screen object. So, when the forms were standalone forms, Screen.ActiveForm referred to themselves. But as soon as you add the forms to a Navigation Form, the Screen.ActiveForm now refers to the Navigation Form, rather than the subforms. You probably had the same issue with the one form with a subform using your old audit code, but maybe just hadn't notice it. If it worked before on a form with a subform, I'll have to see it to see why.

I haven't really reviewed your code closely, but the more I look at it, I am not certain what you're trying to do yet. For example, if you had five textboxes on the form and when the BeforeUpdate event fires to execute the audit code, will your audit table have five new records showing the old and new value for each of the five textboxes? The way I read the code right now, it seems it would either only have one new record, or it will have five but all five will have the same information in them.

Are you able to post a small copy of your db with the audit log for us to play with?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    26th June 2019 - 03:00 AM