Runtime error3011, but not in Debug Mode

Question

I have project I am working on. I have an export form line of code to export a specific form to another database just created. This is one of four things being exported: 2 tables, 1 form and 1 Autoexec macro in that order. The first two complete without issue, either in runtime or in debug mode. However, the form is where the problem arises. In debug mode it exports without issue, during runtime it creates a 3011 error object not found.

I thought it was a timing issue, because before the export I change the record source of the form to one of the tables I am exporting and that completes without issue in both modes, so I moved the code to alter the form to the beginning of my procedure with the exports at almost the very end. I changed the code sequence to place about 2000 lines of code between the form edit and the export command, and it still experiences the problem, works in debug, not in runtime. I have since moved the code back to the original location. It is not a timing issue due to proximity.

Here is the applicable code: All variables are Strings. CACID is a 11 digit string holds alpha numeric code for a CAC Card.

'''VBA
str_TestName - CACID & "_Test" 'Assigns a string variable to for the table created earlier-no issues
DoCmd.openForm "frm_Test", acDesign,,,, acHidden  'Opens the form hidden to modify design
Forms!frm_Test.RecordSource = str_TestName 'Sets the record source to the table created above
DoCmd.close acform, "frm_Test", acSaveYes  'Closes and saves the form edited-no issues
... SQL code to create table str_TblName, then SQL code to insert a record  'works
docmd.TransferDatabase acExport, "Microsoft Access", str_DBLocalPath, acTable, str_TableName, strTableName  'works
DoCmd.TransferDatabase acExport, "Microsoft Access", str_DBLocalPath, acTable, str_tblName, str_Tblname  'works
DoCmd.transferDatabase acExport, "Microsoft Access", str_DBLocalPath, acTable, "frm_Test", frm_test"   'works in Debug not runtime
DoCmd.TransferDatabase acExport, "Microsoft Access", str_DBLocalPath, acMacro, "Autoexec_Local", "Autoexec"

To compound matters this system is on an offline system so posting code is me retyping the code for this forum.

It's really tough to troubleshoot because it compiles and works perfectly during debug mode, no errors raised at all, exports fine the entire procedure completes. Only during runtime the error is thrown and the procedure fails at that point.

Internet research suggested it was a timing issue because the system was busy. They suggested adding a DoEvents before the offending line of code. When I did this nothing worked, for the whole entire procedure and all the sub procedures it calls. I removed those DoEvents.

Other Internet research suggested it was a glitch and Compacting and Repair would correct the issue. It is not a compact and repair issue, it is compacted on closing.

I would appreciate any assistance provided. Thanking you in advance. Kevin

Further testing completed. I commented out the code that edits the record source for the form frm_Test, ran it in debug mode and it worked fine, (as expected). Ran it in runtime version and it failed in the same place, same error (not expected). I reasoned it was because I edited it before I exported it, but that is clearly not the case. Something else must be happening, but that remains the question.

Follow-up: Conducted more testing it produces no error with the VB window open or closed, as long as its not in my run time environment. Okay, my runtime environment has: Allow full menus disabled, Allow Default shortcut menus disabled, use Access special keys disabled, navigation pane hidden and locked, AllowByPassKey is disabled, Display Status bar disabled, enable layout view disabled, enabled design changes in tables in datasheet disabled

I inserted a MSgBox the line before the target line of code. Then a final msgBox appears to indicate task was completed. So I know when the line of code will fire and that the entire process completes in the runtime environment.

However, during testing the AllowByPassKey was not active at the time. Test One reenable "Use Access Special Keys" - Fails same location same error. Test Two with above line reenableed "Allow Default Shortcut Menus" - Fails same location same error. Test Three with the two lines above reenabled Display Status Bar - Fails same location same error. Test Four with the three lines above reenable "Allow Full Menus" - Fails same location same error. Test Five with the four line above reenabled "Display Navigation Pane" - Fails same location same error. Test Six with the five lines above reenabled design changes for tables in datasheet view - Fails same location same error.

However, I noticed that although I reenabled full menus and the navigation pane they did not appear. I used code to prevent those things from appearing as well. I really didn't want the navigation pane display I found four separate commands three would fire no matter what; commented them all out.

DoCmd.ShowToolbar "Ribbon", acToolbarNo
DoCmd.NavigateTo "NavigationCategoryObjectType"
DoCmd.RunCommand acCmdWindowHide

Test Seven with prior six lines, commented out acToolbarNo line - Fails same location same error. Test Eight with prior seven lines, comment out the two line to hide the Navigation Pane - Fails same location same error.

Okay I have essentially recreated the environment when I test the database outside of debug mode and the same problem occurs and at the same location.

So now I am really perplexed, it is not the runtime environment I use to run the program I am creating. So the question now is What is happening between the Runtime and the Debug modes? Why will it work in Debug mode, but not during runtime.

Test Nine with the last eight testing lines plus I'll open the VB Window in the background, it is open, but the password is not supplied to edit the code - Fails same location same error. Test Ten with the nine testing lines above plus I'll enter the password for the VB window - The process completed normally!

Okay, now how do I fix this? 10/13/2019 -Okay next round of testing. My theory is that either the process breaks there because the statement is bad, which it is not or there is a theoretical undocumented upper limit to the number of lines of code that can be run. To test this theory I am moving the line of code to just before the SetWarnings True statement. I am expecting one of two outcomes: the code continues to the original statement proving it is the statement somehow; OR it breaks on the next line of code, potentially pointing to a limit. I changed all the previous testing back to the original settings for the runtime environment. However I don't think it is the limit theory, because I have been adding lines of code above it like msgboxs and error statements for my error handler and it still fails in the same location.

Test 11 moving the offending line of code down. Wholly cow! it worked. Moving the line of code literally 6 lines away allowed it to complete properly. Literally amazing. I do not understand how this is working. It makes no sense, at all. Now I see why it makes no sense. I checked the output database and the frm_test form was not there, nor did it throw an error this time. The execution just skipped? over the line, it seems like.

Test 12 moving the offending line of code up to just after the record source update. Again three expected outcomes executes and skips like before, fails at this location, fails at another location. Okay fails at this new location, just after the record source change for it with the same error code 3011 Object not found.

Test 13 testing to see if changing the record source "locks" or "hides" the form in any way. Three expected outcomes: it fails at this location, it fails at another location or it completes the exports. Okay failed at this location with the same error code. So altering the form properties is not the issue.

Okay Test 14 I moved the line of code to the beginning of code execution right after the new external database creation is completed. The form properties were updated but code execution failed at that line, same error 3011 object not found.

Test 15 fixed everything moved the form edit and form export lines to the beginning after external database creation. Weird thing happened code failed to run entirely. Nothing happened. Forgot to compile code before exiting; there was an error I created by accident.

Test 15, Again. Okay same outcome, failed at the new location with the same error 3011 object not found.

I am running out of options to check. It is obviously the line of code, no matter where I place it, that is where it fails in runtime, but does not fail in debug mode. It only executes properly with the VB window open and unlocked. Opening the VB window and providing the password just to export a form to a new database seems insane on the face of it.

Alright, new tactic. Test 16 - I am going to create a new sub that all it does is the export to the external database. I will call that sub from within the existing code. Fails at the same location with the same error code.

Test 17 and this is the telling one that In think locates the root of the problem I attempted to import the form from the newly created database manually. I got an error message that the password was required to perform this action. AH ha! I need to provide the VBA password to allow the export of a form with the vba code on it.

This is the source of the problem the VBA has a password protecting the code and will not allow export of any code without supplying the password.

Answer 1

I Solved the problem, well not soled, just now fully understand why I could not do what I wanted to do. You cannot export a form contained in password protected VBA prpoject. So, the answer to my original question is you cannot. Find different solution. My understanding is that the program must read the code attached to the form, and cannot do that when there is a password protecting VBA.