Reference Variable in multiple Subs

Reference Variable in multiple Subs

Hello,

I=92m trying to accomplish the following:

Sub WriteQuery()
Dim strSQL as String
strSQL =3D _
=93Select * from myTable=94
End Sub

Sub RunQuery()
Docmd.RunSQL(strSQL) =91won=92t run because variable not defined in
immediate sub
End Sub

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

Thanks,
alex
0
alex
2/17/2010 1:59:45 PM
access 16762 articles. 3 followers. Follow

5 Replies
1533 Views

Similar Articles

[PageSpeed] 28

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

alex wrote:
> 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
0
John
2/17/2010 3:31:43 PM
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


"alex" wrote:

> 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
> .
> 
0
Utf
2/17/2010 4:09:01 PM
On Feb 17, 11:09=A0am, Daryl S <Dar...@discussions.microsoft.com> wrote:
> Alex -
>
> The normal way to do this would be to pass the sql sttring into your
> RunQuery subroutine. =A0 You can use global variables, but passing the st=
ring
> parameter in is usually a better way to go. =A0
>
> If the subroutines you are using are both tied to one form, then you can
> also put a textbox on the form (.visible =3D FALSE so users don't see it)=
.. =A0
> Then you can 'store' your SQL string there in the WriteQuery subroutine, =
and
> can read it in the RunQuery subroutine.
>
> --
> Daryl S
>
>
>
> "alex" wrote:
> > Reference Variable in multiple Subs
>
> > Hello,
>
> > I=92m trying to accomplish the following:
>
> > Sub WriteQuery()
> > Dim strSQL as String
> > strSQL =3D _
> > =93Select * from myTable=94
> > End Sub
>
> > Sub RunQuery()
> > Docmd.RunSQL(strSQL) =91won=92t run because variable not defined in
> > immediate sub
> > End Sub
>
> > It=92s a simplified example, but I=92m trying to reference a variable
> > (that=92s set in one sub) in another sub=85can I do this in some manner=
?
>
> > Thanks,
> > alex
> > .- Hide quoted text -
>
> - 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 =3D "test"
End Sub

Sub RunString()
    Debug.Print strSQL
End Sub
0
alex
2/17/2010 5:07:44 PM
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 wrote:
> On Feb 17, 11:09 am, Daryl S <Dar...@discussions.microsoft.com> wrote:
>> 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
>>
>>
>>
>> "alex" wrote:
>>> 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
>>> .- Hide quoted text -
>> - 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
0
John
2/18/2010 1:28:47 AM
On Feb 17, 8:28=A0pm, John Spencer <spen...@chpdm.edu> wrote:
> 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()
> =A0 =A0strSQL =3D "Text"
> End Sub
>
> Sub PrintString()
> =A0 =A0 Debug.Print strSQL
> End Sub
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
>
>
> alex wrote:
> > On Feb 17, 11:09 am, Daryl S <Dar...@discussions.microsoft.com> wrote:
> >> Alex -
>
> >> The normal way to do this would be to pass the sql sttring into your
> >> RunQuery subroutine. =A0 You can use global variables, but passing the=
 string
> >> parameter in is usually a better way to go. =A0
>
> >> If the subroutines you are using are both tied to one form, then you c=
an
> >> also put a textbox on the form (.visible =3D FALSE so users don't see =
it). =A0
> >> Then you can 'store' your SQL string there in the WriteQuery subroutin=
e, and
> >> can read it in the RunQuery subroutine.
>
> >> --
> >> Daryl S
>
> >> "alex" wrote:
> >>> Reference Variable in multiple Subs
> >>> Hello,
> >>> I=92m trying to accomplish the following:
> >>> Sub WriteQuery()
> >>> Dim strSQL as String
> >>> strSQL =3D _
> >>> =93Select * from myTable=94
> >>> End Sub
> >>> Sub RunQuery()
> >>> Docmd.RunSQL(strSQL) =91won=92t run because variable not defined in
> >>> immediate sub
> >>> End Sub
> >>> It=92s a simplified example, but I=92m trying to reference a variable
> >>> (that=92s set in one sub) in another sub=85can I do this in some mann=
er?
> >>> Thanks,
> >>> alex
> >>> .- Hide quoted text -
> >> - 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. =A0I've attached my
> > simple example below:
>
> > Option Compare Database
> > Option Explicit
>
> > Private strSQL As String
>
> > Sub DefineString()
> > =A0 =A0 strSQL =3D "test"
> > End Sub
>
> > Sub RunString()
> > =A0 =A0 Debug.Print strSQL
> > End Sub- Hide quoted text -
>
> - Show quoted text -

Thanks John.  I'll look to see what I may have done wrong.
alex
0
alex
2/19/2010 6:04:40 PM
Reply:

Similar Artilces:

how do I attach multiple Word documents?
I need to attach multiple Word documents into one large document. Many have different headers, so I can't just copy the text into one master document. Would appreciate any help on this. Before you Insert Text (2007: Insert > Object > Text from File), be sure each document you're including has a Section Break New Page both before and after it. After you've built your document, go through the headers/footers to be sure the page nos. are sequenced as you want them. Or use the "boilerplate" macro devised by MVP Graham Mayor. On Feb 10, 10:43=A0am, El...

Sending from multiple accounts #3
I have two email accounts, one is POP3 (lets call this Account 1) and the other a MAPI (e.g. Accout 2). My default accout is the POP3. When I download email, the received emails go into the correct folders, that is to say Account 1 email goes in the Busines Folders Inbox and Account 2 mail goes in the Account 2 Inbox folder. No problem there, it works as expect. This issues is this: If I write an email on Account 2 and just click send (remember, account 1 is the POP3 account is default) the email is received as coming from Account 1. If they reply, it comes back to me as an email sent to...

Are Charts with Multiple XY Datasets Possible?
I'd like to be able to build a chart using multiple XY datasets -- say from two different experiments. If I plot the two columns of dataset as XY, then Add Data and try to plot the two columns of dataset B, get an XY plot (A), overlaid by two line plots, one for each column o dataset B. I tried googling but have not found any usefu information. Thanks Steve Shervais -- Message posted from http://www.ExcelForum.com when you add the 2nd range did you check the box saying that the firs column is the x axis values -- Message posted from http://www.ExcelForum.com Steve - There are s...

Print multiple items
How can you print multiple items without opening each one and print. Rick wrote: > How can you print multiple items without opening each one and print. Hold down the control key while clicking to select multiple items, just like in Explorer, and then File - Print? -- f.h. Rick, Please enlighten me. Why do so many folks want to print electronic mail? "Rick" <Rick@discussions.microsoft.com> wrote in message news:17BF6FD0-1EA8-4CBE-A54D-4F67C5A957F2@microsoft.com... > How can you print multiple items without opening each one and print. ...

Vlookup with variable column reference
Help! I am trying to link two workbooks with a vlookup that searches for the last populated columns' entry in a range (see example below). 27/03 28/03 29/03 30/03 31/03 Data 6 4 Is there a way of doing this in a function? In a macro I would use the Range().End(xltoleft) code but I cannot figure out how to do something similar in a worksheet function. I basically want the lookup column to start at day 31 and make its way backwards until it finds a value. Another issue is that the seemingly blank cells contain formula - ie. not empty. Any ideas? Thanks!! Jen 1 ...

how do I define a chart series with an indirect reference
I've made a XY scattergram that I want to copy for use to other workbooks with other data series. The exact part of the data series to be displayed in the chart varies between workbooks. In the data series for the chart, I'd like to include an indirect reference that will define the first and the last dat point to be displayed. How can I achieve this? A related question: how can I copy charts beween workbooks in a way that the reference to the original workbook is not being copied at the same time. I.e. I want the displayed data to be taken from the workbook I'm copying the ...

Relative reference Chart data
Hi, This is probably easier than I make it out to be but here goes. I'm making a list of references I've been using for a research paper in excel and because the list is quite large (50 sources) I wanted to visually depict how useful each reference is. So I created a bubble chart with "relevance" on the x-axis and "quality of the source" on the y-axis which references a 1-4 scale for relevance and quality. That works fine, but when I want to sort references, let's say by date, the references for the chart data are absolute, which means if a Row is sorted up or ...

Copying records from one (sub)form to another subform using Append
I have a mainform with products (and thier details) from a products table and a subform showing ingredients (from an ingredients table). I wish to select existing ingredient records from one subform and copy them to so that they appear as ingredients in a new Mainform. I have a selection box next to each of the ingredients to enable selection but how do I "copy" these selections to the new record on the new mainform. Do I use an append query to do this? Any guidance on doing this would be great (not too experienced in this area). Many Thanks On Tue, 9 Feb 2010...

XML Serialisation & Circular References
I have been able to get simple circular references to be serialized in xml by using the ImportTypeMapping method on the SoapReflectionImporter class. But I am unable to serialise circular references when the circular reference is contained with in a collection class, specifically I am using a custom ArrayList object. I keep getting a StackOverFlow Exception from the XmlSerializer class when attempting the serialisation. The classes are: Class A, Class B - this is derived from System.Collection.ArrayList Class C. Class A contains an instance of B Class B contains multiple instances of C Cl...

Multiple Copies of Messages!
In just the last week or so, my Outlook (2002, running in WinXP Pro) has started (1) sending as many as 6 copies to the recipients of some of my emails, and (2) sometimes putting 3-4 copies of incoming messages into my Inbox. It's starting to drive me crazy, and is a cause of constant apologies to friends, clients, etc. Not good. Any ideas anyone? I, too, noticed several copies of sent messages in my Outlook 2002 Sent Items folder dating back a couple of months. I didn't receive any comments from receipents, so I don't know that they received duplicates. It hasn't ...

Refering to control property on a form in VB
Hi I am trying to simply my code in a application by using a sub routine to amend the control properties of a form but I can't seem to get basic to accept the control property being sent to the routine as a string The code I am using is : Private Sub lSubControlPropertySet(stForm As String, stControl As String, stProperty As string, varValue As Variant) If IsNull(varValue) = True Then Forms(stForm)(stControl)(stProperty) = "" Else Forms(stForm)(stControl)(stProperty) = var...

Installing multiple Notes connector
Hi, I am referring to one of the MS guide, where it says it is possible to install multiple Lotus Connectors for scalability. Well..i tried to install just the connector component along with System manager component; but it refuses to install. My question: Do i need to install whole Exchange server in order to install Lotus COnnector on that server? Or, can i install just the connector component? Thanks, On Fri, 3 Nov 2006 01:01:02 -0800, Jack Dorson <JackDorson@discussions.microsoft.com> wrote: >Hi, > >I am referring to one of the MS guide, where it says it is possibl...

Multiple sorting
I'm trying to sort a report that I have by multiple options: 1st) by DATE and then by NAME, I went to: Advance filter Options I insert: first Date/Ascending;...then Lname/Ascending then apply filter, and is not working it's only filtering by date WHAT'S GOING ON??? Thank you!!! Hernan The Sorting and Grouping dialog exists in Access 2003 (as well as all previous versions of Access) However, if you're seeing Form View under the View menu, it sounds as though you're talking about a form, not a report as you stated in the beginning. -- Doug Steele, Microsoft Access MV...

Recording multiple checks in a single deposit
This seems like it's probably brutally simple, but I can't figure it out: how do I record multiple check numbers in a single deposit? The deposit consists of a couple checks from my primary checking account which I'm depositing, along with some cash, as a single deposit transcation in a different checking account. Using MS Money Deluxe 2003. - sph In microsoft.public.money, hodgdon wrote: >This seems like it's probably brutally simple, but I >can't figure it out: how do I record multiple check >numbers in a single deposit? > >The deposit consist...

Using CRM 4.0 Enterprise for multiple environments (production, te
Are you using CRM 4.0 Enterprise with multiple organizations such that certain organizations are set up for non-production purposes (production, test, training, development, demo, etc.)? For example, have you set it up so that you have: Production: http://crm.mycompany.com Test: http://crmtest.mycompany.com Training: http://crmtraining.mycompany.com Development: http://crmdev.mycompany.com Do you need to purchase user CALS for each organization, or can one user CAL be used for all organizations? Can all of the organizations leverage the same Active Directory organ...

Stacked bar for multiple series + secondary axis
Hello! :confused: Could somebody help me to get 2 or more parallel an distinct stacked bars? In the attached picture you will see that th only solution I got was to modify the bar width. Thank!!!!!!!!!!!!! +------------------------------------------------------------------- |Filename: Stacked bar.bmp |Download: http://www.excelforum.com/attachment.php?postid=3908 +------------------------------------------------------------------- -- gilbertc ----------------------------------------------------------------------- gilbertcn's Profile: http...

Opening Multiple files in separate Excel folders
Can this be done? Hi what do you mean with 'separate Excel folders'? -- Regards Frank Kabel Frankfurt, Germany Paul Dunn wrote: > Can this be done? Yes. Dim aryFiles Dim oFSO Sub LoopFolders() Dim i As Integer Set oFSO = CreateObject("Scripting.FileSystemObject") selectFiles "c:\MyTest" Set oFSO = Nothing End Sub '--------------------------------------------------------------------------- Sub selectFiles(sPath) '--------------------------------------------------------------------------- Dim Folder As Object Dim Files As Object D...

Schema reference
I'm looking for a reference to the AD attributes and what processes fill them. i.e. what user attributes get updated when a user logs on or off. Howdie! Am 15.03.2010 19:26, schrieb Chris: > I'm looking for a reference to the AD attributes and what processes fill > them. > > i.e. what user attributes get updated when a user logs on or off. Huuh, I guess you won't find a reference like that anywhere. What you could do is download Sysinternals' ADExplorer. It allows snapshot creation and comparison. Take a snapshot, do some action to the dire...

Multiple Addresses
If an account has two addresses, one at the main level and one under "More Addresses," how can I add a contact in so that the mapped address is the one under "More Addresses?" Thanks ...

Money somehow still keeps the reference to my old payee account
I am using MS Money 2005 Small Business v.14.0.150.1105. Here is a description of my problem: I setup a payee (i.e. "Citibank" with account number "X") in order to pay bills electronically. I pay bills. Everything works perfectly. My financial obligation with Citibank account number X is closed after a number of successful payments. After some time, I incur a new debt with Citibank, this time with account number "Y" (a different accoutn because it is a different financial obligation). I set up a new payee as "Citibank" with account number "...

An option to reassign parent account only or all sub-accounts
When reassigning Accounts, an additional step needs to be added to allow the user to reassigning the parent Account only or all related sub Accounts. Today, when the parent Account is reassigned, all related recodrs, including all sub-Accounts, are reassigned in error to the new user. This cascading function is currently causing significant issues for our large, multi-location Accounts that have one Account Manager with multiple Account managers owning the sub Accounts. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most vot...

Populate sub form
I have asubform that is linked to the main form via one field, multiples entries are allowed in the sub form, but the linked field is the same for all these records. I can add new records manually in the sub form okay. What I want to do , sactivate a query/programming etc that is done by clicking a button that automatically adds several records to the subform from a table that contains potential data items based on a field in the main form. ie if the item in the main form is set to tru, then the sub form is to have several specific records added to it. how do i do populate the subform ...

multiple emails #4
I am receiving the same email over and over again, multiple times -- I delete it and remove delete folder contents and the next time I hit send/receive they all show up again. They seem to be from one sender -- but I can't find anything on her computer to suggest why this is happening. It is as if the send/receive function on her computer is stuck and it just keep sending the same mail. ???? Hi, I think that the UIDL Cahing is broken. What happens with the New Profile in Outlook created through the MAil applet in the Control Panel ? Also, this could be a problem with the ...

One sender to multiple recipients
Hello, I need to know if there is an option in Exchange 2003 or an addon that spam an email to multiple mailbox. We are receiving important messages from our clients. With the time, those client send directly requests to a user mailbox (user@) instead to a general mailbox (info@). When the user is out of the office (2 or 5 days), nobody is aware about the message sent by the client to my user. So we need to replicate the message sent from particular client to multiple mailbox. Any idea? Thanks Alex >-----Original Message----- >Hello, >I need to know if there is an option in Exc...

Clearing variables
Hello, In my current project I have two variables that I initially set: MaxPages = Selection.Information(wdNumberOfPagesInDocument) CurPage = Selection.Information(wdActiveEndPageNumber) These are sent inside a Do Until loop that quits when CurPage = MaxPages. The first time I ran the macro it ran smoothly just like it was supposed to. The next time I ran the macro. Nothing happened. It occurred to me that maybe CurPage and MaxPages were still set and since they are equal the macro quits. This same behavior happened even after I got out of Word (2007) and back in again...