Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

VBA code doesn't run, no errors

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?  
0
Coghan
Asked:
Coghan
  • 4
  • 3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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:
       >>Debug>>Compile
        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:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx
0
 
CoghanAuthor Commented:
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.  





0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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?

mx

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CoghanAuthor Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
cool.

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.

mx
0
 
CoghanAuthor Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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.

mx
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now