Access VBA issue
Greetings,
I am running MS Office 2010 version 14.0.7237.5000 SP2 (32 bit) running Microsoft Access version 14.0.7230.5000. The OS is Windows 10 PRO 1909, Build 18363.693
A VBA test function is shown below that simply opens and closes a recordset, displaying messages in sequence and returning a Boolean True.
In the example below the recordset 4001Trans is a local table in the current database. Running the function results in the expected display of both message boxes and the function returns "True".
If I change "4001Trans" to "4001Trans1", which is not a Query or a Table referenced in the current database, the execution process exits the function as soon as it encounters a reference to the "TestSource" recordset. Thus only the "starting" pop-up message is triggered and the function exits without returning any value.
I also discovered that putting a breakpoint anywhere in the code no longer suspends the execution of the code when the breakpoint is reached. Execution simply continues as noted above. The VBA Stop command does not work either, it is simply ignored.
This appears to have something to do with the recent updates, as this has not been an issue until recently. My feeling is that the management of the real time execution process has been tweaked, and faults and/or stops are not being handled properly. Has anyone else experienced these issues? Any information would be useful.
Thank you,
Richard
I am running MS Office 2010 version 14.0.7237.5000 SP2 (32 bit) running Microsoft Access version 14.0.7230.5000. The OS is Windows 10 PRO 1909, Build 18363.693
A VBA test function is shown below that simply opens and closes a recordset, displaying messages in sequence and returning a Boolean True.
In the example below the recordset 4001Trans is a local table in the current database. Running the function results in the expected display of both message boxes and the function returns "True".
If I change "4001Trans" to "4001Trans1", which is not a Query or a Table referenced in the current database, the execution process exits the function as soon as it encounters a reference to the "TestSource" recordset. Thus only the "starting" pop-up message is triggered and the function exits without returning any value.
I also discovered that putting a breakpoint anywhere in the code no longer suspends the execution of the code when the breakpoint is reached. Execution simply continues as noted above. The VBA Stop command does not work either, it is simply ignored.
Public Function TestOpenRS() As Boolean
Dim TestSource As Recordset
DoCmd.SetWarnings True
On Error GoTo 0
MsgBox "starting"
Set TestSource = CurrentDb.OpenRecordset("4001Trans")
TestSource.Close
MsgBox "ending"
TestOpenRS = True
End FunctionThis appears to have something to do with the recent updates, as this has not been an issue until recently. My feeling is that the management of the real time execution process has been tweaked, and faults and/or stops are not being handled properly. Has anyone else experienced these issues? Any information would be useful.
Thank you,
Richard
Anders Ebro (Microsoft MVP)
Sounds like you might have disabled some of the startup options. If you reopen the database while holding down SHIFT (until the database is fully opened, do you then get the behavior you are expecting?
This is due to Error Handling
Put a proper Error Handler
Like
On Error GoTo 0 Put a proper Error Handler
Like
On Error Goto TestOpenRs_Err
Dim blnCondition as Boolean
blnCondition =False
.... Your Code...
..If Everything is OK
blnCondition =True
TestOpenRs_Exit:
TestOpenRS =blnCondition
Exit Function
TestOpenRs_Err:
'Here you can put what ever handling you want
Resume TestOpenRs_Exit
End Function
Sorry, John that is not correct. That line effectively disables any previous error handler and returns to normal error handling.
Probably you are right @Anders...i hardly seen that kind of handler an it looked like a good candidate.
According to the code you show , it is doing exactly what it should be doing, you are disabling any error handling so when it cannot open that recordset it stops.
As for the breakpoints not working, what is your ALLOW ACCESS SPECIAL KEYS property set to?
As for the breakpoints not working, what is your ALLOW ACCESS SPECIAL KEYS property set to?
If you do not have any error handler set, or have "On Error GoTo 0" (same thing as no active error handler), then Access code execution immediately exits the function the moment an error is encountered, so your function is behaving exactly as you have it coded.
The way error handling works, if you have a called procedure that doesn't have an error handler, such as your test function, an error will cause code execution to "jump back out" to the calling procedure and try to use its error handler (if it has one). If it doesn't, then code execution keeps "jumping back out" to the calling procedures in the stack until an active error handler is found, or the top-most procedure that started it all is reached.
Error handling is a major part of proper application development and behavior. Without it, all kinds of screwy, unintended things can happen... as you have discovered....
The way error handling works, if you have a called procedure that doesn't have an error handler, such as your test function, an error will cause code execution to "jump back out" to the calling procedure and try to use its error handler (if it has one). If it doesn't, then code execution keeps "jumping back out" to the calling procedures in the stack until an active error handler is found, or the top-most procedure that started it all is reached.
Error handling is a major part of proper application development and behavior. Without it, all kinds of screwy, unintended things can happen... as you have discovered....
ASKER
Thank you all for your comments.
My original reason for posting was to see if this particular problem had been seen by anyone else. It appears that it is a unique (or a very limited) problem and will require more testing to determine where the issue lies.
I may not be making myself clear in respect to the environment I am working in. I am using MS Access and the function is in a module of an Access database. The function is public and the table (4001Trans) is a table in the same database. I have opened the Microsoft Visual Basic for Applications editing environment by double clicking the module that contains the function.
In the immediate window I type "? TestOpenRS()" and hit enter to execute the function. As I have noted previously the function fails to stop on the "Stop" command and any breakpoints set in the code editing window are ignored. If I use a table name that cannot be resolved as a table or query then the function terminates prior to the last message "ending". No error messages are displayed.
Microsoft VBA Help (search for "on error goto") notes "On Error GoTo 0 Disables any enabled error handler in the current procedure." In my humble opinion this would mean that the default is now the VBA interpreter error handler. The interpreter should display a VBA error message before attempting to exit the failed code. The line of code in the Function that caused the run-time error would be highlighted in the code editing window. This is what has always happened in the past and I believe that this is the default error handling behavior of the VBA interpreter.
In response to Arana (G.P.)'s comment: how do you check, and/or set, the ALLOW ACCESS SPECIAL KEYS property? I have not made any changes to any system DAO object properties.
I will expand my debugging to include other run-time errors and the use of an error handler to see how the interpreter responds. I will continue to post my findings.
Thanks again everyone,
Richard
My original reason for posting was to see if this particular problem had been seen by anyone else. It appears that it is a unique (or a very limited) problem and will require more testing to determine where the issue lies.
I may not be making myself clear in respect to the environment I am working in. I am using MS Access and the function is in a module of an Access database. The function is public and the table (4001Trans) is a table in the same database. I have opened the Microsoft Visual Basic for Applications editing environment by double clicking the module that contains the function.
In the immediate window I type "? TestOpenRS()" and hit enter to execute the function. As I have noted previously the function fails to stop on the "Stop" command and any breakpoints set in the code editing window are ignored. If I use a table name that cannot be resolved as a table or query then the function terminates prior to the last message "ending". No error messages are displayed.
Microsoft VBA Help (search for "on error goto") notes "On Error GoTo 0 Disables any enabled error handler in the current procedure." In my humble opinion this would mean that the default is now the VBA interpreter error handler. The interpreter should display a VBA error message before attempting to exit the failed code. The line of code in the Function that caused the run-time error would be highlighted in the code editing window. This is what has always happened in the past and I believe that this is the default error handling behavior of the VBA interpreter.
In response to Arana (G.P.)'s comment: how do you check, and/or set, the ALLOW ACCESS SPECIAL KEYS property? I have not made any changes to any system DAO object properties.
I will expand my debugging to include other run-time errors and the use of an error handler to see how the interpreter responds. I will continue to post my findings.
Thanks again everyone,
Richard
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well this is a strange case...
i took your code and i could not find a way of not making it not to stop on the error.
Probably the best idea is to make a copy of your database...strip everything but this function ..try it and if it behaves the same share it here for all of us to take a look.
And last but not least...your are trying 4001Trans...and 4001Trans1...any chance there is actually a 4001Trans1 ?...maybe hidden...what about trying..eg."Richard2020"
i took your code and i could not find a way of not making it not to stop on the error.
Probably the best idea is to make a copy of your database...strip everything but this function ..try it and if it behaves the same share it here for all of us to take a look.
And last but not least...your are trying 4001Trans...and 4001Trans1...any chance there is actually a 4001Trans1 ?...maybe hidden...what about trying..eg."Richard2020"
ASKER
Thank you Arana (G.P.),
I did find the property you were concerned about. The ALLOW ACCESS SPECIAL KEYS property was turned off (unchecked). I did not turn this property off, and it must have always been on in the past. Is it possible that it now defaults to off? I generally use error handlers in my code once I have completed initial debugging. In this case I was writing a simple five line function to do a small task and did not want to deal with the additional error handling code for the initial trial run. In debugging mode the interpreter will indicate the line of code that generated the error and then exit. It is quicker to debug small functions without the addition of error handlers. As this had never happened before and I was unaware that the interpreter's error handling could be turn off.
Lesson learned, Thank you,
Richard
I did find the property you were concerned about. The ALLOW ACCESS SPECIAL KEYS property was turned off (unchecked). I did not turn this property off, and it must have always been on in the past. Is it possible that it now defaults to off? I generally use error handlers in my code once I have completed initial debugging. In this case I was writing a simple five line function to do a small task and did not want to deal with the additional error handling code for the initial trial run. In debugging mode the interpreter will indicate the line of code that generated the error and then exit. It is quicker to debug small functions without the addition of error handlers. As this had never happened before and I was unaware that the interpreter's error handling could be turn off.
Lesson learned, Thank you,
Richard
I think it is not the interpreters error handler that is turned off per se, more like it is the special F11 keys for development environment / macros etc, and this is more like a side effect. Since it would send the end user to the development GUI , and of course we don't want that.