Solved

VBA code doesn't run, no errors

Posted on 2008-06-13
7
328 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

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!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

724 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