References Problem???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Suddenly a major form in my application gives me a type mismatch error on the
following line of code:

MsgBox(Format(Now(), "Short Date"))

There are no variables named Now in the code module, no references appear to
say "Missing", the project compiles fine. This is an mdb file, not
excessivly large - about 4.2 MB. I've tried to compact repair to no avail.

I can run that line of code in another form. But when I create a blank form
and copy all the objects and code from the form giving me the error I still
get the type mismatch.

What is going on? I understand references go bad, but in a particular form?
I've been using Access for nearly 10 year, have NEVER seen anything like
this. It's almost enough to make me lobby my bosses for a switch to .NET
(did I say that????).

HELPPPPPPP!!!
 
Firstly Ed, make sure you have deselected the Name AutoCorrect boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

Then compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair

Next, close Access. Make a backup copy of the file. Decompile the database
by entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

Open Access (holding down the Shift key if you have any startup code), and
compact again.

If the problem still persists, open the code window and deselect all the
references except the 2 requires ones (Access and VBA.)
Close the database.
Unregister and re-register the libraries.
Then open the application, and select the references again.

To unregister, use this kind of thing:
regsvr32 -u "c:\program files\common files\microsoft
shared\dao\dao360.dll"

More info in:
Solving Problems with Library References
at:
http://allenbrowne.com/ser-38.html
 
Thanks for the quick reply Alan. Here's what I've done:

Create a blank database, imported everything into it from the original.
Took off the auto correct option.
Compacted.
Compiled.
Decompiled.
Compacted.

Still can't get the Format function to be recognized in the form in
question...

After I took out the 3 references (of 5) that I could remove, then re
selected them. Actually - I didn't reselect OLE Automation - I don' think I
need that, and originally ADO 2.1 was selected - I reselected the most recent
I have, 2.8.

And still the problem exists.

Not that I'm that deep into the inner working of things, but it would seem
that the reference that control the Format funtion would be VBA -
unregistered and re-resgistered it. No Dice.

What's very odd is that the Format funtion works in other forms as it's
supposed to. So I'm not that confident that this is a references issue, and
afer reregistering the VBA and DAO 3.6 libraries with no effect I'm at a loss.

While my app isn't dead int he water I am stuck trying to put a new feature
in, and I'm very concerned about other simple things stopping. Format is
pretty basic stuff.

Going back even further, it seems that the issue may predate "my app"...I
actually took the parts of a previous app and re-designed them, but I kep the
core of the old application. Looking at the old app, it seems that the main
form in it doesn't allow the format funtion. So now I'm thinking I have to
rebuild the form piece by piece, which will be a pain. But not intrinsically
hard...just tedious.

And at this point I'd really like to know WHY this is happening...the
origianl app was written in Access 2000, I'm using A2K3...
 
You are correct that the VBA library supplies the Format() function, but if
*any* library is broken, the code will probably fail.

In code, you might be able to workaround the problem by explicitly
specifying the library you intend, e.g.:
Debug.Print VBA.Format(VBA.Now(), "Short Date")
If this disambiguation works, it does sound like a naming clash or a
reference problem.

ADO 2.1 is normal. The library normally installs at:
C:\Program Files\Common Files\System\ADO\msado21.tlb

ADOX 2.8 is the current version of the:
Microsoft ADO Ext. 2.8 for DDL and Security
which is typically:
C:\Program Files\Common Files\System\ado\msadox.dll

There is a good chance you don't need the OLE Automation library or the Web
Components library.
 
I worked around by just using CStr(Date) in my in-line SQL - I try to be
consistent in my coding and that type of tweaking makes me a little queasy...
:-)

The disambiguation works...but I'm at a loss why there is a naming problem.
There are no variables declared for the entire module, and if it were
something in my major code module for the entire app it would stand to reason
that just using Format by itself should work in other forms...the word
"Format" doesn't even appear in the form.

I did try to unregister and reregister all the libraries, including the
Access reference "c:\Program Files\Microsoft
Office\OFFICE11\MSACC.OLB"...which gave me a nasty-gram but appeared not to
have any ill-effect. No difference...odd.

Anyway, thanks for the help. Knowing about the using the "vba." will be
helpful if I run into this again, as I am working on consolidating about 60
reporting databases into one application. I've got to assume that this issue
lurks elsewhere.

Case closed. I guess...
 
Back
Top