Bad Data is Bad for Business #1 and #2

[Mike Krailo]

OK Pat, that at least gives me something to think over and see if there is a reasonable solution. I really like the idea behind this to expose the sequence of events on any given project. If I come up with something, I'll let you know.

The class modules use here was to uniformly log all form events for any given form without actually adding events explicitly. So every event can be stored in a table as you suggested to determine which ones to use or not use. Maybe use checkbox in a form to enable/disable specific event logging.

 

[Pat Hartman]

The class module MUST determine the name of the procedure in order to log it. That may be the insurmountable problem.

 

[cheekybuddha]

Here's some quick and dirty code you can run from your form's Load event that will add your LogEvent to all the event handlers.

It requires that LogEvent is a function and not a sub, and also changing the signature so that you pass the form name rather than the form object itself.

I haven't tested it properly, but it should give you a start. It's only useful for showing when vanilla events fire. If you want to have code in the events, then you will need to go down the class route.

Function LogFormEvents(frm As Form) As Boolean

  Dim prp As Property, ctl As Control
  Const DQ As String = """"

  For Each prp In frm.Properties
    If prp.Type = 8 Then
      If (Left(prp.Name, 2) = "On" And prp.Name <> "OnLoad") _
      Or Left(prp.Name, 6) = "Before" _
      Or Left(prp.Name, 5) = "After" Then
'        prp.Value = "=LogEvent(" & DQ & frm.Name & DQ & ", " & DQ & IIf(Left(prp.Name, 2) = "On", Mid(prp.Name, 3), prp.Name) & DQ & ")"
        Debug.Print "=LogEvent(" & DQ & "FORM: " & frm.Name & DQ & ", " & DQ & IIf(Left(prp.Name, 2) = "On", Mid(prp.Name, 3), prp.Name) & DQ & ")"
      End If
    End If
  Next prp
  For Each ctl In frm
    Select Case ctl.ControlType
    Case acTextBox, acComboBox, acListBox ' etc ... Add the rest of the controltypes you might be interested in.
      For Each prp In ctl.Properties
        If prp.Type = 8 Then
          If Left(prp.Name, 2) = "On" _
          Or Left(prp.Name, 6) = "Before" _
          Or Left(prp.Name, 5) = "After" Then
'            prp.Value = "=LogEvent(" & DQ & ctl.Name & DQ & ", " & DQ & IIf(Left(prp.Name, 2) = "On", Mid(prp.Name, 3), prp.Name) & DQ & ")"
            Debug.Print "=LogEvent(" & DQ & ctl.Name & DQ & ", " & DQ & IIf(Left(prp.Name, 2) = "On", Mid(prp.Name, 3), prp.Name) & DQ & ")"
          End If
        End If
      Next prp
    Case acSubform
      Call LogFormEvents(ctl.Form)
    End Select
  Next ctl
  LogFormEvents = Err = 0

End Function

Just put in your form's load event:

Call LogFormEvents(Me)

 

[kentgorrell]

If you use a class, you can specify which events.
Declare the class in your form's code and instatiatle it in the open event then call the class's Init method
In this example I use the class to do the job of code behind a continuous form.

Private WithEvents mfrm As Form 'Holds the form pointer
Private Const cnEvProc As String = "[Event Procedure]"

Sub Init(Frm As Form)
    Set mfrm = Frm 'Save a pointer to the form
    with mfrm
        if .DefaultView = 1 then
              .OnKeyDown = cnEvProc
              .OnCurrent = cnEvProc
        end if
        .OnClose = cnEvProc
     End with
 End Sub
 
Public Sub mFrm_Current()
' for a continuous form, highlight the current record
    mfrm.Controls("txtCurrent_ID") = mfrm.Controls("txtPrimaryKey_ID")
End Sub

Private Sub mFrm_KeyDown(KeyCode As Integer, Shift As Integer)
' Form's Key Preview must be set to true
    Select Case KeyCode
        Case vbKeyUp
            DoCmd.RunCommand acCmdRecordsGoToPrevious
            KeyCode = 0
        Case vbKeyDown
            DoCmd.RunCommand acCmdRecordsGoToNext
            KeyCode = 0

    End Select
End Sub

and if you have any code behind the form for an event, that code will run as well
don't forget to kill the class in the form's close event. but you can even get the class to do that.
Private Sub mFrm_Close()
Set mfrm.mcForm = Nothing
End Sub

 

[kentgorrell]

BTW "instatiatle" is just my word for instantiate.
and kill the form pointer in the class

Private Sub Class_Terminate()
'Clean up objects
    Set mfrm = Nothing
End Sub

or you may find your application slowing over time.

 

[arnelgp]

Private Sub mFrm_KeyDown(KeyCode As Integer, Shift As Integer)

you don't need to put code on the Keydown event?
on your class, if the form is Single form, and you press up/down it will go to next/previous record?
the default behaviour is to go to Next field.

you don't need to code it unless there is special action you want to take, the
Default is sufficient.

 

[regnewby2465]

Great video Pat and Tony. I will now be using BeforeUpdate the proper way.

 

[Pat Hartman]

Thanks @regnewby2465

 

[Pat Hartman]

@cheekybuddha Thanks but I don't see how this code is actually updating the form's class module to insert the call to the LogEvent() function as the first line of code.

 

[Pat Hartman]

Sorry this took so long. Tony and I got distracted. Here is the the second edition of

Bad Data is Bad for Business #2

 

[Gasman]

Is there a link to the actual demo database? I would like to play around with that for sure. Seeing the events in order like that is really nice.

I would lke to play with the demo as well please, purely for educational purposes.

 

[amorosik]

The video is certainly of great use for all operators who use the Microsoft Access environment
It could be even more popular if the audio could be understood even by those who do not understand English
In this sense, I invite the author of the video to upload it to Youtube or a similar platform that has an instant translator in all other languages

 

[Pat Hartman]

I cleaned up the demo a little but it will take some doing to make it work for your own forms.
1. Any form you want to test MUST be modified to add one line of code in each event procedure that you want to see fire. If the procedure already has code in it, place the call to the logging as the FIRST line in the procedure. If you are tracking validation, ALWAYS log the form AND control's Before and After update events, ESPECIALLY if you don't have your own code there which is where it should be.
2. You can import the objects into an existing database if you can't easily separate the form you want to test. Just make sure you import the two forms in the list, all the modules and which ever queries you need. I didn't test this so I don't have a definitive list.

Let me know what you think but try not to clutter up this thread.

 

[ebs17]

If you want to test your own forms, I need to figure out how to get the call's loaded into your form's events without interfering with your existing code.

This is where the little-noticed SaveAsText and LoadFromText methods could help. SaveAsText exports the complete form definition to a text file.

Application.SaveAsText acForm, "frmxyz", "D:\ex_F_frmxyz.txt"

The last part of the content of such a text file looks something like the content of the code module for the form:

...
CodeBehindForm
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Compare Database
Option Explicit

Private m_loa As Long

Public Property Get oa() As Long
    oa = m_loa
End Property

Public Property Let oa(ByVal loa As Long)
    m_loa = loa
End Property

Public Sub Show_oa()
    MsgBox Me.oa
End Sub


Private Sub Form_AfterUpdate()
    Show_oa
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Show_oa
End Sub

Private Sub Form_Click()
    Show_oa
End Sub

Private Sub Form_Current()
    Show_oa
End Sub

Private Sub Status_BeforeUpdate(Cancel As Integer)
    Show_oa
End Sub

Private Sub Stichwörter_AfterUpdate()
    Show_oa
End Sub

With simple string manipulation, one can find the event handlers of interest, get their names, and put the extra call into the handler. The completed file is then loaded back into the application using LoadFromText.

Looped through all the forms of interest with it - and that should have been it.

 

[Pat Hartman]

If you want to test your own forms, I need to figure out how to get the call's loaded into your form's events without interfering with your existing code.

The logging does not interfere in any way with your own code. You just need to add the logging procedure call as the FIRST line of code in the event.

Looped through all the forms of interest with it - and that should have been it.

Sorry, I can't make any sense out of the code you posted. If you have code that updates all the form's procedures, please post it. Also, the code you suggest would only add the procedure call to event procedures that already have code which as I already mentioned is probably not sufficient. And finally, you would have to somehow distinguish between event procedures and other procedures when processing the exported code. Although, there is no reason you couldn't add logging code to any function or sub even those not related to event procedures.

If anyone needs code to export all forms to text, I can provide it. I also have code to import the exported form from text which is NOT the same as replacing/updating the code module. Please provide the code if you have it.

I did provide a template form that has logging in all the form procedures. You just have to use that if you want to build a form to test.

 

[ebs17]

If you don't understand the hint, then ignore it.

 

[Uncle Gizmo]

With simple string manipulation, one can find the event handlers of interest, get their names, and put the extra call into the handler.

I recall, in a recent post, a member here mentioned that he modifies the code, as you suggest in a text editor.

I think the member was @NoLongerSet

 

[Pat Hartman]

If you don't understand the hint, then ignore it.

I know what the code behind the form looks like but I don't know what your code is intended to do. Is it supposed to give me some "hint" on how to identify and update only the event procedures I want to add logging to?

I use save as text all the time, it is part of my backup process and I do it automatically when I close an app that I have modified during development. Then I have code that will rebuild the entire app from the exported text files if necessary. It minimizes the potential to lose too much work from corruption.

But having to export the forms I want to add logging to so I can search for procedure names I want to update and then update them is way beyond the scope of this project. And it still doesn't come close to solving the problem.

I will absolutely not distribute an application that updates code in other people's databases. Way too much liability especially since I no longer carry E&O liability insurance.

 

[Pat Hartman]

Third Video. I gave a presentation of the logging database so you'll see more of how to use the form and other interesting aspects of how forms work.

So, in total, we have THREE videos plus the sample database.

Post 1 has the first video
Post 30 has the second video
Post 33 has the sample database
Post 38 now has the user group presentation video which is interesting because of the interaction with the audience and it shows more of how the forms work to display the events.