Solved how do I log my database event sequence?

[MikeT1941]

Ok so that is not a very good title.
I have my new database thanks to all the help I have received here. However, most times I get two or three refreshes on a photo in a subform before it stabilises. Obviously I have too many saves/refreshes/whatever.
Can I log the sequence that it goes through without putting a breakpoint on every sub and writing them all down?

All help welcome.
Mike

 

[Gasman]

Put a debug.Print with appropriate string in each event and run the process?

Perhaps only run on a global/tempvar being set?

 

[MajP]

I do not think there is any native way to do this, but that would be a great feature. I think you could write a logger to do this. You could use vba extensibility to some code to each procedure in order to facilitate not having to write your own code. However, that would be a lot of work for one db, unless you can find some code that someone already did.

 

[CJ_London]

subforms open before the main form and if there are multiple subforms, they open in order (of subform control creation I believe) and each subform will refresh those already opened and finally the main form will do it's own refresh.

Events to look at are the open, load and current events. Perhaps also look at the resize and activate events which occur between load and current.

Open event only occurs once when the form is initially opened
Load event each time the form is populated i.e. initially and subsequently if the recordsource is modified
Resize occurs initially after load and subsequently if the form is resized (but I don't think it does if the recordsource is modified)
Activate occurs when the form has the focus
Current occurs when a record is selected

There are a number of ways of stopping the flicker, some work better than others depending on where the issue is. With a subform issue probably the best way is in design view, leave the sourceobject property as blank. Then populate it in the main for current event with something like

if mysubformcontrol.sourceobject="" then mysubformcontrol.sourceobject="NameOfForm"

 

[The_Doc_Man]

I built a logging routine that was sensitive to a "trace" flag. I had an innocuous-looking title box that, if I double-clicked it, it set or cleared a global flag that enabled logging or disabled logging to a table I built for the purpose of tracking where I had been. Then when I called the routine, I included that global flag as an argument and a constant text string as another argument.

Public Sub LogWaypnt( Logit as Boolean, WayPntName as String)
    Dim SQLString as Text
    If Logit Then
        SQLString = "INSERT INTO LogTable ( LogWhen, LogWhat ) " &
            " VALUES ( " & Cstr( Timer() ) & ", ' " & WayPntName & " ' ) ;"
        CurrentDB.Execute SQLString, dbFailOnError
    End If
End Sub

The columns I used for LogTable were LogWhen (SINGLE), LogWhat( Text(80) ) and that Timer() function is seconds and fractions thereof since midnight. It isn't ultra-high resolution, but it is good enough to show what is going on.

So once you have this, you call it at the start of each routine. Then it is a matter of keeping the global "Logit" flag TRUE or FALSE depending on whether you did or didn't want logging.

 

[MikeT1941]

Thanks everyone- I'll start at the top and work down- or at the bottom and work up, but I'll report back

 

[MikeT1941]

Well, as a first attempt, I put breakpoints on every sub in both forms and the only call was if I loaded a new photo into the sub-form, so logging doesnt seem to be able to give a record of where i've been. Further investigation gave a refresh of the subform on every alteration in a calculated field on the main form, which accounted for one of them, or more, since I was turning pounds, shillings, pence and farthings into £ decimal.
Doc, please can you explain to me in words of one syllable, where the values for WayPntName come from. I'm missing something.
Thanks
Mike

 

[jdraw]

Mike,

Can you provide a zipped copy of your database?
I was visiting this topic a while back when trying to assist a user.

 

[isladogs]

Several years ago I added logging code to the event handling section of every procedure in a very large commercial database (FE approx 150 MB).
It took a lot of time to setup but that soon proved worthwhile
This logged exactly who/what/where/when triggered the error then 'silently' sent me an email with the details
After an initial flurry of emails, I was able to fix all application bugs and I never received another 'error email'.

You could theoretically do something similar but placing the logging code at the start of each procedure.
Then check the logging table to get the event sequence.
However, you may well find that the procedure which errors may not do the event log...unless the code is also part of the erro handler.

Anyway, its certainly 'doable'. Whether its worth the time and effort is a different matter

 

[MikeT1941]

JDraw. Thanks for the offer.

Attached a copy of the db plus a very small number of photos in a folder which will need to go directly on C: drive. I have realised that, having not reduced any of the photos, their size may be contributing to the problem as they take a finite time to load each time during a refresh ( I assume).
Anyway, if you load and look for say, Hinks ( a "Client"), you are in the area of the db to which the photos apply, around 1838. I'm thinking of adding a refinement to also log payment dates, as the length of credit taken by the clients is considerable. Having said that, there are some novel barter items, including two hats (over £100 at todays prices), stabling for a horse, 6 silver knives and even some beer!
Thanks for help
Mike

Dropbox link: https://www.dropbox.com/s/hvy9yfxuqxoss0p/Tomkinsondb.zip?dl=0

 

[The_Doc_Man]

Doc, please can you explain to me in words of one syllable, where the values for WayPntName come from. I'm missing something.

It is a unique name you make up arbitrarily that represents that particular call and no other.

So, if you have an event logger call in your Inventory form's FORM_BEFOREUPDATE event, you might supply the name "InvFrmBefUpdEvt" as a constant in that call. But then the same call in the Shipping form's Form_Current event might get the arbitrary name "ShpFrmCurEvt." Heck, in a particularly long-winded routine I once wrote, I put in intermediate logging points and called them "WayPnt001" and "WayPnt002" etc.

This kind of logging is for your benefit so make the names something that is meaningful to you and that is unique to each different place that you call the logging routine. NOTE that if you use the TIMER routine as I suggested, those times include SECONDS since midnight and fractions of a second. But to be honest, that timer that gets used for this is the 1/60 of a second clock that is the computer's low-resolution timer. You could still see a lot of items with the same timing, given 1.6 to 3.2 Gigaherz system speeds. If EXACT order is important, include an autonumber in the logging table and then sort by that field.

 

[MikeT1941]

It is a unique name you make up arbitrarily that represents that particular call and no other.

So, if you have an event logger call in your Inventory form's FORM_BEFOREUPDATE event, you might supply the name "InvFrmBefUpdEvt" as a constant in that call. But then the same call in the Shipping form's Form_Current event might get the arbitrary name "ShpFrmCurEvt." Heck, in a particularly long-winded routine I once wrote, I put in intermediate logging points and called them "WayPnt001" and "WayPnt002" etc.

This kind of logging is for your benefit so make the names something that is meaningful to you and that is unique to each different place that you call the logging routine. NOTE that if you use the TIMER routine as I suggested, those times include SECONDS since midnight and fractions of a second. But to be honest, that timer that gets used for this is the 1/60 of a second clock that is the computer's low-resolution timer. You could still see a lot of items with the same timing, given 1.6 to 3.2 Gigaherz system speeds. If EXACT order is important, include an autonumber in the logging table and then sort by that field.

Ah, right, I can understand that! But since it doesn't appear that the VB holds the key to the problem as it doesn't stop on any of my breakpoints, how do I tie a name to the SQL statements-or whatever they are- that's very obscure to me.

 

[MikeT1941]

Terrible to be ignorant!

 

[The_Doc_Man]

Where I was using the equivalent of CurrentDB.Execute "SQL statement", vbFailOnError, I often tested CurrentDB.RecordsAffected (and a couple of other things) and concatenated that information into the name. That way I knew when my SQL was doing something and when it was doing nothing. I also made it a point to have error trap handlers do logging as well so I could see that, for example, I got a "No Current Record" error just after Waypoint 103. If you have 80 characters to play with, you can log a place name, an error or status or value, and a short message. It is all a matter of your imagination.

 

[jdraw]

Mike,


I have downloaded your database.

Anyway, if you load and look for say, Hinks ( a "Client"), you are in the area of the db to which the photos apply, around 1838

Unsure of what I am to load or what to do re processing. I have added Debug.prints to every form event and every function/sub, but have no instructions of how to "operate" your database.
My routines with display proc name and timestamp for each routine as it is executed to show the logic flow.

 

[The_Doc_Man]

Terrible to be ignorant!

True, but MUCH more terrible to be uneducable, and I don't think THAT applies to you. Ignorant (in the sense of "not educated on a particular subject") is correctable. But as comedian Ron White says, "Stupid is forever." So just be prepared to learn and you'll do fine.

 

[MikeT1941]

Mike,


I have downloaded your database.

Unsure of what I am to load or what to do re processing. I have added Debug.prints to every form event and every function/sub, but have no instructions of how to "operate" your database.
My routines with display proc name and timestamp for each routine as it is executed to show the logic flow.

Sorry about that. Mea Culpa.
The operating form is ClientHistory. If that is open, you see there are 380+ entries. Each of those has a Client, who may have more than one "job".
The earlier and original "Jobs" are merely records of papers or data held "concerning" various people/companies within the organisation as it progressed through time. Earlier it was a succession of "one man bands" and later progressed to a more broad structure. Each "Job" , mostly, has one or more pieces of info about it, which are appended in "Photoform and listed in t_attachments_local.

Hope that helps
I started off with a very different structure with the info incorporated within the database, but was disabused of that notion by Mike60smart who vastly helped by pointing me in the right direction.

It was only after starting off that I realised that the 500+ pages of a ledger stretching 10 years from 1837 would provide interesting info about life in those days that I added it. So far I have only entered 25 pages worth, so only 475 to go. But I'm locked down and no one else will have the time. Then I've got all the "jobs" over the last 50 years that I do know about.

happy to talk over WhatsApp/Skype after contact via PM

Mike

 

[jdraw]

Hi Mike,

I loaded your jpgs based on their path in the zipfile. They are in c:\ledger pages 21-40\.
I found those via frmClientHistory and adjusted the existing path to the path I have and can see the images load.(So the locations of those files has updated your t_Attachments_Local info).
Regardless of moving through the various records and displaying images, my routine only shows Photoform Form_Load() 1 time. I do see the image flash instantly (totally expected and acceptable from my view).

I am attaching a png of one of the frmClientHistory records.

Here are samples of the code I generate to include the Debug.Print with the form control event or function/sub call. Although only Photoform Form_Load() was actually actioned.

Option Compare Database
Option Explicit

Private Sub Combo112_Change()
Debug.Print Me.Name & "   Combo112_Change()"      '14-Jan-21 3:30:55 PM ---modified code DebugFlow

      If Me.Dirty Then

         Me.Dirty = False

      End If

End Sub

Private Sub Combo112_Exit(Cancel As Integer)
Debug.Print Me.Name & "   Combo112_Exit(Cancel As Integer)"      '14-Jan-21 3:30:55 PM ---modified code DebugFlow
    Me.Refresh
End Sub

Private Sub Combo126_Enter()
Debug.Print Me.Name & "   Combo126_Enter()"      '14-Jan-21 3:30:55 PM ---modified code DebugFlow
Me.Combo132.Requery
End Sub
.....
....
Private Sub CmdClose_Click()
Debug.Print Me.Name & "   CmdClose_Click()"      '14-Jan-21 3:30:55 PM ---modified code DebugFlow
'Form_PhotoformLarge.Visible = False
'Forms!Form_PhotoformLarge.Movesize = (50,50,25000,25000)
End Sub

Private Sub Form_Load()
Debug.Print Me.Name & "   Form_Load()"      '14-Jan-21 3:30:55 PM ---modified code DebugFlow
Me.Refresh
End Sub

Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
Debug.Print Me.Name & "   Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)"      '14-Jan-21 3:30:55 PM ---modified code DebugFlow
 If Not Me.Dirty Then
      If (Count < 0) And (Me.CurrentRecord > 1) Then
          DoCmd.GoToRecord , , acPrevious
      ElseIf (Count > 0) And (Me.CurrentRecord <= Me.Recordset.RecordCount) Then
          DoCmd.GoToRecord , , acNext
      End If
  Else
      MsgBox "The record has changed. Save the current record before moving to another record."
  End If
End Sub

I don't know if any of this is helpful. Good luck.

 

[MikeT1941]

Jack

Many thanks for your comments. I've had another look at the size of some of the attachments and reducing them in size seems to get rid of the double flicker that was annoying me. I've tried your code and that is so easy but really powerful way of charting whats going on, so I shall incorporate it with a global variable to turn it on and off, per Doc's comment.
So again, much appreciated and also thanks to Doc and Isladogs.

Mike

 

[Gasman]

Real shame you can not use something like Parent.Name.Event.Name for that debug.print statement