Replace() not working

  • Thread starter Thread starter Robin
  • Start date Start date
R

Robin

I've seen this before somewhere, but I do not recall how
to resolve it.

Calling the Replace function generates an error 5.
Everything else (at least as far as I can tell) is working
fine. (Access XP on Win 98SE)

On another PC, I'm having a huge problem with VBA not
working at all (Access XP/ Windows XP) - even the Access
Wizards will not function (command buttons do not work,
etc.) I've tried uninstalling and reinstalling, both
Access by itself and all of Office.

I posted this question on another group but haven't
received any responses. Any ideas?

Robin
 
Assuming that this is not a References error (which it usually isn't), the
problem with Replace is a bug that is caused by ACCESS improperly
interpreting the sixth argument (which you likely aren't specifying, as it's
optional) as a language setting and not a comparison mode. The way to avoid
this error is to always specify all six arguments of the Replace function,
even the optional ones:

MyResult = Replace("SourceString", "FindString", "ReplaceString", 1, -1,
vbTextCompare)


Regarding your issue that VBA won't run on the one PC, what happens when you
click a button? Can you open the VBE and put a breakpoint on the first line
of code for a command button and then click the button and have VBE break
for you?
 
-----Original Message-----
Assuming that this is not a References error (which it usually isn't), the
problem with Replace is a bug that is caused by ACCESS improperly
interpreting the sixth argument (which you likely aren't specifying, as it's
optional) as a language setting and not a comparison mode. The way to avoid
this error is to always specify all six arguments of the Replace function,
even the optional ones:

MyResult = Replace
Click to expand...
("SourceString", "FindString", "ReplaceString", 1, -1,
vbTextCompare)
Click to expand...

I opened the database this morning to change this, and the
error went away before I could even impliment your fix.

I really don't like the inconsistency in the error's
repeatability. Can I assume it would be good practice to
always specify all six agruments, just to ensure this
doesn't happen?


Regarding your issue that VBA won't run on the one PC, what happens when you
click a button? Can you open the VBE and put a breakpoint on the first line
of code for a command button and then click the button and have VBE break
for you?
Click to expand...

When I tried to set the breakpoint in the VBE, it threw up
a simple "Error Loading DLL" message with OK and Help
buttons. The DLL in question isn't specified. Help
didn't.

To get far enough to fulfill your request, I had to start
with a blank database, create an unbound form, put a
command button on it (and then close the Wizard with Alt-
F4 because I forgot to shut the wizards off before I
selected the button tool) and then edit an event by hand.


Before all of that, I was having trouble even getting to
the VBE. Access (not VB) would puke up a fatal error when
I was trying to open an existing mdb.

The Error Signature looked like this:
AppName: msaccess.exe AppVer: 10.0.2627.1
ModName: oleaut32.dll ModVer: 3.50.5014.0 OffSet: 000016f3
 
The error "going away" after rebooting your PC (and sometimes after just
closing and reopening ACCESS) is normal. Until I'd learned of the way to
avoid the problem, the intermittent error vexed me at the most inopportune
times.

So, yes, I absolutely recommend that you always use all arguments for the
Replace function whenever you use it. Seems to completely avoid the error
occurring.
 
Ken,

Sounds good. Thank you for the insight. Do you know of
any other Access functions that exhibit this intermittent
problem?

Also, any ideas on the DLL issue?

Rob
 
No, I don't recall other functions at this moment.

Sorry, I'd overlooked your additional reply re: the VBE error. Are you
saying that the error is occurring only in the "old" database and not in the
new one that you created? Or is it occurring in both?
 
Any database. Old, new, doesn't matter.

VB code will not execute, nor will the Access Wizards (the
wizards will open to their first screen, but the only
thing that happens when a button is clicked is the visual
representation of the button being pressed and released).

Rob
 
There are no references listed as Missing in VBE.

One thing that seems a little odd is that there are half a
dozen "Visual Basic For Applications" options listed.

Only one of them is checked. It points to C:\Program
Files\Common Files\Microsoft Shared\VBA\VBA6\VB-something
(I can't completely identify it because the screen does
not display the entire path, and there are a number of
VB*.dll files in that directory). Would one of these
other options be more appropriate?

Also of possible interest is that "acwzmain" was not
checked. I set the checkmark for it hoping that might
help out, but it had no discernable affect.

I also tried renaming the oleaut32.dll file to
oleaut32.xxx and running Office Setup's Repair. Again no
joy.

Short of reformatting and installing from scratch, I don't
know what else to try. Unfortunately this would be a bit
problematic as the owner of the PC lost the OS and system
utilities disks (it's a laptop).

Any other things I could try?

Robin
 
Sounds to me as if you need to uninstall OFFICE and do a complete reinstall.
Something definitely is messed up in the current setup.

Sorry that I don't have more suggestions for you.
 
I've obtained some additional things that you can check out -- not sure if
they'll get you the answer, but, we can hope!

First, you may need to try a few more steps with respect to seeing if any
references are missing. Try following these steps (from a post by Douglas
Steele, MVP):
This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html



Second, try these Knowledge Base articles:

Microsoft Knowledge Base Article - 833220
"Error in loading DLL" error message when you run a Microsoft Access wizard
in Access 2002
http://support.microsoft.com/default.aspx?scid=kb;en-us;833220


Microsoft Knowledge Base Article - 303769
ACC2000: Error Message When Running Access Wizards
http://support.microsoft.com/default.aspx?scid=kb;en-us;303769


Let us know if any of this helps!
 
One additional thing. After unchecking some references, you may need to
compact and repair the database, close the database, reopen it, and then
check the References list to see if any are missing.
 
Back
Top