Baffling behaviour on Form close

[Petrol]

I have a form, F02, with a subform, subfrm_UAC. I can open and close the form, in either form view or design view, multiple times without problems; but if I open it in form view, then switch to design view and try to close it, Access pauses for about 7 or 8 seconds then crashes, offers to take a backup of the .accdb, asks for the password twice, then re-opens it. As far as I remember it didn't do this until I added the subform, but now even if I delete the subform the issue remains.

I have rebuilt both the form and subform by copying their controls to new forms, but the issue remains. Decompiling and recompiling doesn't help. I attempt to do a compact and repair, but it now says "You attempted to open a database that is already opened by [me on my computer]". Normally in the past this has worked.

Does anyone have any ideas how I can tackle this problem?

[NeoPa]

Hi Petrol.

It's hard to know what specifically may be at play here. M365 has some known issues with crashes around this time apparently. M365, and Access 365 for our purposes, is on different release schedules depending on what you select to be included in when installing (or you can switch too, if you choose). Rolling back to a version that predates the crashes may be an option to try. Not necessarily as a permanent solution but at least to determine what, where the problem is.

Another approach is to import just these items into a blank, new database and see if the problems persist.

Another issue to look at is what code or macros may be triggered. The Form_Close() event procedure (or macro) is of particular interest as that is triggered when switching from Open to Design View.

If all else yields nothing & the problems persist then try rebuilding the forms themselves again, bit by bit, and making note of the point where they start to exhibit the same problems.

[Petrol]

Thanks for those thoughts. I'll start (next week) by rebuilding the forms from scratch (rather than copying the controls) and see how we go.

[NeoPa]

Another thing to check out is current issues with A365 which are covered at Another Access Process Not Closing.

NB. This is a valid link to an Access MVP site so not spamming.

Only you can say if this is relevant to your situation. If not then no harm, no foul I suppose.

[Petrol]

Sorry about the delay. I got somewhat delayed in following up on this, both by the disappearance of the old Bytes forum, and concurrently by the above-mentioned Access 365 bug of not closing the msaccess.exe process properly. However the latter has apparently been fixed in the latest update.

In the meantime I have rebuilt the form and its subforms from scratch (twice!). The results are inconsistent. The form works OK when first rebuilt (i.e. I can switch back and forth between Design and Form views repeatedly), but after I build a subform and put it on, Access crashes when closing or switching from Design view. I have now taken the subform off again, but the main forms till crashes as described in the OP when closed or switched from Design view :-(.
Maybe it's something to do with the way I put the subform on (Create new empty form, save, drag into position on the main form). In my latest iteration, both are unbound (though I have previously tried it with binding).

[NeoPa]

To be honest, this sounds like an issue with Access as opposed to one with your design.

Be aware that there are many ways that you might not have the fixed version even if a fix has been released. You need to be on the correct channel (a whole topic in itself) and even when a release has been made to the one you're on there are (sometimes?) delays as it gets out to different areas in a staggered way.

I know I can add no more myself to what you may be doing wrong. I strongly suspect that's because there's nothing you are doing wrong, but the possibility exists there's simply something I'm not aware of. My use of subforms is almost entirely based on looking into things in order to answer questions but everything you describe makes sense to me.

[jimatqsi]

I cannot add a lot helpful at this point but I may have a similar problem. I have an app that runs on about 20 different machines. Some of those machines have the runtime only version of Access installed. One of those runtime only machines was crashing when it ran a particular form. I couldn't fix it, but eventually I realized that a subform on that form was no longer being used. So I deleted the subform and now that workstation no longer crashes when they run the form.

I cannot get into design mode on a runtime only machine, so I couldn't test your particular circumstance. But there are some similarities in our situations.

Did you try NeoPa's suggestion about creating a new database and importing all your database objects into that? With the option of importing the problem forms or building from scratch there. I think I'd give that a go.

[Petrol]

Thank you both for your thoughts. They are both comforting (maybe it wasn't my fault after all) amd discomforting (maybe I'll never be able to fix it).
The Access development team at Microsoft must have tools that would allow them to analyse this sort of problem. I suppose there is no way an ordinarry person like me can enlist their support to resolve the problem?

To be honest, I didn't try NeoPa's suggestion of creating a new database and importing all my database objects into that. I quailed at the thought, having 28 tables, 151 queries, 80 forms and subforms, 57 reports and subreports and a unknown number of modules, macros, subs and functions to deal with. But I did try making some random changes to a subform on one of my other forms, and it gave no trouble. However, I can't continue to develop the system as it is, having to restart Access every time I make a change, so I'll have to try putting anew form and subform into a new database and see how we go from there.

[jimatqsi]

One question about your attempt to Repair the database. Did you start the database with the return (Shift) key pressed so no startup activity happened? And then go directly to the Compact and Repair tool? That would make sure nothing gummed up the works before you could try the repair.

Moving to a new database is not so challenging. You actually have a small number of objects as compared to many Access databases. You don't have to manually rebuild everything, you can import most of it. Actually, you can import everything but it wouldn't be wise to import the form that is giving you problems. From the menu, you just go External Data / New Data Source / From Database / Access and follow the bouncing cursor.

But you could try some additional research before doing that. Maybe put a message box in every form level event. The message should identify the event being processed. For example,

Private Sub Form_LostFocus()
    MsgBox "Form_LostFocus()"
End Sub

Private Sub Form_Close()
MsgBox "Form_Close ()"
End Sub

Since the error happens even after you deleted the subform, I'd just work with the main form that crashes. If you trap enough events, you'll eventually learn the event that may be involved with the crash.

[NeoPa]

Hi Jim.
Just corrected your code layout & suggested you might mean Shift for the Bypass key rather than Return.

@Petrol.
Importing all the items consists simply of selecting them all from the screen found during the import. There's even a "Select All" button so numbers make barely any difference.
As Jim says though, perhaps exclude any items you aren't sure about and re-add them manually.

Good luck anyway :-)

[jimatqsi]

Thank you, sir, you are correct about that bypass key, of course. My mind and my fingers get out of sync quite frequently.

[NeoPa]

I hear you Jim. I'm in the same boat - so let's hope she stays seaworthy for a while yet eh?

[Petrol]

I have spent the last few days creating simpler and simpler versions of the problem and trying to find some commonality in the results. There are various ways of recreating the problem. For example, I made a brand new database containing nothing but a new form 'Form1' and a subform 'Subform', Form1 contains nothing byt a command button which makes the subform visible. (it is invisible because in the desired production version there are several alternative subforms any one of which can be made visible by command buttons). The subform contains nothing but a label.
If I click the button when the subform is already visible, the problem (Pause for 7 seconds, crash Access, re-open the DB) occurs . If Subform is not already visible, it works. However, if I then copy the Command button and paste a duplicate of it, with exactly the same OnClick code (Me!Subform.Vis ible = True), the First copy of the button works but the second copy causes the crash. The properties of both buttons are identical apart from their names (Command1, Command2) and tab stops (1,2).

Tomorrow I'll try putting message boxes in all the event proceduyres as Jim suggested, but ... ??

[jimatqsi]

Wow, this one is fascinating. This might not help but I remember tracking down a crash one time that led me to examining Windows logs. There is a log somewhere that will tell you something about the crash. I can't remember now how to find the necessary log, maybe someone else can add that info. And then the message found might be too generic to help.

Given the weirdness of your problem, I'm thinking the problem and solution is probably not anything in your database.

Does this happen on every workstation you run it on? Sure sounds like a problem in the environment. But I imagine there re many other buttons on many other forms that do not exhibit such odd behavior.

Could you eliminate the data driver from the equation? Make a copy of the database with all the data needed in local tables. Or change the recordsource for the main form to a query that returns literals for every field. ODBC drivers can do strange things.

[Petrol]

Actually, I don'neeed to compact and repair, and I don't need to import objects from my live system, or to eliminate ODBC data drivers, as I can reproduce the problem very simply on a clean database. So I suspect it must be something in my system environment (Lenovo laptop, Windows 11 with latest H2 update, Microsoft® Access® for Microsoft 365 MSO (Version 2409 Build 16.0.18025.2003 0) 64-bit). I don't have anther computer to try it on, I would be interested to know if anyone else can reproduce the problem.

1. Create a new database; save and then delete the default table Table1 (or don't; it is simply to eliminate another possibility);
2. Create an unbound form ("Form1") with default properties;
3. Create another unbound form ("Form2") with default properties, and shrink it so it's smaller than Form1;
4. Put an unbound text box ("Text0") on Form2;
5. Drag Form2 onto Form1 as a subform;
6. Put a command button on Form1 (without using control wizards, to keep it simple);
7. Build an OnClick event procedure for the command button, with some simple action such as Me![Form2]![Text0] = "A"
8. Save Form1, then swap back and forth between Design View and Form view a few times (just to confirm that it does that OK);
9. Open it in Form view and click the command button;
10. Swap to Design view then back to Form view (or simply close it from Design view).

At this point Access usually crashes on my system; it offers to take a backup then re-opens the database. (if occasionally it doesn't, repeat step 9 and click inthe subform before repeating step 10.
If for any reason that doesn't crash Access, make a copy of the command button, with a similar OnClick action, and try steps 8 and 9 wihth the copy button. That's another sure-fire way of crashing Access on my system. There are lots of variations of this, all using subforms, that cause the problem to appear on y system. However my live application has numerous othe examples of forms with subforms.

Final bizzarre feature: If I put an EMPTY ONnUnload event procedure (not even a comment in it) on Form1 I crashes when I simply open Form1, switch to Design and back to Form view, even without touching the command button.

I am not making this up. It reslly happens, reproduceably.