1

MS Access 2016 running on Windows 10.

I am debugging VBA changes to a MS Access application and am seeing some unexpected interactions between the VBA editor and running code. The steps are basically:

  1. Open the application, which opens startup form.
  2. The startup form_load instantiates an object used by other forms the user may subsequently open.
  3. Open the VBA editor
  4. Using the VBA editor, select a line in any code module and the instantiated objects are set to nothing.
  5. An error is thrown when the other forms using the object are opened.

So basically, the VBA editor action has set the objects to nothing. I have added instrumenting code to confirm this.

Has anyone seen the behavior? Does anyone have thoughts about what may be happening and causing this?

Thanks in advance...

Additional information: The code instantiating the object in the Form_Load method is:

Set musrInfo = New usrInfo

Where usrInfo is a class module containing user information.

Also, there is no problem with earlier versions of this - I have never experienced the described problem with any other MSA VBA application. The compiled version of this particular MSA file is a bit bigger than 20MB, with little in the way of data tables - only a few parameters, etc. - and more than 13MB in forms, reports, etc.

I hope this helps... Lindsay

And there's more... - I tried this .accdb file on another PC with Win7/MSA2010 and this behavior did not occur. - I then tried it in a different folder on the original PC and it did not occur.

Maybe these findings will allow a path forward, but I still wonder why this would ever happen - why would the folder choice make any difference?

Earthman55
  • 33
  • 5
  • Can you post the code in the startup form that instantiates the object? It may be discarded if you make certain changes in the VBA or if you stop the project's running code (particularly if you change code in the form itself which stops the form). – andrew Mar 23 '18 at 22:48
  • Code posted - nothing special. Further, the only thing I do with the VBA editor to cause this is select within the VBA code - I make no changes and the object variable is set to nothing. – Earthman55 Mar 26 '18 at 16:18

1 Answers1

1

This is usual behavior.

When making changes using the VBA editor, it may recompile the VB project behind your database. This can be the whole project, or parts of it, depending on the exact change.

Recompiles will clear any variables.

You can change this behaviour, by going to Tools -> Options, under the General tab. See the following screenshot.

enter image description here

However, even with Compile On Demand off, you will have to trigger a recompile for most changes, clearing any set variables.

For classes that need a single instance to be publicly available as long as the database is open, I recommend setting the VB_PredeclaredID to true. That will instantiate the object as soon as the database opens, or the code recompiles. See here how.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • I tried working with the Compile selections in the Options dialog that you suggested and the problem behavior doesn't seem to be happening - I unchecked the Background box, saved it, opened it, etc., then checked and saved it and the behavior doesn't seem to be happening. I will monitor this for a little while and see if it returns. If no problem in the next couple of days, I will close this. Thanks for your ideas... Lindsay. – Earthman55 Mar 26 '18 at 16:44
  • I worked with it for a while, debugging and testing, and the problem eventually returned. Any further ideas? – Earthman55 Mar 27 '18 at 14:29
  • Have you left compile on demand off, and does it still persist? – Erik A Mar 27 '18 at 14:38
  • I have tried it with compile on demand both on and off, and the problem occurs either way. In addition, I tried on different PCs and in a different folder on the same PC and the problem did not occur. See the edits to my original post. This all seems very odd. – Earthman55 Mar 29 '18 at 14:51
  • Just leave compile on demand off. Also check _Notify Before State Loss_. That should notify you when it's about to reset everything. – Erik A Mar 29 '18 at 14:55
  • So I found an interesting correlation: when I move the failing .accdb file to another folder and launch it, I get the yellow bar under the ribbon that says "SECURITY WARNING Some active content has been disabled..." If I then click on the Enable Content button it runs fine until I make a change that causes the "Notify Before State Loss" notification. I don't know much about how this security warning actually works - it would appear Windows or MSO keeps track of acceptable files or something like that - but it appears it is somehow interacting with the VBA editor. – Earthman55 Apr 02 '18 at 22:47