?
Solved

Access form stuck between open and load event

Posted on 2012-04-04
7
Medium Priority
?
583 Views
Last Modified: 2012-04-05
Hello,

I have an access form that stalls between the open and load events.  I put a message box in the open event of the form and it fires.  However the load event never fires.  I've commented out all the code within the load event as well as compacted and repaired the database.  The form was working fine earlier in the day.  This form is populated via a linked sql server table (view).  The view opens fine when I open it by itself. This is a Access 2007 database
0
Comment
Question by:chtullu135
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 37808636
Run this procedure on your db: (and post the code in the Open event)


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

mx
0
 

Author Comment

by:chtullu135
ID: 37808813
Hello mx,

I went ahead and went through the procedure you posted.  I was able to get the application going to now see where it was hanging up.  The form seemed to hang up when it hit PopulateMainComboBoxes.  This is the procedure I use to populate the comboboxes at runtime

Private Sub Form_Load

 ClearComboBoxes
 '   PopulateMainComboBoxes
 '   LoadRetrieve

End Sub

Private Sub PopulateMainComboBoxes
   Dim cnn as ADODB.Connection
   Dim rst As ADODB.Recordset
   
    Set cnn = New ADODB.Connection
    On Error Resume Next
   
   
    cnn.ConnectionString = cStrOLEDBConnectionString
    cnn.ConnectionTimeout = 0
    cnn.Open
    Set rst = cnn.Execute("dbo.spRetrieveGFPList")
    rst.MoveFirst
    Do While Not rst.EOF
        Me.cmbFilterByGFP.AddItem Nz(rst!GFP, "")
        rst.MoveNext
    Loop
   
    Set rst = cnn.Execute("dbo.spRetrieveWorkStreamList")
    rst.MoveFirst
    Do While Not rst.EOF
        Me.cmbFilterByWorkstream.AddItem Nz(rst!WorkStream, "")
        rst.MoveNext
    Loop
   
   
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing


End Sub
0
 
LVL 75
ID: 37808821
ok ... so, where is ... it hanging up ?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 77

Expert Comment

by:peter57r
ID: 37809870
I don't know if it's the way you copied and pasted your code into the question, but you appear to have created subs that don't have  () at the end of their declaration.. I don't know how it is possible to do that.  Can you check that your actual code does have the () for both procedures.
0
 

Author Comment

by:chtullu135
ID: 37811846
Yes I have () for both procedures.  One of the things I found was that I had the following line
On Error Resume Next
This was masking the actual problem which is a timeout error.  Upon further investigation, I found that the following stored procedure "dbo.spRetrieveGFPList" was running very slowly.  I'm now in the process of optimizing it.
0
 

Author Comment

by:chtullu135
ID: 37812142
Hello DatabaseMX,

It is hanging up on
Set rst = cnn.Execute("dbo.spRetrieveGFPList")

I am getting a timeout error.  I then executed the stored procedure and it took 40 seconds to complete.  It returned 17 rows
0
 

Author Closing Comment

by:chtullu135
ID: 37812783
Thanks for the help on the decompile.  That really helped to get the form up and running enough so I could trouble shooting.  I optimized the query and then set the connection command timout to 300 .  That portion of the form is now running great.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 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