Solved

refresh on form crashes db

Posted on 2013-01-25
22
289 Views
Last Modified: 2013-01-28
Whenever I refresh on a particular form it crashes the entire db. I'm pretty sure it's corrupt or at least haven't been able to find out why.

can someone help me with maybe a checklist on how to resolve this?
0
Comment
Question by:Shawn
  • 11
  • 8
  • 3
22 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 200 total points
ID: 38821002
Here is the 'checklist' :


Open the VBA Editor and from the menu ...Tools>>References ....
If you see any listed as **Missing: <reference name>, including the asterisks and the word Missing, the
you need to fix that first.

Then, follow this procedure:

****
0) **Backup your MDB BEFORE running this procedure**
****
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
Close the mdb after the Compact & Repair.
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Close the mdb.
6) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

7) Close the mdb
8) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an **example** of the command line syntax  (be SURE to adjust your path and file name accordingly) before executing the decompile:

Run this from Start>>Run, enter the following command line - **all on one line** - it may appear like two lines here in the post:
Also, the double quotes are required.

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\YourMdbNameHERE.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

AND ...
Once you get familiar with the Decompile idea (and ALWAYS make a BACKUP first!) ... you can add both Decompile and Compact/Repair to the Right Click menus in Windows Explorer, which I use multiple times daily:

Getting the Decompile and Compact context menu options
http://access.mvps.org/access/modules/mdl0039.htm (note:link my be temporarily broken - if so, let me know and I can upload).

mx
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 300 total points
ID: 38821007
try doing this
do first a decompile
see this link  http://www.granite.ab.ca/access/decompile.htm

then do a compact and repair

if the problem persists
create a blank db and import all objects


if the problem still persists,

you need to create a new form.

to make it easy, open the form in design view
select all the controls, Ctrl + C (copy) then paste them to the new form
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821021
mx:
have already done all those steps though I didn't hold the shift key while opening.
Access 2000 db running in A2002 environment. I went through every step again including the hold shift and form still crashes on refresh.

capricorn1:
I've already tried to creat a new form but perhaps I was missing something. I'll try again now and get back with results.
0
 
LVL 75
ID: 38821028
Holding down Shift key is just to prevent any start up code/form from running - unnecessarily.

mx
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38821030
you can also save the form as text file with

Application.SaveAsText acForm, "NameOfForm", "c:\folder\NameOfForm.txt"

in the new db, retrieve the form with

Application.LoadFromText acForm, "NameOfForm", "c:\folder\NameOfForm.txt"
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821042
>>if the problem still persists, you need to create a new form.

just did this. Created new blank form, copied all controls and added recordsource, saved. Closed. Re-opened. Ctrl-G copy/pasted procedures. Saved. Compact/repair.

refreesh and still crashing.

will try to save as text now. Question: can I save it as text then import into the Same db? er, I know i can but will this help or is it just garbage out = garbage in?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38821048
did you create the new form in the new db?
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821065
i created the new form in the SAME db and that didn't work. Creating the form in a new blank db now.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821073
created new form in new db with this Application.LoadFromText acForm, "NameOfForm", "c:\folder\NameOfForm.txt"

then dragged and dropped to current (old) db and still crashes
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821078
I even tried earlier to put everything into new db and refresh still crashed it.

Could it be there are too many controls or something?

the system is xp sp3 and I know there were issues a while back but I thought that was resolved.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38821079
don't use the old db, as i've said in my first post, import all objects to the new db.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:Shawn
ID: 38821081
>>don't use the old db,

done and still crashes
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38821083
<Could it be there are too many controls or something?>

in the new form that you created, start with a few controls

btw, how many controls do you have in the form?

Number of controls and sections you can add over the lifetime of the form or report = 754
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821089
>>btw, how many controls do you have in the form?
not sure how to count but a good 100 at a glance along with 2 subforms.
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 300 total points
ID: 38821095
the problem could be your subforms, do a process of elimination
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821098
ok, "relatively" good news. just deleted subforms and about half the controls and no more crash.

Now to work backwards.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38821105
try the loadfrom text, then delete one subform if the problem persists
do it again, now delete the other subform
once you find which subform is causing the problem,
create a new subform
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821111
ok, found the suform causing havoc. Strange thing is when I open it up directly and perform a refresh it doesn't crash.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38821113
it could be the relation of the subform with the main form,  codes called from main referring to the subform.. etc
0
 
LVL 1

Author Comment

by:Shawn
ID: 38821125
holy cow.

got it down to one control, TmpExchangeRate and it turns out it had an empty after update procedure.

Private Sub TmpExchangeRate_AfterUpdate()

End Sub

I deleted this and no more crash. Amazing. I had no idea something like this could cause such a headache.
0
 
LVL 1

Author Closing Comment

by:Shawn
ID: 38827594
I hope I split the points ok. The question was to help construct a checklist. I think it's a great one and will help me in the future too. I also gave points for including checking the subforms. I had no idea they could create havoc like that.

thank you,
Shawn
0
 
LVL 75
ID: 38827694
cool.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now