Solved

refresh on form crashes db

Posted on 2013-01-25
22
288 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

12 Experts available now in Live!

Get 1:1 Help Now