Microsoft Access Has Encountered a Problem

[gray]

Hi

WinXPPro
Access 2002 SP3

I've spent 2/3 days trying to resolve the dreaded "Microsoft Access has encountered a problem and needs to close" etc.

My Db (size=8328Kb) will open OK but when I "form-view" a particular form it crashes out with the above. Unfortunately, the problem is intermittent as sometimes the form will open perfectly OK. Equally, when running with debug/break it fails at differing points in my code.

In terms of structure, my main form has 4 subforms. They are not bound to one another. Instead the mainform contains several comboboxes which modify subform record and control sources (via the combobox after-update events). On opening of the main form, I clear and rebuild the various default sources.

So far, I've tried:

1. Opening a new DB and Importing all objects.
2. Creating a copy of my DB, deleting the offending form and re-importing it from a backup.
3. The normal compact and repair (many, many times)
4. Compiling the DB and sorting any compilation issues
5. Tried a /decompile but got some weird "licence not found problem" which I was unable to fix.
6. Downloaded the Jet4 Compact utility which failed with a very helpful... "Error" !
7. Carrying out the Help, Detect and Repair
8. I found an article re- combobox source change issues but that pertained to Access 2003.
9. Tried serially chopping out chunks of code in the Form-Open event to see if I could isolate the problem - certainly nothing obvious.
10. I even found an article on the use of the Hatten true-type font - bizarre but I followed it anyway.
11. I saw something about running too many threads but I didn't fully understand it?

Can anyone advise on the best way to debug my problem or perhaps expand on my points?

Thanks

 

[jjturner]

You may dread doing this, but have you tried starting from a blank form and copying over the controls and code into that (including blank start-overs for your subforms)?

It may or may not have anything to do with the Access crash you're running into, but I know there's a limit to how many controls you may add and changes you may make to a form, and possibly with 3 subforms going, you may be getting into design-limit issues - depending how 'busy' your form is . . .

 

[gray]

Gulp!

Hi JJ

Yes, indeed, they are quite busy... and at present I use lots of columns in the comboboxes too... I had a horrrrrrible feeling it might be some design limit.... so unlesss I can explicitly debug the problem, it looks like a redesign is on the cards.

Thanks for the advice.

 

[jjturner]

Sorry to hear about the degree of complexity -
This post addresses some of those limitation issues.

But now that you're being confronted in earnest with a re-design, you may want to consider working from the ground up and conceptually rework an efficient data model and table structure (i.e., fully Normalized) - which of course bears fruit with so many downstream efficiencies such as reducing the number of controls needed on a form . . .

Well, best wishes on a redesign - hope it gets you to a place with many fewer headaches (and controls)!

Cheers,
John

 

[gray]

Hi

Quick Update for anyone reading this thread...

I chopped out a number of 'nice-to-have' controls and it did, indeed, solve my problem... just a little surprised that this caused an intermittent fault... one might have expected it to be the same each time around.

Anyway, it works OK now ... thanks to JJ and others for their help.

 

[Endgame]

Sorry for resurrecting an old thread, but I came across some additional correlations that may be of help to someone in the future who encounters this wonderfully descriptive problem.

I have two forms which give me the "MS Access has encountered a problem and needs to close...." message and crash.

The first form has many tabs and many controls on each tab. Yes, yes, yes - I consider myself admonished at not having a normalized table and form set. I will get out the cat-of-nine-tails when I get home and give myself a proper thrashing.

The second form has no tabs and relatively few controls which are filter criteria. The form displays lots of data that is filtered by the criteria. One sets the criteria and then presses a button which activates a Form.Recalc

The crash/erm occurs when both forms are open and the Form.Recalc button is pressed.

I hope stronger minds than mine will expand on this, but it appears to me that I am close to the maximum number of control handles and the Form.Recalc method on the second form attempts to grab more control handles than are available.

 

[gray]

Hi Endgame

Actually, I've now seen this issue arise in a variety of circumstances... not only when opening a form but also when closing, having just edited, it. It's particularly troublesome when adding, cutting/pasting controls or even changing their position on the Form. Indeed I am at the point where having made even a small change, I close the form and close Access to ensure the edit is saved (as you might know, when Access starts itself up again after a crash it seems to go back one generation the result of which is the loss of one's most recent edits).

Anecdotally, I noticed, when moving/adding controls etc, that if I click on other tabs or controls immediately afterwards crashes are avoided?? This makes me wonder if it is a memory issue whereby the click on other controls frees up memory resources?? This might account for your symptoms of entirely diverse forms crashing?

There are umpteen threads out there re- Access crashes some of which speculate about the max number of controls over a form's life being breached.. i.e. not only the existing count but all previous deleted controls too. Try as I might, though, I could not find any that explained how to 'reset' this counter.

It may be that you will have to create new forms and cut/paste controls from old to new. You will of course lose all your form events .... BUT I discovered that by switching 'form has module' off and on and then cutting/pasting old form module to new form 'blank' module, the form's events are restored!! Got to be worth a try Endgame??

Sadly, life's too short to get to the root cause of all problems... workarounds (in these cases; pruning controls, creating new copies of forms), it seems, are inevitable.

 

[Endgame]

Hello gray,

I suspect you are correct about life being too short to pursue the root cause of all problems, but I would have thought there would be some resolution on at least this one because of its prevalence. Oh well, I've thought wrongly before....

I'm with you on this one though in terms of rebuilding the form. I have literally spent weeks trying to trap the error cause only to find the precipitating conditions seemingly morph from one day to the next. I suspect you are correct in that there are a variety of conditions that can cause the ERM and s.b.a.d.

Cheers and happy form rebuilding (and rebuilding)(and rebuilding),
Endgame