Option Explicit - Bug? Feature? User Failure?

[Tokyo Alex]

Dear all,

I'm experiencing an issue that I think may be a bug.

I have a form with a command button, btnNewProcess. In the Code behind the
Form, I have the following routine:

Private Sub btnNewProcess_Click()
On Error GoTo Err_btnNewProcess_Click

DoCmd.SetWarnings False
Dim stDocName As String

stDocName = "qdmQuoteHeadersWeWant"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "qdmQuoteDataExtract"
DoCmd.OpenQuery stDocName, acNormal, acEdit

MsgBox "Extract Completed", vbInformation, "Success!"

Exit_btnNewProcess_Click:
DoCmd.SetWarnings True
Exit Sub

Err_btnNewProcess_Click:
DoCmd.SetWarnings True
MsgBox Err.Description
Resume Exit_btnNewProcess_Click

End Sub

When I originally wrote the code, I did NOT have Option Explicit in the
module. This worked fine. Absolutely no problems at all. However, when I
added Option Explicit (after being reminded how crucial it is) to the module,
the routine will no longer run. At all. Instead, I get the following error
message:

"The expression On Click you entered as the event property setting produced
the following error: Variable not defined."

This same error occurs for all other event procedures in the module (most of
which are very similar to the sample above) also. I have been through the
code and am sure I don't have any undeclared variables anywhere. If I
comment out the Option Explicit it goes back to working fine.

I'm using Access 2007 on an Access 2003 format .mdb file. OS is Windows XP
Pro.

Is this a bug? If so is there a work around? Or am I dong something
monumentally stupid somewhere?

Any comments or advice greatly appreciated.

Thanks,
Alex.

 

[Paolo]

Hi Tokyo Alex,
actually the option explicit requires every variables to be declared. If you
are absolutely sure that this is complied the only suggestion I can give you
is to try to compact and repair the database.
Another try could be to decompile the database with the /decompile option in
the command line. If you try this be sure to backup your database before. The
decompile option flush all the pcode so it will be rebuilt enterily during
the execution.

HTH Paolo

 

[Jim Evans]

Have you tried compiling the project to see if you get an error?

Jim

Dear all,

I'm experiencing an issue that I think may be a bug.

I have a form with a command button, btnNewProcess. In the Code behind the
Form, I have the following routine:

Private Sub btnNewProcess_Click()
On Error GoTo Err_btnNewProcess_Click

DoCmd.SetWarnings False
Dim stDocName As String

stDocName = "qdmQuoteHeadersWeWant"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "qdmQuoteDataExtract"
DoCmd.OpenQuery stDocName, acNormal, acEdit

MsgBox "Extract Completed", vbInformation, "Success!"

Exit_btnNewProcess_Click:
DoCmd.SetWarnings True
Exit Sub

Err_btnNewProcess_Click:
DoCmd.SetWarnings True
MsgBox Err.Description
Resume Exit_btnNewProcess_Click

End Sub

When I originally wrote the code, I did NOT have Option Explicit in the
module. This worked fine. Absolutely no problems at all. However, when I
added Option Explicit (after being reminded how crucial it is) to the
module,
the routine will no longer run. At all. Instead, I get the following error
message:

"The expression On Click you entered as the event property setting produced
the following error: Variable not defined."

This same error occurs for all other event procedures in the module (most of
which are very similar to the sample above) also. I have been through the
code and am sure I don't have any undeclared variables anywhere. If I
comment out the Option Explicit it goes back to working fine.

I'm using Access 2007 on an Access 2003 format .mdb file. OS is Windows XP
Pro.

Is this a bug? If so is there a work around? Or am I dong something
monumentally stupid somewhere?

Any comments or advice greatly appreciated.

Thanks,
Alex.

 

[Tokyo Alex]

Hi Paulo, Jim,

Thanks for your input on this.

After a good night's sleep, I went through the code again and found that I
did indeed have an undeclared variable. *facepalm*

It would be nice if the VBA editor could highlight the variable that caused
the error (or at least the sub or function containing it), rather than just
giving up and forcing you to go through all the code. Oh well.

Thanks once again,
Alex.

 

[Douglas J. Steele]

Compiling the application (available through the Debug menu in the VB
Editor) should highlight the variable. Are you saying this isn't the case
for you?

 

[Tokyo Alex]

Hi Douglas,

Thanks for your response. Just tested this, and compiling from the debug
menu does highlight the offending variable. Wish I had known that before
going through all the code by hand

When I read Jim's suggestion to compile the project I, mistakenly, assumed
he meant from MDB to MDE. I tried this, and it did generate an error, but
not a very helpful one.

I guess the lesson here is to have Option Explicit on from the word go.

Once again, thanks for the tip.

Regards,
Alex.

 

[David W. Fenton]

Just tested this, and compiling from the debug
menu does highlight the offending variable. Wish I had known that
before going through all the code by hand

Compiling should be a regular part of your coding in VBA. Indeed, I
compile after every two or three lines of code (or after a logical
structure, such as an If/Then/Else). This way you'll find it very
hard to produce non-compilable code (though it's still quite
possible if you use public variables, for instance).

Add the compile button to your VBE toolbar so it's easy.

 

[vanderghast]

Not necessary. Try:

-------------------------
Option Compare Database

Option Explicit

Public Sub Toto( )
ReDim anUndimmedOne(1 To 400)
End Sub
-------------------------

It compiles without error.


But that is probably among the very few cases not covered by Option Explicit
(no other come to memory right now, in fact).


Vanderghast, Access MVP

 

[Tom Wickerath]

Hi Alex,

I guess the lesson here is to have Option Explicit on from the word go.

Yep. Here is a "gem tip" on this topic:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

and a few more "gem tips", while we're at it:

http://www.access.qbuilt.com/html/gem_tips.html#AutoIndex
http://www.access.qbuilt.com/html/gem_tips.html#ToolbarBtns


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________