Reference Variable in multiple Subs

[alex]

Reference Variable in multiple Subs

Hello,

I’m trying to accomplish the following:

Sub WriteQuery()
Dim strSQL as String
strSQL = _
“Select * from myTable”
End Sub

Sub RunQuery()
Docmd.RunSQL(strSQL) ‘won’t run because variable not defined in
immediate sub
End Sub

It’s a simplified example, but I’m trying to reference a variable
(that’s set in one sub) in another sub…can I do this in some manner?

Thanks,
alex

 

[John Spencer]

Try declaring strSQL at the very top of the module as Public or Private variable.

Option Compare Database
Option Explicit

Dim strSQL as String 'available in all VBA modules
OR
Private strSQL as string 'available in only the current module

Sub WriteQuery()
'Dim strSQL as String Don't declare the same string here
'you will end up with two strSQL - one local to this sub
'which will be used instead of the one declared at the module
'level
strSQL = _
"Select * from myTable"
End Sub

Sub RunQuery()
Docmd.RunSQL(strSQL) ‘won’t run because variable not defined in
immediate sub
End Sub

An alternative would be to change Sub WriteQuery to a function and return the
string when it is called.

Function WriteQuery() as String
Dim strSQL as String
strSQL = "SELECT * FROM MyTable"
WriteQuery = strSQL
End Function

Sub RunQuery()
Docmd.RunSQL(WriteQUery())
End Sub


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Daryl S]

Alex -

The normal way to do this would be to pass the sql sttring into your
RunQuery subroutine. You can use global variables, but passing the string
parameter in is usually a better way to go.

If the subroutines you are using are both tied to one form, then you can
also put a textbox on the form (.visible = FALSE so users don't see it).
Then you can 'store' your SQL string there in the WriteQuery subroutine, and
can read it in the RunQuery subroutine.

 

[alex]

Alex -

The normal way to do this would be to pass the sql sttring into your
RunQuery subroutine.   You can use global variables, but passing the string
parameter in is usually a better way to go.  

If the subroutines you are using are both tied to one form, then you can
also put a textbox on the form (.visible = FALSE so users don't see it)..  
Then you can 'store' your SQL string there in the WriteQuery subroutine, and
can read it in the RunQuery subroutine.

--
Daryl S









- Show quoted text -

John/Daryl thanks for the comments...they help a lot.

John, I went with the function (which worked), but I'm testing the
first alternative (which seems a little too easy) and it's not
working...I must be mistaken as to how it works. I've attached my
simple example below:

Option Compare Database
Option Explicit

Private strSQL As String

Sub DefineString()
strSQL = "test"
End Sub

Sub RunString()
Debug.Print strSQL
End Sub

 

[John Spencer]

Did you execute the sub DefineString before you execute the RunString sub? If
you don't execute DefineString at least once before you execute RunString,
then strSQL is a zero-length string "".

I just tested this in a VBA module and this works for me.

Option Compare Database
Option Explicit

Private strSQL As String

Sub makeString()
strSQL = "Text"
End Sub

Sub PrintString()
Debug.Print strSQL
End Sub


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[alex]

Did you execute the sub DefineString before you execute the RunString sub? If
you don't execute DefineString at least once before you execute RunString,
then strSQL is a zero-length string "".

I just tested this in a VBA module and this works for me.

Option Compare Database
Option Explicit

Private strSQL As String

Sub makeString()
   strSQL = "Text"
End Sub

Sub PrintString()
    Debug.Print strSQL
End Sub

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County










- Show quoted text -

Thanks John. I'll look to see what I may have done wrong.
alex