Setting Variables

Hi,

I have a form module that runs a lot of SQL code in several different subs.

I often call ALL my string SQL variables strSQL, so my subs look like this:

Sub A()
Dim strSQL as String
strSQL = “abc”
…run sql code
End Sub

Sub B()
Dim StrSQL as String
strSQL = “xyz”
…run sql code
End Sub

Instead of using Dim in every Sub, however; I sometimes do this:

Option Compare Database
Option Explicit
Private strSQL As String
----------------------------
Sub A()
strSQL = “abc”
…run sql code
End Sub

Sub B()
strSQL = “xyz”
…run sql code
End Sub

I wonder, however, if I should “reset/clear” the variables myself at the end of every Sub, like this:

Sub A()
strSQL = “abc”
…run sql code
strSQL = “”
End Sub

Sub B()
strSQL = “xyz”
…run sql code
strSQL = “”
End Sub

Any thougts? Is that a good idea?
Thanks,
alex

My thoughts are that there is no good reason to declare strSQL at the module level unless
you have more than one procedure in the module that needs to know what it's value is at
any given time. Even then I would declare it at procedure level and pass it to any other
procedure that needs to know it's value.

It's no more work to declare strSQL in each procedure than it is to make certain that it is
cleared in each procedure.

The only reason I can think of to declare strSQL at a module level is if you need to expose
it to code outside the module as a property.

Thanks ace!

Let me make sure that I understand you...

You would Dim (in this example) strSQL in every procedure, instead of at the module level.

alex

I would, yes.