• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1267
  • Last Modified:

setting recordsource of live subform's subform through vba results in "this action will reset the current code in break mode" when closing any other form

I'm working with an Access 2003 (.mdb) database in Access 2007 with a MySQL backend. After some recent additions to the functionality of my application I noticed that I was repeatedly having the error "this action will reset the current code in break mode" when trying to close forms. The error message stays in an endless loop if I click "Yes" but it goes away if I click "No" only to return as soon as I try to close a form again.

After extensive troubleshooting and error trapping I have finally found what I believe to be the offending line of code--the line that changes the recordsource of a different form's subform's subform. I believe that this is the offending line of code because running this line from the Immediate pane by itself causes the error every time--no forms can be closed and the looping "break mode" error pops up. (just as with any function that includes this line). Here's the line:

Forms!frmContacts.Form!subfrmPain2.Form!subFrmListings.Form.RecordSource = "qryListingsAndProps"

Open in new window


What's the best way around this? I read on one forum that I can set the recordsource to SQL rather than a named query (the contents of the current query are being rewritten on-the-fly)but I tried this and it didn't work. I've been dealing with this for two days and I'm at a total loss.

Thank you in advance for your help!
0
evan stone
Asked:
evan stone
  • 12
  • 8
1 Solution
 
hnasrCommented:
Try to reproduce the issue in sample database in Access 2007.
If you can, then upload the sample database.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Wait a second, are you actually changing the code line?  

You only get this message if you are currently executing, have stopped code execution, and are changing the code in a way that would cause a re-compile.

Under normal execution, changing the rowsource/recordsource of an object will not generate this error.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Normally, you would do something like this:

Dim strRecordSource as string

Select Case Me.grpRecordChoice

  Case 1
      strRecordSource = "myquery1"
  Case 2
      strRecordSource = "myquery2"
  End Select

Forms!frmContacts.Form!subfrmPain2.Form!subFrmListings.Form.RecordSource = strRecordSource

 In this way, the recordsource changes, but the line of code itself never changes.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
In contrast, if your taking this:

Forms!frmContacts.Form!subfrmPain2.Form!subFrmListings.Form.RecordSource = "qryListingsAndProps"

and changing it to:

Forms!frmContacts.Form!subfrmPain2.Form!subFrmListings.Form.RecordSource = "someotherquery"

 while in the VBA editor, then you will get that error.

Jim.
0
 
evan stoneAuthor Commented:
This definitely happens during normal execution. I don't change anything in the editor to get this error but I _have_ executed this line from the immediate window to reproduce the error. Good idea about trying it in the sample database. I will do that now. Still convinced that it is related to a subform within a subform so I will need to re-create that first. Thanks!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<This definitely happens during normal execution.>>

 That doesn't make sense.  What you posted is the actual line of code?  What is it being changed from/to and how?

 What you showed would mean the recordsource would never change.  It would always be set to the same thing.

Jim.
0
 
evan stoneAuthor Commented:
Jim,

The line of code, as you are reading it, is executed verbatim. The code does not change. The contents of query "qryListingsAndProps" is being rewritten on the fly.

When I step through the code and get to this line, that's when the error occurs (no form can be closed--break mode error). Likewise, if I execute this line alone from the Immediate window, the error occurs.

I have not yet tried this in a sample database, but I did go back to an old version of the database that has always run flawlessly and contains the same form/query structure. I copied this line of code into the Immediate window and BOOM--the error occurred.

And, for the avoidance of doubt, I have decompiled and repaired multiple times. The only thing I have not done is start with a fresh database. At this point, I'm considering it, but I really don't think my database is corrupt because I got this error to occur in my old db just by running this one line of code.
0
 
evan stoneAuthor Commented:
Obviously, running this line does trigger Form_Current in the form named therein. I have a few lines of code that are executed on Form_Current, such as the disabling/enabling of a button, the requerying of a listbox and the changing of a few labels. I'm about to comment out these lines and try again... (though I will note that none of this has caused a problem before and these lines are executed, obviously, every time I flip through records in this form).
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<The line of code, as you are reading it, is executed verbatim. The code does not change. The contents of query "qryListingsAndProps" is being rewritten on the fly. >>

 So why are you executing it then?

Jim.
0
 
evan stoneAuthor Commented:
Do you mean why am I executing it manually or why am I executing it at all?

I'm executing it manually to verify that it is the line causing the problem. If I do not execute this line manually, the problem still occurs. Specifically, the problem occurs when this line is processed.

Did you see my above comment from 2hrs ago?
When I step through the code and get to this line, that's when the error occurs

I didn't know if the error was due to the cumulative effects of everything in the procedure and that this line was the straw that broke the camel's back if this line alone caused the error. To determine if it was this line alone I closed and reopened the database and ran this line alone, which indeed still caused the error.

In the normal running of the database, no one would ever execute this line manually. It's simply part of an existing procedure.

If instead you are asking why I have this line of code included in my database at all, I am including it simply to re-run the query. I heard that me.recordsource = me.recordsource is sometimes better than me.requery. This line is effectively the same as me.recordsource = me.recordsource.

I've tried both here. Both result in the error.
0
 
evan stoneAuthor Commented:
Ok... this is critical to note:  I just tried stepping through the code again, VERY SLOWLY.

No error.

I did it again slowly--still no error.

Then I ran through the code at normal speed instead of stepping through--ERROR.

So something is happening too fast for Access I guess. I considered this at first and put Timer + DoEvents pauses after every currentdb.execute line because I don't know any other way to make Access wait for a SPT query to finish. The wait time was three seconds and it didn't help. Maybe something else is going too fast...?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Do you mean why am I executing it manually or why am I executing it at all? >>

 No I meant the statement.  If the name of the query never changes, then just set it in the property sheet,  or don't bother with the query and place the SQL directly in there.

 But with your last comment, apparently you have other events on going, and setting the rowsource effects that.

Do you have a timer event firing off in the background anywhere?  Where is this statement located?

Jim.
0
 
evan stoneAuthor Commented:
No timers.

It's part of a select case statement in a combobox_change procedure.

I really appreciate your help with this. Also, please ignore my comment about it working if I step through the code slowly. Though that did happen...it was on a slightly different part of the db that was producing the same error with the same line of code.

I'm now back in the part of the db that produces the error whether I step through the code fast or slow.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
So the combo is un-bound then?

Something to check - insert above the line:

 If me.dirty = True then
    STOP
    Me.dirty = False
    dbEngine.Idle
    Me.Repaint
End If

If you hit the stop, continue on with f8 and see if you get the error

Jim.
0
 
evan stoneAuthor Commented:
It's a bug with Access. I'm certain of it now.

I just created a blank database with one table, three of my linked ODBC tables, two queries and one blank form. One query selects from the local table and the other query selects from the three linked ODBC tables with one inner join and one outer.

This test database has no code and I imported NOTHING from the database I've been working on.

From the immediate pane, with the blank form open, I did this:

forms!Form1.recordsource = "Query1" (the query with the local table)

The source was updated and the form was fine. I closed the form and reopened it. Then I did it again and again. No problem. Then I did this from the immediate pane:

forms!Form1.recordsource = "Table1" (the local table)

Again, no problem. The recordsource was updated live and the source was requeried. I closed the form--no problem.

Then I did this from the immediate pane:

forms!Form1.recordsource = "Query2" (the query with the ODBC tables)

***BOOOM***  The error returned. Can't close the form--stuck in the break mode error loop.

It's something to do with setting a complex ODBC recordsource live.

How do I get around this?
0
 
evan stoneAuthor Commented:
FYI, opening the query that pulls from the ODBC tables takes less than three seconds. It's not that complex and the network is not that slow.
0
 
evan stoneAuthor Commented:
And if this was not clear from my previous posts, please know that the form works fine with this complex query as a recordsource when it first opens and continues working throughout the day. The error only occurs when a user uses one of the functions that changes the recordsource. (Note again: the code is merely changing the recordsource to itself.)
0
 
evan stoneAuthor Commented:
One more note: I made a copy of Query2 and turned it into a SQL passthrough query (Query3). Changing the recordsource of Form1, while open, to Query3 [through VBA in the immediate pane] worked fine. So it looks like a query with local tables is fine and a passthrough query is fine, but a mildly complex query with ODBC tables, as a recordsource, cannot be changed while the form is open.

And unfortunately, if the source is a passthrough query, the records in the form are uneditable. I know I could get fancy and have separate edit modes or edit forms, but I'm really trying to keep everything in one place with maximum flexibility and functionality. My users want to be able to browse and edit all in one. There's got to be some solution here. Changing some kind of cache or timeout setting or something? Otherwise, I might even try the [gasp] MS Access 2010 runtime. (I don't like developing in 2010 much and I'm still hooked on the mdb format--can't help it)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
In terms of this being a bug, I think your jumping to conclusions there.  This is a bound form and there are operations being performed by Access.  You can easily have something going on in the background when you make the change.  

Did you try the test I suggested?

Aside from that, changing a recordsource at runtime is never a hot idea as there are a number of issues.  Besides making sure no other code is executing, events firing, or record operations going on, there's also the issue of having different fields in the data sources.

 As for workarounds, I can think of two:

1. Set the recordsource to a blank first, let Access/JET catch up, then set it to what you want.

2. Maintain multiple forms and switch between them and don't change the recordsource.

Jim.
0
 
evan stoneAuthor Commented:
Jim,

Thanks for that. Tried suggestion 1, to no avail.

Suggestion 2 involves a fundamental redesign that I'm not prepared to entertain at this time. But thank you for that suggestion as well.

The good news is, I found a solution!!!! Or a very, very easy workaround at any rate: remember that I mentioned that the query contained three linked tables with one inner join and one outer? I removed the table with the outer join from the query and now it's FINE. I tried it first in the blank test database and then in the actual development database.

My hypothesis is that changing the recordsource of an open form through VBA to a query with linked tables that are joined by at least one outer join will cause a fatal error for anyone. (Fatal in the sense that no forms can close, no queries can be executed and no data can be written through that new recordsource.) I would love someone else to test this. I hate to think this bug is unique to my table structure.
0
 
evan stoneAuthor Commented:
I accepted my own comment as the solution because it's the least involved workaround for this bug. (And I'm steadfast in calling this a "bug" because it's replicable and recordsource swapping is not an action that should fail merely by including an outer join in your query.)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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