Command Button Fails

[Privateer]

After compacting and repairing the db, (W10-Office 365), closing and opening it, seven command buttons fail their respective on click event. Two buttons created recently, and on the same form, work as expected. So, I open VBA and open the class module for that form ("frmAdmin"), click on any of the seven that failed, and they work. Close the class object, but leave VBA open, and the buttons fail again, open the class object and the buttons work. The error message is Error 91, Object variable or With block variable is not set, which isn't much help as six to twelve procedures run behind any one of these seven buttons with no errors. Additionally, the Event Procedure is in the On Click field.

I have deleted one of the seven and created it from scratch, thinking the newly created buttons don't have this problem. I changed the name of the button and created a brand new on click event into which I copied the code. I confirmed the link to the on click event in the procedure and everything worked fine. However, after C&R, close, open, buttons fail. The form will open, but that class object must be open for the code to work.

I put a stop execution on a procedure in the form load event and closed the module, then the form. Opening the form caused to module to open on the stopped line, so the link between the form and the module is working, and this allowed the command buttons to work. I tried this because the form and the module act as if they are not joined to each other properly.

I did some research and decided to import the form into another Access database, created on the same laptop three weeks ago. I ran into some reference problems and added in Excel 16 and Office 16 objects to get the module to compile, which it evidentially did. Buttons still would not work unless the class module was opened. Very frustrating, never seen this in 25 years.

I am trying to deliver this front end to co-workers, and I can't have this loss of function simply because the class module, and VBA are closed. Any help on this abnormal behavior would be greatly appreciated.

 

[MarkK]

Can you post a database that demonstrates the problem?

 

[Privateer]

I understand the difficulty of trying to solve this problem without seeing it and looking around. However, I will have to ask those at work on Monday if the form can be distributed even if it is the only object in the otherwise blank database. Sorry, the security here is really tight.

 

[The_Doc_Man]

While it might go nowhere in solving your problem, I have an idea that you could check fairly easily. We have seen a recent set of oddball behavior after a recent Office update. Open Access, select the File tab from the ribbon, open Help, and look for the "About" description that will include the build number of Access. In particular, 2204 has been a bit troublesome.

 

[Privateer]

I think I have found the problem. I went looking for code to identify the name of the module and the procedure so I could identify the location of the error. Unable to find a way to identify the procedure code, I decided to manage that myself. However, the module code I found and used, "Application.VBA.ActiveCodePane.CodeModule" seems to be the cause of the problem. The code worked fine during development as VBA and various class modules were all open. Yet, after compact and repair and closing and opening the database, neither the VBA editor, nor any modules were open, so there was nothing "active". I had a co-worker take a look and he suggested we comment out that line. So, lesson learned, I used code I didn't fully understand and grew to trust it during development. I removed all instances of this code and suddenly all the command buttons came to life. Thanks to all who read the post and to those who made suggestions.

 

[OuterApply]

You haven't even done the most basic of troubleshooting steps yet. Click "Debug" and find out which line it stops on, then start debugging using normal Immediate Window techniques, etc., google that if needed. There is no need to do all the other stuff when you have not even debugged your code yet.

 

[Pat Hartman]

@Privateer I'm going to take a stab at why that code is only working in development. The .ActiveCodePane part may only be valid when the VBA window is open which it wouldn't be under normal circumstances. So the error 91 is saying that the Object Variable isn't available.

Try this - put the code back in one module. Click in the left margin to set the code to stop on that line. Compile, save,close VBA. Open the form and run the code module. It should stop on the line. Then step to the next line and it should work because VBA is open. let the code run to the end. Then go back and removed the stop mark. Compile and close VBA. Next time you run it, it will fail. That should be pretty definitive. Maybe @isladogs has an answer.

 

[Privateer]

@Pat Hartman Following your suggestion, I put the code back in the on click event of the command button, it's the first line, and put the stop on that line. Doing everything except C&R and the bouncing ball did stop on that line, even when VBA was closed at the start. However, when the C&R is run, the stops disappear, as usual, and the error returns. So, your theory is verified. I guess I assumed the active code pane was where the ball was, whether it was an open window/pane or not, but it really means open and active.
It's not a damaging line of code, put it in an on click event of a button and see what happens before and after the C&R. What caused me to post was the blue circle started spinning and then the "Access is Unresponsive" appeared in the title. I though the import was taking a long time, but ten minutes later I had no control, mouse was dead, and the only way out was to forcible close the application. Thanks again for the help.

 

[Pat Hartman]

You're welcome.