VBA code doesn't run, no errors

Posted on 2008-06-13
Medium Priority
Last Modified: 2008-06-17
I developed a tool for my company that I'm pretty proud of.  It gets rolled out to the whole company next month so that we can do our credit card reconciliation easily.  It consists of a front end that lives on each person's desktop and a backend behind the scenes in a common drive.  Each person has their own full copy of the front end, so there are no sharing issues.  However, I have a couple of users helping me beta test that have problems.  There are no error messages when the tool opens the form, and to the untrained eye, there appears to be no problems.  However certain command buttons and so forth that should be hidden On Load are actually showing.  That tells me right there something is off.  Also, regular queries run, but queries that are fully coded in VBA do not execute.  I can see this because some fields that should update do not.  Finally if they click any command buttons or try to close the form normally they get this message:  The Expression On Click you entered as the event property setting produced the following error: Object or class does not support the set of events.  The expression may not result in the name of a macro..... etc.  We all have the same version of ACCESS.   They can come back to a PC in my area, log into the network as themselves, pull a new copy of the tool and it works smooth as silk.  Also, I had them reload the tool to their own machine and it still didn't work.

I checked Tools > Macro > Security and it is set to Low.  I have called it into our help desk but of course they say it is my tool, which I have conclusively proven that it is not.  So I'm trying to see if I can figure out the problem on my own.   What else should I check?  
Question by:Coghan
  • 4
  • 3
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 21781552
Tool ?  You mean your Access MDB?

How is it not yours ?

Anyway ... for good measure - do this on your Master - to be *Sure* it's 'clean' ... then redistribute:

A **DeCompile** may help here ...

But first, if you have not already:
Check for any **Missing References via the VBA Editor>>Tools>>References ....

Then, follow this procedure:

0) **Backup your MDB BEFORE running this procedure**
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Close the mdb.
6) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

7) Close the mdb
8) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an **example** of the command line syntax  (be SURE to adjust your path and file name accordingly) before executing the decompile:

Run this from Start>>Run, enter the following command line - **all on one line** - it may appear like two lines here in the post:
Also, the double quotes are required.

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\YourMdbNameHERE.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:



Author Comment

ID: 21782205
Tool ?  You mean your Access MDB?...  Sorry, I call the front end database the Tool.  I should've been consistent and referred to it as the Frontend every time.   It is MDE, not MDB

How is it not yours ?  Ha ha.. just miscommunication.  Here is a better way to say it:  I have called it into our help desk but of course they say THE PROBLEM IS IN MY TOOL, which I have conclusively proven that it is not.

I did a decompile/recompile first.  I kinda knew this wouldn't help as it works on dozens of PCs, only a small number have problems doing exactly the same function on the same records.  However, that was done. I also created a new database, broke all the links between forms and the code (has module false) then recreated the whole thing and recompiled.  No change for the people having problems.  No change for the people not having problems.  Like I said, they can go to a different PC and it works, so I'm pretty certain it is somehow ACCESS is configured.. I just can't see any obvious things that are not the same.  

References is a good idea to explore thoroughly though..  I'm not sure how we could have different references, since Office is pushed out to all machines in the company all at the same time.  But it could be they got a bad copy somehow, or were monkeying around and clicked on stuff they shouldn't have.  I didn't see any bad references at a glance, but I probably need to list all on my copy, then just tick them off one at a time on the non-functioning copy.  

LVL 75
ID: 21782305
Of COURSE ... the IT Dept would disavow any knowledge, lol!

Does sound machine specific ... the just wanted to try the basic Decompile procedure.

Can you run the MDB master on one of the suspect machines - and see exactly where the error occurs?


The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


Author Comment

ID: 21782708
Good idea.  I'll save it to a flash drive and take it over there and see what happens.  I'll report back on Monday.
LVL 75
ID: 21782767

You may need to *temporarily* set 'Break on all errors' in the VBA module Option settings ... to be sure it breaks at the exact point.


Author Comment

ID: 21805136
Microsoft Common Dialog Control missing.  IT dept. was oh that is a special thing you installed for your project we can't support it... ahem.. well...   LOL... One has to be diplomatic.  But the end result is that it was a missing reference.  I was able to get my users to all send emails in with screenshots showing the file  (comdlg32.ocx) was installed on their PCs while the few who weren't able to go in showed the file missing, clearly demonstrating that we are not all configured alike.  Seemed like a lot of trouble to go through but it was finally convincing enough.  I do wish there didn't have to be this friction between support and development, but it seems to be almost a universal issue.
LVL 75
ID: 21805282
comdlg32.ocx  is a *standard* Windows component for years - it's not an optional component.  It contains the File Browse used everywhere, the Color picker and many other items.

Weird!  It's *not* a special thing.  IT = Clueless, lol.


Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question