Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

refresh on form crashes db

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
Shawn
Asked:
Shawn
  • 11
  • 8
  • 3
3 Solutions
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
ShawnAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Holding down Shift key is just to prevent any start up code/form from running - unnecessarily.

mx
0
 
Rey Obrero (Capricorn1)Commented:
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
 
ShawnAuthor Commented:
>>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
 
Rey Obrero (Capricorn1)Commented:
did you create the new form in the new db?
0
 
ShawnAuthor Commented:
i created the new form in the SAME db and that didn't work. Creating the form in a new blank db now.
0
 
ShawnAuthor Commented:
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
 
ShawnAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
don't use the old db, as i've said in my first post, import all objects to the new db.
0
 
ShawnAuthor Commented:
>>don't use the old db,

done and still crashes
0
 
Rey Obrero (Capricorn1)Commented:
<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
 
ShawnAuthor Commented:
>>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
 
Rey Obrero (Capricorn1)Commented:
the problem could be your subforms, do a process of elimination
0
 
ShawnAuthor Commented:
ok, "relatively" good news. just deleted subforms and about half the controls and no more crash.

Now to work backwards.
0
 
Rey Obrero (Capricorn1)Commented:
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
 
ShawnAuthor Commented:
ok, found the suform causing havoc. Strange thing is when I open it up directly and perform a refresh it doesn't crash.
0
 
Rey Obrero (Capricorn1)Commented:
it could be the relation of the subform with the main form,  codes called from main referring to the subform.. etc
0
 
ShawnAuthor Commented:
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
 
ShawnAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
cool.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 11
  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now