Access closing unexpectedly after editing VBA

[dkmoreland]

I have never seen Access do this before today. I've been working on this database for months - it was working fine just a couple of days ago. But today, Access has started closing completely after I open the VBA editor, make a change and close the editor.

It's not a crash and there is no error message - I get the save prompts where appropriate and Access just closes.

Has anybody else see this kind of weirdness?

Thanks

 

[Minty]

Sounds like either a Access.exe / office problem or database corruption.

If you have an older backup you know was okay - make a copy and open that. If the problem isn't there then you may have corruption. If it is I would suggest trying a repair on the access install.

Corruption may be cured by a compact and repair, or if serious you may have to import the data into a new blank database. Worst case revert to a backup.

 

[isladogs]

If it only happens on one database, then it's almost certainly corruption.
If so, I very much doubt compacting will help.
I would recommend decompiling your database first as that will fix any corrupted code. See http://www.fmsinc.com/microsoftaccess/Performance/Decompile.asp
It's very fast to do.

If on all Access apps, then reinstall/repair Access

 

[The_Doc_Man]

OK, you get the SAVE prompts. If you choose to save your work, does it get saved before the crash or do you lose the changes? If it doesn't even SAVE, then probably it is caused by corruption. If it does save, it could still be corruption, but testing other Access apps would be advisable to rule out a problem with Access itself.

Basically, I'm with Minty and Colin on this one.

 

[dkmoreland]

I appreciate everyone's input - I was afraid the consensus would be something like this. What a colossal pain in the butt. :banghead:

I will try the options suggested.

Thanks so much.

 

[arnelgp]

don't mixed edit your vba on x86 and x64 mso.
also if the db is made from earlier version, don't edit it in higher version.
doing so will add extra sys tables to your db and most likely won't work on earlier version.

 

[isladogs]

You haven't stated which version(s) or 'bitnesses' of Access you are using.

Whilst arnelgp's comments are sensible advice in general, I don't think these are likely to be the cause of your problem.

a) 32/64-bit
If you are using API calls, make sure the conditional compilation is correct

b) Different versions
From A2007 onwards, only one set of changes has occurred in Access system tables & then only in a specific situation regarding pre-2010 cache settings. I've tested these & had no issues with opening the database in A2007.

If you wanted to open the db in 2003 or earlier, you'd need to convert to MDB and the extra ACCDB system tables are omitted automatically

However you could have reference issues in opening e.g. a 2010 version in 2007.
Using late binding avoids the need for version specific references such as Excel

Similarly certain code that works in one version won't in another version.
Particular issues occur for A2007 users

If the issue is with your startup form, Access will indeed crash.
See if removing the startup form helps solve the issue at least temporarily.

BUT if you haven't yet done so: BACKUP, COMPILE, DECOMPILE, RECOMPILE, COMPACT in that order

 

[dkmoreland]

This is so frustrating - I have decompiled and when I try to recompile, I get the error shown in the attached screenshot.

I tried creating a new database and importing everything but it did not help.

Everything still works but I can't compile and Access still shuts down after I edit the VBA.

:banghead::banghead::banghead:

I'm running Access 2016 - all of the machines that run this database are at either 2013 or 2016.

Any additional insight would be most appreciated.

 

[Insane_ai]

This may be off base but is it possible that VBA is choking on "GoTo" from GoToRow?


I realize these terms are not exactly the same and I usually get around reserved words like "Date" with variations like "MyDate"

I'm curious if you used a different name for the function, would the error go away?

similar issue but not exact:
https://stackoverflow.com/questions/44629351/vba-access-crash-with-no-error-when-calling-function

 

[isladogs]

Your problem is that the function is GoToJob and the next line wrongly says GoToRow

Exactly the same error on the GoToNCR sub following

Also you really should tick require variable declaration and retrospective se Option Explicit to all existing code modules

 

[dkmoreland]

this makes no sense - everything was working just fine and now when I try to recompile, it craps out on nearly every line of code. I also tried creating a new database and importing everything and all the colors on my forms changed. The Quick Style color I was using does not even show up in the new db.

And it did not fix the problem with the VBA editor. I cannot believe all this code has been riddled with compile errors all this time. Everything still works but I can't compile and I can't change the code without Access shutting down when I try to edit or debug.

Would reinstalling Access do any good?

 

[isladogs]

Reinstalling Access is probably not the solution - the errors are almost certainly just in your database

This is what I'd do:
1. BACKUP
2. Decompile - ask if unsure how to do that
3. Tick Require Variable Declaration in VBE Options if not already done
4. Add Option explicit to every existing code module if not already done
5. Debug...Compile and work through fixing EVERY error even if it takes some time. Normally it will mean defining a variable that you've forgotten to do
Continue till its fully compiled - the menu item will then become disabled.
6. Compact
7. BACKUP again & delete the previous backup

From now on always tick Require Variable Declaration when you create a new database

You've also just discovered one of the disadvantages of importing all objects to a new database.
Make sure you save any custom theme in future

 

[The_Doc_Man]

From an earlier post in this thread:

I tried creating a new database and importing everything but it did not help.

More recently:

everything was working just fine and now when I try to recompile, it craps out on nearly every line of code.

As a one-time exercise, I would go back and verify that you have the correct references for the things you wanted to use from the various run-time libraries. You should NOT have to review references more than once after doing that copy-over that you mentioned. But you might need to do it once. And of course that doesn't count if you add new functionality that requires a new library reference, but that case should be obvious.

 

[JHB]

Back to the error in post #8, do you expect to have a Function/Sub called GoToRow? Because the error message say it is missing.
If you open another database, does it also have the same problem as this one?
Have you tried open the database on another computer?

 

[James Dickinson]

I would make sure that every single procedure has an error handler that prompts you with a message if an error occurs. This will hopefully catch some of the random crashing. Also do a quick check for docmd.quit lines and make sure that there is a message that appears before the quit takes place.
compile and fix each error until you have none remaining. then compact repair. I know a lot of this has already been said but it would be my first port of call, good luck

P.S. I'm happy to help and if need be I can look at your code personally to try and get to the bottom of your issue.

 

[dkmoreland]

I think I am out of my league on this problem - I still can't get it to compile.
The GoToRow function that it is crashing on is a Public function in another module.


Plus now, one of my reports has started crashing with an 'expression is too complex to be evaluated" error. It's just a date range from a form, passed to a query. The weird thing is, it works fine if the dates are previous to 10/31/18. Once we get to November, it blows up. I checked the data - there are no wonky data entry errors for November that would cause this. The dates are simple short dates.

And I can't debug it because the debugger is acting so freaking weird - throwing in characters that I did not type, inserting spacing unnecessarily, etc. I can't even set print.debug break points - it keeps changing the syntax incorrectly.


If the query will evaluate 10/31/18, why won't it evaluate 11/01/18?

I also need some new swear words. I appreciate the responses to my post thus far - unfortunately, I am not making any progress. Any additional insight would be most welcome.

 

[Minty]

Any chance of trying to open the database on another machine ?

It would remove the possibility of a local windows/office/access issue.

Do you have a working backup someone else could try opening?

 

[dkmoreland]

Any chance of trying to open the database on another machine ?

It would remove the possibility of a local windows/office/access issue.

Do you have a working backup someone else could try opening?

This is a split database - I have opened the front end on several different machines and the problem is the same. I'm pretty sure it's not a local windows/access issue.

The most recent backups behave the same way, regardless of which machine is used.

I just had a thought - this did not start happening until I added a hidden idle timeout form triggered by an autoexec macro.

Could this have caused this problem?

 

[psyc0tic1]

I just had a thought - this did not start happening until I added a hidden idle timeout form triggered by an autoexec macro.

Could this have caused this problem?

Have you tried removing that?

Does it crash as you described if you open the database by holding down the SHIFT key and clicking the DB? That should bypass the AutoExec and any other code.

 

[Minty]

Active form timers really mess with the VBA editor. All sorts of strange things can happen.
Deactivate it and try again.