Switching forms crashes Access

K

kagard

Greetings:

My application has a menu form that lets users jump to other forms in
the database. Switching from one particular form back to the menu
causes Access to crashes. If I close this form and return to the menu,
it's fine. If I leave it open and ctrl+f6 back to the menu form,
Access goes down hard.

The application has several dozen forms, but only one (frmProjects)
causes this problem. This is the only form that contains tabbed
subforms. I removed the subforms one at a time and found that the 10th
of 12 subforms (frmPayAppsOverview) was causing the problem.

Then I began removing controls from the subform and found that the
Amount Due text box was causing the error. It's control source is:

=GetPayAppAmtDue(Nz([lngAppNbr],0)

This calculation works properly when I display the main form and
subform. Since this function runs 3 queries and uses some domain
aggregate functions, I thought it might be causing the problem, so I
commented out the code and just had it return a value and exit. Even
so, it still crashed when I switched back to the menu.

Running the function in the Immediate window works with the menu and
project forms open.

There are no lost focus event procedures associated with the form or
subform.

The subform does contain a hidden textbox with the foreign key value
that links it o the main form.

I have compacted and repaired the database.

The users need to be able to leave this form open to gather
information as they fill out other forms and do research, or I would
just close it each time.

To sum it up, my menu and form display and calculate properly
individually. Removing an essential calculated control fixes the
problem, but isn't an acceptable solution. I've spent hours on this
and I'm out of idea.

TIA

Keith
 
T

Tom Wickerath

Hi Keith,

Try opening your database using the undocumented /decompile option. If you
only have one version of Access installed, you can click on Start | Run, and
enter:

msaccess /decompile

The next database that you open will have it's compiled VBA code discarded.
If you have more than one version of Access installed, then create a shortcut
whose target points to the full path of msaccess.exe with this optional
switch included. For example:

"C:\Program Files\Microsoft Office 2003\OFFICE11\MSACCESS.EXE" /decompile

Open the suspect database while holding down the Shift key the entire time,
to prevent any startup code from running. After opening the database, do a
compact and repair, again holding down the Shift key. Then open any code
module and compile your VBA code. If this does not fix your database,
continue on with the next paragraphs.

Create a brand new database and immediately disable the NameAutocorrect
feature (see: http://allenbrowne.com/bug-03.html for reasons why you want to
do this). Then import all objects from the suspect database into the new
database, one group at a time. In other words, import all tables (but not
linked tables), then import all queries, then all forms, etc. While Access
will allow you to import all objects in one operation, the experts at FMS,
Inc. (a Microsoft Partner), have stated that it is best to import objects one
group at a time (Reference:
http://www.fmsinc.com/ubb/Forum12/HTML/000285.html).

Recreate any linked tables from scratch. Access can cache a lot of
information about linked tables, which may no longer be valid, so it's always
best to recreate the linked tables from scratch. When importing local tables,
make sure to check the option to import relationships, menus and toolbars,
and import/export specs. If any of the local tables in the source DB are
hidden, you'll need to first unhide them. You will need to set the checked
references to match the source database, along with any startup options set
under Tools > Startup. Going through this process often times solves
corruption problems, because you get a new set of the hidden system tables
(the tables whose names start with "MSYS"). These system tables are updated
appropriately as you import objects.

This may sound like a lot of work, but it really isn't. Creating a new
container DB, disabling NameAutocorrect, importing all objects one group at a
time, re-establishing any linked tables, setting startup options, and setting
references to match the source DB is usually a fairly quick procedure. When
you are in the Visual Basic Editor, in order to check that the references
match the source DB, you should do a Debug > Compile ProjectName as well.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

[email protected] said:
Greetings:

My application has a menu form that lets users jump to other forms in
the database. Switching from one particular form back to the menu
causes Access to crashes. If I close this form and return to the menu,
it's fine. If I leave it open and ctrl+f6 back to the menu form,
Access goes down hard.

The application has several dozen forms, but only one (frmProjects)
causes this problem. This is the only form that contains tabbed
subforms. I removed the subforms one at a time and found that the 10th
of 12 subforms (frmPayAppsOverview) was causing the problem.

Then I began removing controls from the subform and found that the
Amount Due text box was causing the error. It's control source is:

=GetPayAppAmtDue(Nz([lngAppNbr],0)

This calculation works properly when I display the main form and
subform. Since this function runs 3 queries and uses some domain
aggregate functions, I thought it might be causing the problem, so I
commented out the code and just had it return a value and exit. Even
so, it still crashed when I switched back to the menu.

Running the function in the Immediate window works with the menu and
project forms open.

There are no lost focus event procedures associated with the form or
subform.

The subform does contain a hidden textbox with the foreign key value
that links it o the main form.

I have compacted and repaired the database.

The users need to be able to leave this form open to gather
information as they fill out other forms and do research, or I would
just close it each time.

To sum it up, my menu and form display and calculate properly
individually. Removing an essential calculated control fixes the
problem, but isn't an acceptable solution. I've spent hours on this
and I'm out of idea.

TIA

Keith
Click to expand...
 
K

kagard

Hi Tom:

Thanks for your reply. I tried the decompile / recompile, but it
didn't fix the problem.

NameAutocorrect was already set to none on all my tables.

It seems like a lot of work to recreate linked 44 tables from scratch
and import all their data. Do you think there is a good chance of this
clearing up the problem? (No guarantees, I know.)

Thanks again for your help.

Keith
 
D

Douglas J. Steele

Importing or linking 44 tables can be done in a single step.

Go to File | Get External Data | Link Tables. (or File | Get External Data |
Import)

Select the source database from the file dialog that appears.

Click on the Select All button before clicking on Okay.
 
K

kagard

Importing or linking 44 tables can be done in a single step.

Go to File | Get External Data | Link Tables. (or File | Get External Data |
Import)

Select the source database from the file dialog that appears.

Click on the Select All button before clicking on Okay.
Click to expand...

Hi Doug,

Yes I know I can import or link in a single step, but Tom said,
"Recreate any linked tables from scratch.", which I took to mean
"Recreate them one field at a time, table by table.". I guess I
misunderstood. Thanks.

Keith
 
K

kagard

Importing or linking 44 tables can be done in a single step.

Go to File | Get External Data | Link Tables. (or File | Get External Data |
Import)

Select the source database from the file dialog that appears.

Click on the Select All button before clicking on Okay.
Click to expand...

Hi Doug,

Yes I know I can import or link in a single step, but Tom said,
"Recreate any linked tables from scratch.", which I took to mean
"Recreate them one field at a time, table by table.". I guess I
misunderstood. Thanks.

Keith
 
K

kagard

Greetings:

Looks like the decompile trashed the db. Now, if I try to enter a
single line of code in any module and save it, Access crashes. I tried
importing the database objects into a new blank database, one object
type at a time (tables, then queries, then forms) and Access crashes.

It was either the decompile, or the installation of Office 2003 sp3,
which I did yesterday in response to a recommendation from Microsoft
when I reported an error. Of course, I'm supposed to be finishing the
project today. Boy am I stuck.

Keith
 
K

kagard

Greetings:

Looks like the decompile trashed the db. Now, if I try to enter a
single line of code in any module and save it, Access crashes. I tried
importing the database objects into a new blank database, one object
type at a time (tables, then queries, then forms) and Access crashes.

It was either the decompile, or the installation of Office 2003 sp3,
which I did yesterday in response to a recommendation from Microsoft
when I reported an error. Of course, I'm supposed to be finishing the
project today. Boy am I stuck.

Keith
 
T

Tom Wickerath

Hi Keith,

Does Access crash if you attempt to import tables (but not linked tables)
plus relationships and any menus and toolbars and import/export specs [using
the Options >> button] ? If so, then you likely have corruption in a table.
Can you import all queries successfully? How about any macros? Try to
identify exactly when the crash occurs during import. Is it a specific form?

Hopefully, you have a recent backup copy of your database. (I see that I
should add a statement to indicate make a backup first).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
M

MyCVWork

Hi Tom:

All my tables are linked, and I can link to them without a problem in
a new blank db. When importing queries, Access crashes on the last
query. When importing forms, Access imports the first 17 and then
quits. I don't have any macros.

I do have a recent backup and I'm working from that now.

Keith
 
T

Tom Wickerath

Hi Keith,

It sounds like you have a corrupt query and a corrupt form. Gee, it's
probably good that you've discovered this sooner, rather than later.

Can you open the original database, and view the SQL statement for the query
in question (View > SQL View, when in query design)? If so, try copying the
SQL statement, and pasting it into Notepad as a temporary repository. Save
the text file. Then try copying the SQL statement from the text file, into
the SQL View for a new query in the new container database that you are
creating.

For the form in question, try copying any VBA code to a text file as well,
and save that. Then set the Has Module property to No. Then try using the
undocumented SaveAsText command, from the Immediate window in the corrupt
database, ie:

Application.SaveAsText acform, "NameOfForm", "C:\Temp\NameOfForm.txt"

Example for Customers form in the sample Northwind database:
application.saveastext acForm, "Customers", "C:\Temp\Customers.txt"

Then, open your new container database, and use the opposite command from
the Immediate Window. For example:

application.loadfromtext acForm, "Customers", "C:\Temp\Customers.txt"

Finally, replace any class module associated with this form, by creating a
module (if the original form included a class module) and then copying the
saved VBA code from your text file and pasting it back in.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
K

kagard

Hi Tom:

SaveAsText works great! I wrote this to extract all the code from the
corrupt db:

Public Sub ExportAllFormCode()
'Extracts the code for all forms in the database.
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
For Each obj In dbs.AllForms
Application.SaveAsText acForm, obj.Name, "C:\TLMCode\" _
& obj.Name & ".txt"
Next obj
End Sub

Did the same thing for my reports. Queries came out a little weird -
not SQL.

Thanks for you time and help.

Keith
 
T

Tom Wickerath

Hi Keith,
SaveAsText works great!
Click to expand...

Cool! It sounds like you've been able to completely recover your database
now. Is this correct? And, more importantly, did this solve the initial
problem that you posted, ie. switching forms causes Access to crash?
I wrote this to extract all the code from the corrupt db:
Click to expand...

Yes, that type of code can work, although, I have encountered situations in
the past where something weird was corrupt in the class module associated
with the form, where using SaveAsText / LoadFromText was only able to recover
the form if I first (manually) saved the VBA code associated with the form to
a text file. Don't ask me why this worked, but it just did.
Queries came out a little weird - not SQL.
Click to expand...

I think Access MVP Arvin Meyer may have just the code you need:
http://www.datastrat.com/Code1.html

Also, see this posting from Frank Miller [MSFT]
http://groups.google.com/group/micr.../7e3f15c638abe4e7?hl=en&lr=&ie=UTF-8&oe=UTF-8


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
K

kagard

Hi Tom:

Yes, I'm back on track. I switched from Access 03 to 07. It read the
file that was failing in 03, works faster, is more stable, and handles
some application issues that were frustrating in the previous version.

I'll check out the links once I cross the finish line with this
project. Thanks for your help with this.

Keith
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Forms offline? 1
Sharepoint not capturing staff name when automation runs 0
Populate main form from subform record 0
Access form continuous 1
Set as Default Template not working 0
Optionbutton Not working 3
Subform Requery Criteria 1
Requery unbound textbox 1

Top