Hi,
I have named a variable in Visual Basics(In Microsoft Access). The
variable is called "RandomVariable1". I was wondering if it is
possible for me to store "RandomVariable1" to be used in another
procedure. My understanding is that all variables are automatically
cleared once it reaches "End Sub".

How do I recall this from another procedure?

Regards,

Jimmy Hung

---

Clarification of Question by shlodge-ga on 23 Jul 2006 03:15 PDT

Hi,
What I have done is created an SQL statement and assigned it to a query.
The SQL statement has 3 parts(stored in 3 different variables), Select
From and Where.
Once the query executes the user is lead to another form. I would like
to use the same SQL for my next query (Select, From and Where). The
only part that I will be modifying is the Where Statement.
Any suggestion on what is the best way to do this?

Regards,

Jimmy

Jimmy

Hi,

There are many ways to accomplish this. However, you need to tell
exactly what you are trying to accomplish to give the optimized way of
doing this. With the information you have given I have explained 3
ways of doing it.

To use the variable RandomVariable1 you need to declare at the form
level or the module level. If you declare RandomVariable1 at the "sub"
level then the value would get cleared automatically once the end sub
is reached.

1. Declaring the variable at the form level - If you have your
procedure in the form level then you can use the variable in any
procedure defined in that form. Each procedure can read/write the
value of variable.

Eg.

Add a form, followed by two buttons. Right click on one of the buttons
and click "Build Event".

'----------------*********************
Option Compare Database
Dim x As Integer

Private Sub Command0_Click()
    x = 5
End Sub


Private Sub Command1_Click()
    MsgBox x + 5
End Sub
'----------------*********************

click Command0 followed by Command1

2. Declare variable at the module level - If you want the variable
across all the sub-procedures and across all the forms then you can
declare in a module. This would be a global variable. But be careful
of using global variable. Its not a very good programming practice. It
should be used only when no other option is left.

E.g.
To add a module 

In the code window right click "Microsoft Access Class Object",
followed by insert module.

'----------------*********************
Option Compare Database

Public x as Integer

'----------------*********************

You can read/write x in any form in the project and in any subs.

3. You could also do this in a much better way by creating properties
and persist them in a class module (OOP way). But I cannot explain
this because one needs to understand the requirement.

Thanks and regards,
Sunil

Hi, 
   Thnx for the interesting question.
There are no. of ways to take over the variable to use from one
procedure call to another
1. Extending the scope: By defining publically or static.
2. Attaching this with tag of any component which support tag like Form
3. Passing it as a argument to the other function.
4. Storing it in a text file (may be in encrypted format) or in a
invisible text box. and then the value can be taken from that text
box.It can also be stored as any invisible thing names or values.
5. U can make ur defined procedural or method call which contain that
value and returned whenver that function is called.(Its the simplest
and most popular one).
Please Let me know that is these steps have solved ur problems or not.
If not, please further clarify your requirement.
Looking for a response from you.

Hi,

I would suggest u create 3 properties for Select, From and Where
clause in the 2nd form. Set the properties from the calling form. In
the second form initialise form level variables for Select, From and
Where, the value being coming from the parameter of the property.

For E.g.

in the second form lets say there are 3 variables at form level

Dim sSelect as string
Dim sFrom as string
Dim sWhere as string

Public property Set SelectClause (byval sString as string)
      sSelect = sString
end property

'Similarly write the property code for other 2 viz. From and Where clause

In the calling form Form1

Dim oForm2 as new Form2

oForm2.SelectClause = <your local variable that has the select statement value>
'Similarly set properties for Where and From clauses

Hope this make sense...

Regards,
Sunil