Access form stuck between open and load event
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ok ... so, where is ... it hanging up ?
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.
ASKER
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.
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.
ASKER
Hello DatabaseMX,
It is hanging up on
Set rst = cnn.Execute("dbo.spRetriev
I am getting a timeout error. I then executed the stored procedure and it took 40 seconds to complete. It returned 17 rows
It is hanging up on
Set rst = cnn.Execute("dbo.spRetriev
I am getting a timeout error. I then executed the stored procedure and it took 40 seconds to complete. It returned 17 rows
ASKER
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.
ASKER
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.spRetriev
rst.MoveFirst
Do While Not rst.EOF
Me.cmbFilterByGFP.AddItem Nz(rst!GFP, "")
rst.MoveNext
Loop
Set rst = cnn.Execute("dbo.spRetriev
rst.MoveFirst
Do While Not rst.EOF
Me.cmbFilterByWorkstream.A
rst.MoveNext
Loop
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub