Catch an action on subform

[neseri]

Hi guys

I am trying to track changes to my tables and I hit the wall.

I have this code in my modules:

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

I have set Tag to Audit in my property sheet for a field that I want to track.

And I have 2 event procedures:

Private Sub Form_AfterDelConfirm(Status As Integer)
    If Status = acDeleteOK Then Call AuditChanges("RecID", "DELETE")
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Call AuditChanges("RecID", "NEW")
    Else
        Call AuditChanges("RecID", "EDIT")
    End If
End Sub

It works fine when I open the specific form (sf_Details) and make the changes, but doesn't work when that form is a subform. I tried things like Me!sf_Details.form but no with no luck.

Could you give me some guidance?

Regards

 

[Galaxiom]

I tried things like Me!sf_Details.form but no with no luck.

You tried it where?

Also you have not said what kind of module you have the sub in.

Best be explicit about the scope of the procedure. (Public or Private)

It is a good practice to always declare the way arguments are passed. (ByVal or ByRef). Otherwise they are passed ByRef and any changes to the parameters in the procedure are passed back to the calling procedure. Without declarations anyone reviewing the code will not immediately know if you are intending to pass back through the parameters.

Better to pass the control object rather than its name. That way you don't need to refer to the form it is on. This might fix your problem if I have correctly guessed how you are using this procedure.

A control is not a field. Names for controls including the word "field" are misleading.

Public Sub AuditChanges(ByRef IDControl As Control, ByVal UserAction As String)
 .
 .
  ![RecordID] = IDControl.Value

(BTW Value is the default property of a Control so does not need to explicit.)

A Sub can be run without the Call keyword if you don't put parentheses around the arguments. The parentheses cause it to be treated as a function and require the Call because there is no equation in the line.

AuditChanges IDControl, "EDIT"

Since there is a specific list of actions to be passed these would be better done as Long with Enums instead of passing a string. This way an invalid action cannot be passed to the Sub.

Public Sub AuditChanges(ByRef IDControl As Control, Action As ActionEnum)

Public Enum ActionEnum
  
    actionNew = 1
    actionEdit = 2
 etc
End Enum

http://www.cpearson.com/excel/Enums.aspx

 

[neseri]

Thank you Galaxiom! I tried to change it to Public Sub but that did not resolve the issue. Also, I tried to follow your explanation but I am not sure if I understand everything as I am quite a beginner. However, this is the exact code that I used: fontstuff.com/access/acctut21.htm and applied to my database. When I open a single form and edit data it works as expected, but when I do that from the main form that contains "details" form, nothing happens.

 

[Accessing]

I believe you have to do the following:

1) Open the subforms and make both subs (Form_AfterDelConfirm and Form_BeforeUpdate) PUBLIC subs. If you don't make them public you can't call them from another form, not even if you include them as subforms there.

2) Get the proper name of the child object. When you refer to it, you don't refer to the name you gave to the subform when you saved it, but to the name it has as an object in the main form. To get that name you should right click the properties of the subform as an object of the main form, but NOT on the form properties inside the object. See the picture attached (it's worth a thousand words after all), do it as in the left example, not as in the right one.
In this example the name you are looking for would be "Child0"; when you don't set this name yourself Access usually sets it automatically to Child0, Child1, etc. (as many times as you insert a subform or subreport).

3) In the code of the form, where you want to call the action, use the following commands:

Forms!FormName!ChildName.Form.Form_AfterDelConfirm(Status)
Forms!FormName!ChildName.Form.Form_BeforeUpdate(Cancel)

Where FormName is the name of the main form, and ChildName is the name I referred you to in step 2.

I hope it works.

 

[neseri]

Thank you Accessing for detailed instructions. I just tried to follow steps that you wrote and I get an error "Invalid outside procedure" and (Status) gets highlighted. I think you are very near the solution to my problem, there is just some detail missing here.

 

[neseri]

Thank you Accessing for the detailed explanation. When I do as you have said I get an error: "Compile error: Invalid outside procedure"

I added this code

Forms!FormName!ChildName.Form.Form_AfterDelConfirm(Status)
Forms!FormName!ChildName.Form.Form_BeforeUpdate(Cancel)

on the top of the code in the main form, but did not add anything else there except this code. Of course, I changed the form names as you instructed.

I think that the solution is very close to your code.

 

[chnj]

I understand first 2 steps, but could you explain the third step in more detail? How to put the quoted code - as it is or as a part of another piece of code?

 

[The_Doc_Man]

For Each ctl In Screen.ActiveForm.Controls

Here is a bit of an issue. The subform is not the active form if you call this from the main form and the main form is not the active form if you call this from the subform.

I would suggest that you pass the name of the form you wanted to test as a parameter in the call. When the main form calls this for itself, it can pass "Me.Name" as the form name, and when it calls this for the subform (which it would have to do explicitly) then it could pass the name of the form that is in the subform control's .Form property.

Alternatively, if you put this in a Form_AfterUpdate event, BOTH the main and the subform can call it with "Me.Name" - but with the understanding that you have two events, and if there were no changes in one of the forms, the event wouldn't fire.

 

[pbaldy]

Not sure why posts 5 and 6 were moderated, but I've approved them. The code must be within a sub or function, it can't be by itself. If needed:

http://www.baldyweb.com/FirstVBA.htm

 

[neseri]

Here is a bit of an issue. The subform is not the active form if you call this from the main form and the main form is not the active form if you call this from the subform.

I would suggest that you pass the name of the form you wanted to test as a parameter in the call. When the main form calls this for itself, it can pass "Me.Name" as the form name, and when it calls this for the subform (which it would have to do explicitly) then it could pass the name of the form that is in the subform control's .Form property.

Alternatively, if you put this in a Form_AfterUpdate event, BOTH the main and the subform can call it with "Me.Name" - but with the understanding that you have two events, and if there were no changes in one of the forms, the event wouldn't fire.

Is there any simple solution to make it work when calling from the main form?

I will not need it from the subform as well. I will need it only from the main form, but I wanted to confirm that, as it is now, works from the subform.

Thank you.

 

[neseri]

Not sure why posts 5 and 6 were moderated, but I've approved them. The code must be within a sub or function, it can't be by itself. If needed:

Thanks for approving.
You say it can be for example "on enter"?

 

[chnj]

Forms!FormName!ChildName.Form.Form_AfterDelConfirm(Status)
Forms!FormName!ChildName.Form.Form_BeforeUpdate(Cancel)

If this is the part of the procedure it will work in your opinion?

 

[pbaldy]

Thanks for approving.
You say it can be for example "on enter"?

Sure. Whatever event is appropriate to the task at hand. I was focused more on moderator duties than the thread content. For some reason these last two posts were also moderated.

 

[chnj]

What would be the best way to call this from main form if it is intention to use it only from the main form and not from the subform?

 

[pbaldy]

Your code appears to be intended to be called from the before update event of a form, given its use of OldValue.

 

[chnj]

Private Sub Form_BeforeUpdate(Cancel As Integer)
Forms![Main Form]![Sub form].Form.Form_AfterDelConfirm (Status)
Forms![Main Form]![Sub form].Form.Form_BeforeUpdate (Cancel)
End Sub

This way it doesn't work. It doesn't show error either.

Appreciate your help!

 

[The_Doc_Man]

Is there any simple solution to make it work when calling from the main form?

Here is your conceptual problem: Access itself is synchronous (though there is a chance that the Ace or Jet engines that are operating in the background can be multi-threaded). The controls and forms are treated synchronously even if Windows is running multiple threads. The issue is that Access was not coded to be be multi-threaded (to the best of my knowledge).

If you are calling this from an event on the main form, then the sub-form is NEVER current and Screen.ActiveForm is ALWAYS the main form, never the sub-form, because that is the only way an event would occur. In order to use this from form X it must be called from an event on form X. That includes if form X is a main, a sub, a sub-sub, or ten layers deep. If you want to catch changes on sub-form X you have to call it from sub-form X, in other words.