Form with two subform, Open/Load gets stuck randomly

[mdlueck]

Form with two subforms, Open/Load gets stuck randomly

Some time ago I mentioned this trouble with one form. The form has two subform controls on it. On the main form itself, processing gets stuck between finishing the Form_Open event, Form_Load never gets processed until I click somewhere on the main form.

I put some Debug.Print LOC's in the various events, Immediate window output as follows:

Form_subform_quality Form_Open
Form_subform_quality Form_Load
Form_subform_metooling_gaging Form_Open
Form_subform_metooling_gaging Form_Load
Form_quality Form_Open
Form_quality Form_Open, Exit_Form_Open
[B][COLOR=Red](pause until click)[/COLOR][/B]
Form_quality Form_Load

This morning I tried rebuilding the quality form from scratch, and the fresh form had the same random pause, so I rolled back to last evening's version of the FE DB.

When I clean up the DB ( http://www.access-programmers.co.uk...to_Decompile_/_Compact_/_Compile_an_Access_DB ), then for sure the first time quality opens, it does not get stuck / pause. Subsequent times I open it, then randomly (50% of the time perhaps) it gets stuck.

The area of code processing gets stuck at is as follows:

Private Sub Form_Open(Cancel As Integer)
  On Error GoTo Err_Form_Open
Debug.Print "Form_quality Form_Open"
  'Test if this form is being opened programatically vs manually
  If architecturecheck_CheckHowStarted() = False Then
    'Caught someone being sneeky... we outtahere!
    Cancel = True
  End If

  [B][COLOR=Blue]Me.fldprojecttitle.BackColor = vbRed
  Me.fldprojecttitle.Value = "Loading of form got stuck, please click here!"[/COLOR][/B]

Exit_Form_Open:
Debug.Print "Form_quality Form_Open, Exit_Form_Open"
  Exit Sub

Err_Form_Open:
  Call errorhandler_MsgBox("Form: " & TypeName(Me) & ", Subroutine: Form_Open()")
  Resume Exit_Form_Open

End Sub

Private Sub Form_Load()
  On Error GoTo Err_Form_Load
Debug.Print "Form_quality Form_Load"
  'Call shared code to make sure the form is set properly
  Call uiutils_ResetForm(Me)

Does anyone have suggestions how to resolve this random pause-until-click?

I have resorted to a bit of very annoying duct tape to alert people to trouble. :banghead: Such should NOT be necessary! Suggestions?

 

[tranchemontaigne]

Does the pause go away when you set these form properties outside of the VBA module or from a function call to an external code module?

I've seen odd behavior similar to this when too many lines of code appear in a single module. In those instances it appeared that only the first X lines of code would load, but the particular lines of code that would load were nto counted sequentially from the top of the module, but based upon the sequence in which code was executed at run time.

 

[mdlueck]

I seem to be able to aggravate the issue by opening the Quality screen for the same Part record repeatedly. If I select a different Part to open the Quality screen for, the screen is far more likely to load correctly / completely.

At the time of the error, there is no code path difference between displaying the same cached data vs looking up new data.

 

[tranchemontaigne]

I'm still wondering if it is a problem with too many lines of code in your form module.

Are there any blocks of code that could be copied from the form module and pasted into a general module (aka overflow module)?

This approach has worked to resolve similar problems for me many times. I have one form in one application with 4 different 'overflow' modules to store code that will not run correctly when stored in the main form module.

I know it's poor form to make the function scope span the entire application when it is only called from a single form, but you do what you need to do...

 

[mdlueck]

I'm still wondering if it is a problem with too many lines of code in your form module.

Well here is a couple of forms in comparison...

 1078         Form_parts
 1070         Form_partsedit
 307          Form_quality

Granted, quality is the one with two subforms and lots of event passing between them. The form itself does not have much code compared to other very stable forms.

Are there any blocks of code that could be copied from the form module and pasted into a general module (aka overflow module)?

All of the code could go into a separate module. I observed that coding style in one sample recently... hook button events direct to calling the subroutine in the separate module.

So is there some limiting factor in Forms that they have issues with lots of code, that modules do not have?

 

[tranchemontaigne]

I'm not sure what the limiting factor is with modules. I just know that when I would write and test code in isolated modules, then copy paste them into pre-existing modules the code would get stuck on lines of code in the pre-existing module where it would not get stuck without adding the new code. I've never seen the limit described in writing, but from empiracle observation the place where the code would stick would vary upon which lines of code were executed first (e.g. 10 lines in the first subroutine, 200 lines in a function, 50 lines in a second subroutine, etc). The number of total lines the code in a module seemed to vary depending upon the number of characters inside each line of code. It's as if a buffer overflow happens and the code fails without a run-time error because of the overflow. Modules often start to misbehave around 1350 total lines of code. I first strugged with this on a form containing 5725 lines of code that were specific to the forms scope. The code ultimitaley got spread across 1 form module and 4 overflow modules.

The same problem exists in general modules and form modules. Simply reducing the amount of code within modules where code would hang without catching an error proved tremendously successful in overcoming the issue. CAVEAT: I encounter this issue in MS Access 97 and MS Access 2K. At work we do not support or develop for newer releases of MS Office.

NEW THOUGHT: Could you have a break point that did not clear in the compiled code when it was cleared from the canonical code? Sometimes a bit sticks.....

 

[mdlueck]

NEW THOUGHT: Could you have a break point that did not clear in the compiled code when it was cleared from the canonical code? Sometimes a bit sticks.....

My DB cleanup process took care of that nonsense...

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk...to_Decompile_/_Compact_/_Compile_an_Access_DB

Which is where it sounded like you were going with your earlier text in that post #6.

Well I have managed to get ported to modules the first three Form events: Open / Load / btnFAEdit_Click. CoMpLiCaTeD!!!

The stall loading the Form is still alive and well.

Note: It does not appear (at first managing to get the code to compile) that a call to a separate module may Cancel Opening the Form. I get my error box when I open the form manually, then the form opens anyway. So, at the very least I would need to come up with alternate syntax to be able to perform:

Cancel = True

within an external module.

Starting to snow storm. I best get going home. g'night all.

 

[mdlueck]

Well I have been all through the code this morning. Found various subtle goofs / nothing major.

1) I specifically adjusted the code to never rely on cache data in the FE DB. That cut down on the form getting stuck, did not eliminate the bug. So I re-enabled FE caching. It is very odd that relying on cached data makes the stall happen more. (shrug)

2) Some times when selecting a different Part record to display the Quality screen of, the Quality screen gets stuck. Usually it is entering the Quality screen of the same Part, but NOT ALWAYS.

3) I am going to leave the Event code in the Form, not move it out to separate modules. I was not able to get the Form_Open Cancel to work in that coding style scenario.

I guess it is as good as it is going to get in Access / VBA. I shall rely on the red text I added per #1.

 

[mdlueck]

"A picture worth a thousand words." Here is what I have been working on...