Access crashes when opening form

[Guest]

I have a problem that has boggled my mind all day. Advice needed please! A
problem with our main database just arose yesterday. When you try to open
one of the forms, Access crashes. Here is what I’ve discovered so far:

1) When I open backup versions of the database file (6 months old) that used
to work fine, they have this same problem now. So it does not appear that
the database was recently corrupted, as the old files (which worked fine) are
exhibiting the same behavior.

2) If I open these files as read-only (directly from my backup CD), the
problem does not exist.

3) I think I have narrowed down the problem to a particular field. If I
create a new form based on a new query that does not include this field, the
problem goes away. This field has never caused problems in the past.

Put it all together and it makes no sense to me. If you could point me in
the right direction towards solving this problem, I’d greatly appreciate it!
If you need more details in order to help me, please ask.
Thanks much, Diane

 

[Allen Browne]

Diane, can you give us some more info about this?
What version of Access is this?
What service pack? (Shows on the Help About screen.)

Tell us about the field you suspect.
Name? Data Type? Other properties?

Does a compact/repair help?
Here's a suggested sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

5. Try exporting the form using the undocumented SaveAsText.
Press Ctrl+G to open the Immediate Window.
Enter something like this:
SaveAsText acForm, "Form1", "C:\form1.txt"
Verify that this created a text file in the specified folder.
Then delete the form, and compact again.
Now try getting Access to recreate the form with:
LoadFromText acForm, "Form1", "C:\form1.txt"

If the export fails, the form is corrupt. Delete. Compact. Re-create. (You
may be able to copy any code you have in its module from the code window
before you delete it.)

 

[Guest]

Thanks for all your advice Allen. I only had a short period of time today to
work on this, but have not yet solved the problem. I am using Access 2003
SP3. The suspected field is a text field, and the field name is
EABU_PlantName.

I went through your suggestions and the final "export" operation crashed
Access. Here is what I truly do not understand: If I go back and open an
older archived version of this database that used to work fine (that is, the
form worked as expected and never caused problems), why does it appear that
the form is NOW corrupted? I cannot logically explain this. Same file, same
version of Access, same computer. What could have possibly changed?

I will hopefully be able to get back to this problem tomorrow. I'll provide
you with more details at that time, if it makes sense to do so. Thanks again.
Diane

 

[Allen Browne]

Okay, there's nothing odd about the text field, or its name.

SP3 for Office 2003 has only been released for a short time, and does have
some bugs. I'm not aware of any that should cause this problem, but given
that it's just started happening and happens even with older copies of the
database, it is a suspect. You might be able to test whether it is a factor
by finding a machine that only has SP2 for Office 2003, and seeing if the
database works correctly there.

You are not using Visual Source Safe, are you? It has known problems with
SP3.

This field is not a combo, is it? There are some issues with combos under
SP3 also.

There's not a subform linked on EABU_PlantName is there?

Is there anything else in the database that has the same name?

Hopefully you have Name AutoCorrect turned off, the decompile done, and then
compacted.

 

[Guest]

Allen -
You were correct in suspecting SP3. I didn't realize that our IT department
updated to SP3 on all machines.

My problem falls into the category "if a table with more than 31 rows is
assigned as a rowsource for a combo box, the application will crash". Yup -
that's it exactly!

Unfortunately, I can't even open the form in design view to make some
temporary changes that might allow us to limp along until such time as MS
fixes this mega bug. Any suggestions? Is there some sort of "safe mode" or
something?

Perhaps, in the meantime, I can see if the end user will allow me to reduce
the number of fields in the source table (I have a feeling there are many
unused fields), and just create a new form.

BTW, I've also had a problem with "compact on close" not working (it creates
db1, db2, etc, without compacting the original file), and I now realize that
it started happening right after SP3 was installed. (I see that other users
have posted the same problem). Any fixes for this that you know of?

So many hours I've wasted trying to solve these SP3-related issues!

Your help is greatly appreciated. Thank you!

 

[Allen Browne]

Excellent news. Tracked down.

If you don't need all those fields in the combo itself, you might be able to
create a query that uses only the fields you do use. Then using an SP2
machine, assign the query to the RowSource of the combo.

 

[Guest]

My user has agreed to reduce the number of fields in the table to 26, so
hopefully that should fix the problem. Is it correct that if I reduce the
number of fields in the table, the existing form should work OK? My fingers
are crossed.

I'm curious about your comment. The combo box in the form only uses one
field (EABU_PlantName), but the problem is that this field is in a table
(EABU_PlantInfo) with over 50 fields. This one field is the only one from
this table that is used on this form (the other objects on the form are from
a different table). Are you saying that there's some way to create a query
that will get around the problem of the "large" table? By the way, all of my
forms are already based on queries. I learned the hard way that that's the
best way to do it!

I wish I had an SP2 machine to work on, but our IT department is rather
incompetent and it wouldn't be worth the battle to try and set up a machine
just for this.

Do you have any knowledge/advice regarding the compacting problem that I
mentioned?
Thanks!

 

[Allen Browne]

Say your table has some 50 fields, but you only need 2 for the combo's
RowSource. You would create a query such as this:
SELECT DISTINCT EABU_ID, EABU_PlantInfo
FROM Table1
ORDER BY EABU_PlantInfo;

Now the combo's RowSource has only 2 fields, so the number of fields in
Table1 doesn't matter.

 

[Guest]

Allen - Thanks so much for your most recent advice. I implemented it and it
works perfectly (though my fingers are still crossed). Before I made that
change, I actually had to bring the number of fields down to less than 20 to
keep the db from crashing.

With your method, I was able to keep the old table (with the 50+ fields)
intact and still have a working database.

Thank you Allen!