Sudden Change in Event Behavior? (1 Viewer)

[Frothingslosh]

Okay, in a nutshell, I have this form that generates its underlying SQL when it is opened and assigns the recordset as part of a procedure called by Form_Open. One of the things I do is check to see if the recordset is empty, and if so, I notify the user. However, the users have requested that the message pop up AFTER the form is visible.

So what I had done, and what had worked until roughly two weeks ago, is skip the recordcount check (which is separated into its own procedure for this very reason) when the UpdateRecordset procedure runs as part of Form_Open, and run it directly at the end of Form_Load. (There is a call to the record count check at the end of UpdateRecordset, but it's buried inside an IF...THEN.)

Until a week or two ago, this worked fine. The form would get a recordset assigned, the form would load, and then the message would come up if the recordset was empty.

Now, however, instead of UpdateRecordset completing its processing, when the forms RecordSource property gets set, processing is immediately passed to Form_Load, then Form_Activate, then Form_Current, and only then does UpdateRecordset continue processing. This is new behavior; it only started happening in the last two weeks. (I did some extensive modificationof the view behind this screen two weeks ago, which meant I opened this screen roughly a bajillion times and this never happened.)

I'm curious - has anyone else run into this, is this new behavior, or was it always supposed to act like this and my system was just running wrong the last 9 months?

I can provide the code if you want, but it's technically working as written. It's just the logic is now messed up due to the sudden change in event calling, so I need to determine if I can just undo the workaround I put in 9 months ago (when calling the warning code during the Form_Open sequence caused it to display BEFORE the form was visible) or if I need to figure out a solution that automatically checks to see if the form is visible to the user before displaying the message and delays the message if it isn't...without being able to rely on knowing whether or not Form_Load has fired yet or not.

 

[isladogs]

Don't know the answer to your question though in general Form_Open is too early to run code.
Here's a possible work round that's worked for me when I've had issues of this kind.
Try putting the code on a Form_Timer event with interval=1000 or similar.
Then change interval to 0 immediately after the code runs to disable the timer.

 

[Frothingslosh]

Form_Open is actually a perfectly good time to run code if you're using it to dynamically generate the SQL for the form's recordset, as well as if you want to run a series of checks and prevent the form from opening if they fail. The former is what I have to do with this form, since there are enough optional parameters that building them all into a single query slows recordset navigation unacceptably (this form touches that 1.8 billion record partitioned table I have to work with), as does using a filter. The form needs to be updateable, so a stored procedure or table-valued function is right out. And the users go screaming to management if I wait until Form_Load to load the recordset and they see #ERROR# in all the fields even for a fraction of a second.

Also, as I mentioned, all the code in that procedure not involved in creating and assigning the recordset is buried behind an IF...THEN looking for a variable that isn't set to TRUE until Load has completed, specifically so it won't run as part of the Open.

I'll see how your timer suggestion works out.

 

[Frothingslosh]

But regardless of whether or not one should ever use Form_Open, I take it this is the first time you've heard of assigning a recordset causing a procedure to temporarily stop processing?

 

[Frothingslosh]

Actually, I found a different (if slightly annoying) option.

Since all the code was working just fine and the issue was just caused because the events started firing in a weird order, I stripped the procedure call to the notification procedure out of the update procedure, and then updated all 6 events that can trigger a recordset update to ALSO call the notfication. It's 5 additional lines of code overall, but it both prevents the false positive I've been getting and also no longer cares whether assigning the recordset during the Open event triggers Form_Load or not (just in case it suddenly reverts to how it worked before now).

 

[The_Doc_Man]

OK, you are dynamically changing the .Recordsource of a form by dynamically generating the SQL in the Form_Open routine. Which makes me wonder if at Form_Open time the original .Recordsource is blank (i.e. looks like an unbound form).

I cannot tell you how Access works internally at this level, but I know that unbound forms DO NOT trigger a Form_Current event. Verified by experiment.

This makes me wonder whether there is something not normally seen that would cause the code to act as though a "re-open" had to occur. I'm not going to swear on a stack of Bibles (which you could have guessed anyway) but the actions related to "Form_Open" involve opening all of the objects associated with the form - including, of course, the Form object, the Class Module object, the .Recordsource's recordset, and I believe this also includes any controls that have recordsets implied by their .RowSource properties.

When you dynamically change the form's .Recordsource, you have in essence implemented a "late addition" to the list of things that have to be opened. Is there ANY CHANCE of a "DoEvents" being somewhere buried in the code you were calling? Because I think the form has to re-evaluate what it has opened.

Also, have you had any patches recently? Microsoft is notorious for having patches that "fix" one thing and break a thousand things at the same time.

 

[Frothingslosh]

Our IT doesn't pass along MS patches immediately. I don't recall any patches coming through in the last two weeks, and I've been explicitly watching for one. Colin let me know that a patch came out in June that added additional themes for Access 2016, including a couple with non-white backgrounds. I have several users who get migraines from white backgrounds, so I've been pushing IT to clear that thing already. If they snuck a patch past me, it wasn't THAT one.

I also don't see any recent patch pushes in either my machine's or my corporate account's patch history.

There is no Do Events anywhere in the code being called. I just checked.

This is honestly the first time I've seen a Load event get forced by an action in the Open event. I've seen issues where code would mess with BeforeUpdate and AfterUpdate, but not this.

The form itself starts as unbound, but by the time the Load event fires, it's bound. I actually confirmed that by putting a debug.print and the event name in Form_Open, Form_Load, Form_Current, Form_GotFocus (not Activate, sorry), and the two extra procedures, UpdateRecordSource and HasNoRecords. The results were:

OPEN
UpdateRecordSource
LOAD
GOTFOCUS
HasNoRecords
GOTFOCUS
HasNoRecords
CURRENT

The current trace looks almost the same, without the second HasNoRecords result, but since I changed how the check works, I don't get the false error now. (I didn't add a Print after the Set rs = statement in UpdateRecordSoruce, but basically, processing returns to that procedure after that second GotFocus event. That's where the second HasNoRecords came from, and then once that's done, Current finally fires.)

Also, please keep in mind that behavior changed without a matching change in the code being used. While I was testing this screen a couple weeks ago, all the changes were being done on the SQL Server view that is the ultimate record source. And this issue never occurred during that testing. Since then, this form has not been modified.

 

[isladogs]

Frothy
The new black and grey themes were a more recent update. October I think.

 

[The_Doc_Man]

Now, however, instead of UpdateRecordset completing its processing, when the forms RecordSource property gets set, processing is immediately passed to Form_Load, then Form_Activate, then Form_Current, and only then does UpdateRecordset continue processing.

This is interesting. Your UpdateRecordset routine was called from Form_Open, right? What makes it interesting is that event flow cannot do that UNLESS there is some implicit DoEvents (you denied an explicit one). An event cannot interrupt another event. Events are linearized. The Form_Open routine HAS to have done an Exit Sub in order to leave event context, which means it would have executed a return from your subroutine first. Which means it CAN'T return TO that routine because it has already returned FROM it. The subroutine requires that something would be on the stack to give it a pointer of where to return, but the call order is wrong.

Or did I read that explanation wrongly?

 

[Frothingslosh]

Frothy
The new black and grey themes were a more recent update. October I think.

Ah, okay. Must have been thinking of something else. It's been an annoying week. Thanks!

 

[Frothingslosh]

This is interesting. Your UpdateRecordset routine was called from Form_Open, right? What makes it interesting is that event flow cannot do that UNLESS there is some implicit DoEvents (you denied an explicit one). An event cannot interrupt another event. Events are linearized. The Form_Open routine HAS to have done an Exit Sub in order to leave event context, which means it would have executed a return from your subroutine first. Which means it CAN'T return TO that routine because it has already returned FROM it. The subroutine requires that something would be on the stack to give it a pointer of where to return, but the call order is wrong.

Or did I read that explanation wrongly?

Nope, that has me baffled, as well. I did a line-by-line trace.

Form_Open called UpdateRecordset at the end.

UpdateRecordset processed through all the conditionals, processed the Set Me.RecordSource = SQL line, then jumped to Form_Load.

Form_Load ran a couple small procedures and then called HasNoRecords, which ran successfully before returning to Form_Load. After exiting Form_Load, processing went back to UpdateRecordset.

UpdateRecordset finished with the second call to HasNorecords.

Once HasNoRecords completed, the thread passed back to Form_Open, which finally exited, allowing GotFocus and then Current to run.

Ignore the first GotFocus event - that was from a stray Me.Visible = True that was left over from something I took out back when I was working out how to get this screen to NOT have hellacious lag.

 

[Frothingslosh]

Here's a snippet of the code. Other than my adding the comment about it, nothing has been changed in this part. The processor reaches the line in red, executes it, jumps to Form_Load, does all that other stuff, then returns to the line in blue. I tracked all that by stepping through the entire load process line-by-line.

   [COLOR="SeaGreen"] 'These are only needed if accessed via PIDA menu.  If from UWS, only the selected RecoveryID was staged.[/COLOR]
    If Nz(Me.OpenArgs, "") = "" Then
    
        [COLOR="seagreen"]'Apply Auditor ID filter if required.[/COLOR]
        If Me.cboProcessorID.Value <> "(ALL)" Then
            WhereClause = WhereClause & " AND VW_GroupAndMemberLetterData.ProcessorBadgeID = '" & Me.cboProcessorID.Value & "'"
        End If
        
       [COLOR="seagreen"] 'Apply Recovery ID filter if required.[/COLOR]
        If Me.cboRecoveryLookup.Value <> -1 Then    [COLOR="seagreen"] 'Displays to user as  '(ALL)'[/COLOR]
            WhereClause = WhereClause & " AND VW_GroupAndMemberLetterData.RecoveryID = " & Me.cboRecoveryLookup.Value
        End If
    End If
    
    [COLOR="seagreen"]'NOTE: For some bizarre reason, when this is called from Form_Open, processing stops after this next line and passes to Form_Load, then
    '   Form_Current, and only THEN does it continue to Set rs = Me.RecordsetClone.[/COLOR]
    [COLOR="Red"]Me.RecordSource = "SELECT * FROM VW_GroupAndMemberLetterData" & WhereClause & ";"[/COLOR]
    
    [COLOR="Blue"]Set rs = Me.RecordsetClone[/COLOR]

EDIT: This is pulled from UpdateRecordset.

 

[The_Doc_Man]

I know that having a change of .RecordSource also changes lots of other things for the form, like .Recordset and other properties related to the .RecordSource.

According to: https://support.office.com/en-us/article/recordsource-property-7a8ea3b5-cfc0-4da9-b394-44c440b9fc38 , dynamically changing a .RecordSource forces a Me.Requery, which I suppose should be expected.

I searched a few topics that I thought might make sense. The above link is the only thing I found. The article doesn't mention events. I focused on events but I have a feeling that what you did somehow went off the beaten path, as the phrase goes.

 

[JHB]

This behaviour is normal, the only way you can display a message after the form is visible, (with the setup you've), is to open the form in design view first, then open the form in normal view, example is attached.

 

[Frothingslosh]

JHB, the message box is displaying after the form becomes visible as intended, both before and after this odd behavior began. That's not what this is about. This is about the recordset assignment - which, by the way, Doc, I knew it called a requery, and have taken advantage of that before - suddenly triggering Form_Load in the middle of Form_Open when it hadn't been before. The code for that part has been in place for a couple months now, but this behavior only started within the past two weeks. Before then, it was completing Form_Open before moving on to Form_Load. There has been no code change affecting these events in that time.

EDIT: *THAT* was why I had that Me.Visible = True in the code: it forces the form to show before the MsgBox that immediately follows it. Looks like I need to put it back in before testing continues on Monday.

Example is attached.

 

[JHB]

Not only the recordset assignment triggering Form_Load in the middle of Form_Open, for each in the below code does the same, so I think there are more that would trigger Form_Open to run before Form_Load has finish.

Private Sub Form_Open(Cancel As Integer)
   MsgBox "OPEN EVENT"
   'Comment out the one after the other
   'Me.RecordSource = "" 
   Me.Text1.SetFocus 'Text box control on the form
   'Me.Visible = True
   
   MsgBox "OPEN EVENT"
End Sub

 

[The_Doc_Man]

Now here is the thing I don't understand.

suddenly triggering Form_Load in the middle of Form_Open when it hadn't been before

NORMALLY you trigger Form_Load by exiting from Form_Open because there are sequences of events and the Open, Load sequence is standard. I don't understand the "when it hadn't been before" part of your comment.

To my understanding, if you don't Cancel the Form_Open event, you WILL execute the Form_Load event. But if you have an unbound form, you will NOT execute the Form_Current event. It is the Current event that depends on binding, not the Load event. The ONLY way to stop a Form_Load is a Form_Open with Cancel=True.

Therefore, I have to ask: Did I miss something somewhere?

 

[Frothingslosh]

Nope. Form_Open is calling UpdateRecordSource, and halfway through URS, execution jumps to Form_Load. Once Form_Load is complete, execution returns to URS, then to Form_Open, then Form_GotFocus, then Form_Current (because by this point, there IS a bound recordset, and therefore a current record), then execution terminates.

Makes no sense to me, either, and I F8'd through the freaking thing. It wouldn't be bothering me so much if not for the fact that this behavior just recently started - before, it would be Open -> URS -> Return to Open -> Load -> GotFocus -> Current -> Done. Now it's Open > URS > Load > Return to URS > Return to Open > GotFocus > Current > Done.

Regardless, however, I rewrote the notification code so that the message will come up after the form displays and not before, without having to deal with which start-up procedures have run yet and which ones have not, so at this point it's more of an academic question. This isn't the kind of situation that normally comes up for me - typically, if I have to do an EOF check when a form is being opened, I can just check on Open and cancel it if the RS is empty. That was contraindicated here by business requirements.