If Then Fails Comparing String Variable With Text (Access 03 to 2010) 2

[Du2good]

I've assembled many Access 2003 db's on Windows XP. They all perform a multitude of If Thens that are used to compare string variables such as strRptNm with "ChargeOffs".
If strRptNm = "ChargeOffs" Then
End if

Converting the db to 2010 is causing the If Then to fail. I can rewrite the code, and use strComp but it seems that this is an odd error. Odd in that not all If Thens fail, for example, using If Then to compare MyDate with Date() works correctly.

Is there something with 2010 that is so different from 2003 that I need to know about?
Compiles without error, and references are OK, I sure dont want to start from scratch on these, nor do I want to pull them apart piece by piece and rebuild them. I'm hoping for a quick fix. Anyone else have a similair issue?

Thank you

 

[MajP]

By fail do you mean error of just not evaluate to true. If the latter one issue could be in the conversion it changed your Option Compare.

http://support.microsoft.com/kb/98227

So for example it is possible then that "Chargeoffs" <> "ChargeOffs

 

[Du2good]

I thought I'd sent off a reply to this post already. But here it is now with some additional information/questions. I intended to provide the actual error message in the first post... by fail I mean I get an error message of "Invalid procedure call or argument".

I've come to suspect that lack of training and bad habits are responsible for the problems.

I never liked to Dim variables, so when I discovered Global variables, I put them to use.....widely across everything.
I was adding a sub to a well running 2003 db, using Access 2003, that used Global variables, and then after the updates I got the error "Invalid procedure call or argument" on a simple If Then. After I removed the new global variables the error went away and the code ran.

So I'm asking for some instruction on why when how to use Globals, and what the drawbacks are, what to avoid etc. I've done searched and it seems that a risk is to having the variable be changed elsewhere unexpectedly. I havent had that problem, yet, but it seems like I should change my habits now. Perhaps using option explicit with the sub I was working on, along with the Global variables created a problem in Access causing the error "Invalid procedure call or argument" error, even though it compiled.

 

[Andrzejek]

IMHO -
You should always use [tt]Option Explicit[/tt] in your code. In your VBA editor go to Tools - Options... - Editor tab and check 'Require Variable Declaration' check box. That will put Option Explicit in your code for you for any new Form, Module, etc.

For your variables - use the smallest scope of the variable possible. Do avoid Global variables at all cost, use them only if you must. There is 99% chance you can use something else rather than Global variable in most situations.

Establish some naming standards and stick to it. So, for example, all Strings start with strXYZ, all Integers start with intXYZ, etc. The same goes for naming your controls: cboXYZ for combo boxes, txtABC for text boxes, etc.


Have fun.

---- Andy

 

[dhookom]

Great post/advice from Andy. I agree 100%.

If you really think you need a global memory variable, consider prefixing the name like:
[tt][blue]
gintXYZ
gstrABC
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

There are actually two things to limit
Scope (you want the narrowest)
Lifetime (you want the shortest)

Here is a good article

http://www.vb6.us/tutorials/variable-scope

Scope and lifetime are not synonomous but often go hand in hand.
Example at the module level
Private var1
Public var2
Both variables have the same lifetime, but are different in scope.

As said global variables are a last resort when it is two complicated to do it another way.

 

[Du2good]

Good info, scope was never considered before. Ha, years of stuff to clean up now that I know better.

Option Explicit I knew about but was always fortunate?? to get by without using 100% of the time.

This was the biggest eye opener, I knew Public and Private existed but never took the time to determine why, thanks MajP! Simple to understand with few words.
Example at the module level
Private var1
Public var2
Both variables have the same lifetime, but are different in scope.

Now I see that I can declare a variable in a module and not have to declare the variable in each sub.....which is what I thought I should have been doing all along and is what led me to make them global in first place.
I'm still digesting the link, perhaps I'll have more questions later.
Thank you.

 

[Andrzejek]

Among other good things about Option Explicit, try this:

Option Explicit
Dim strThisIsMyVeryLongVariableName As String

Private Sub MyStuff
    MsgBox [blue]strt[/blue]
End Sub

and at the end of the blue part of this code just hit Cntr-Space and see what happens. No more typing, and you cannot enter wrong variable name.

Have fun.

---- Andy

 

[MajP]

Now I see that I can declare a variable in a module and not have to declare the variable in each sub.....which is what I thought I should have been doing all along and is what led me to make them global in first place

I think you interpreted what we are saying wrong. I rarely use variables declared at the module level. Again, I want to keep them alive only as long as needed.
example

private someVariable as somedatatype

public sub subOne
   set someVariable = ...
  ....
  some code
  call subTwo
end sub

Public sub SubTwo
  someVariable = ....
  some code
end sub

after calling sub one it calls sub two and both work with the module level variable. And now that thing floats around. You can still reference it at anytime. Unlikely that is what you want. You are likely done with it. That is bad code unless your intention is to hold that value for the life of the application. Better to pass it to the next procedure. Once procedure two executes the the variable is destroyed.

public sub subOne
  dim someVariable as somedatatype
  set someVariable = ...
  ....
  some code
  call subTwo (someVariable)
end sub

Public sub SubTwo(SV as someDatatype)
  sv  ....
  some code
end sub

 

[Andrzejek]

With MajP's example I would add:
Make the Subs Private (not Public) if they are not called from outside the module. Same rule apply as to variables: make the scope of Subs / Functions as small as needed.

Have fun.

---- Andy

 

[MajP]

yes procedures and properties too.

Here is another good (but rarer) example of the difference in scope and lifetime(persistence)

Public Sub SubOne() 
  Static intcount As integer 
  intCount = intCount + 1
End Function

intCount has the scope of a procedure level variable yet its lifetime is that of a module level variable.