Solved

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

Posted on 2014-02-12
21
1,217 Views
Last Modified: 2014-02-20
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
Comment
Question by:evan stone
  • 12
  • 8
21 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 39855585
Try to reproduce the issue in sample database in Access 2007.
If you can, then upload the sample database.
0
 
LVL 57
ID: 39855985
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
 
LVL 57
ID: 39855992
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
 
LVL 57
ID: 39855999
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
 

Author Comment

by:evan stone
ID: 39856071
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
 
LVL 57
ID: 39856115
<<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
 

Author Comment

by:evan stone
ID: 39856294
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
 

Author Comment

by:evan stone
ID: 39856315
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
 
LVL 57
ID: 39856430
<<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
 

Author Comment

by:evan stone
ID: 39856882
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:evan stone
ID: 39856977
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
 
LVL 57
ID: 39857107
<<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
 

Author Comment

by:evan stone
ID: 39857147
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
 
LVL 57
ID: 39857236
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
 

Author Comment

by:evan stone
ID: 39857645
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
 

Author Comment

by:evan stone
ID: 39857661
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
 

Author Comment

by:evan stone
ID: 39857758
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
 

Author Comment

by:evan stone
ID: 39857789
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
 
LVL 57
ID: 39858707
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
 

Accepted Solution

by:
evan stone earned 0 total points
ID: 39861568
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
 

Author Closing Comment

by:evan stone
ID: 39872900
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

759 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

22 Experts available now in Live!

Get 1:1 Help Now