Solved

VBA code doesn't run, no errors

Posted on 2008-06-13
7
317 Views
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?  
0
Comment
Question by:Coghan
  • 4
  • 3
7 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 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:
       >>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
 
LVL 1

Author Comment

by:Coghan
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.  





0
 
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?

mx

0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 1

Author Comment

by:Coghan
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.
0
 
LVL 75
ID: 21782767
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
 
LVL 1

Author Comment

by:Coghan
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.
0
 
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.

mx
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now