Setting a Variable

  • Thread starter Thread starter Bob Quintal
  • Start date Start date
B

Bob Quintal

DS said:
Can I set a variable on the OnOpen event of a form.
And then use the value as long as the form is open?
Any special tricks to this?
Thanks
DS
Click to expand...

Yes, but you have to Dim the variable above the first private
[sub|function] declaration
 
you can, but AFAIK you can lose the value in the variable if you experience
an unhandled error while the form is open. you might want to store the value
in the form's Tag property instead, as

Me.Tag = somevalue

note that Tag stores values as String data type, so if the value is a
number, you might have to use a conversion function on it when you use it in
the code, for instance

lngValue = CLng(Me.Tag)*2

hth
 
One thing Tina forgot to point out is that if you do want to use a variable,
you cannot declare it inside the Form_Open sub. Instead, you must declare
the variable at the top of the module, before any subs or functions:

Option Compare Database
Option Explicit

Dim MyVariable As Long

Private Sub Form_Open()
MyVariable = 42
End Sub
 
<picky>
That should have been

Private Sub Private Sub Form_Open(Cancel As Integer)
MyVariable = 42
End Sub

</picky>
 
Can I set a variable on the OnOpen event of a form.
And then use the value as long as the form is open?
Any special tricks to this?
Thanks
DS
 
Douglas said:
One thing Tina forgot to point out is that if you do want to use a variable,
you cannot declare it inside the Form_Open sub. Instead, you must declare
the variable at the top of the module, before any subs or functions:

Option Compare Database
Option Explicit

Dim MyVariable As Long

Private Sub Form_Open()
MyVariable = 42
End Sub
Click to expand...
Thanks Douglas,
I was wondering...
Can I make my variable a class and then declare it at the module level.
Then I can just put it at the top of every module that I need it on.
Dim OKA AS REDACTIVE

My variable would be called "REDACTIVE"
And the contained in the class would be...
Dim strPath As String
strPath=Nz(DLookup("BackPath","tblBackPath","BackActive =-1 And BackID =1")

But how do you build a class?
Thanks
DS
 
This is my Class Module
Public Function REDACTIVE(strPath As String) As String
Dim strPath As String
strPath = Nz(DLookup("BackPath", "tblBackPath", "BackID =1 AND
BackActive=-1"), "")
End Function

Then at the top of the module on a form I have...
Option Compare Database
Option Explicit
Dim RA As REDACTIVE

Then in a procedjure I have...
Dim Test2SQL As String
If RA > "" Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & RA & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
Else
End If

Any reason why this isn't working.
Thanks
DS
 
Douglas said:
One thing Tina forgot to point out is that if you do want to use a variable,
you cannot declare it inside the Form_Open sub. Instead, you must declare
the variable at the top of the module, before any subs or functions:

Option Compare Database
Option Explicit

Dim MyVariable As Long

Private Sub Form_Open()
MyVariable = 42
End Sub
Click to expand...
Thanks Douglas,
This works fine.
But I'm still interested in this class module thing.
DS
 
One thing Tina forgot to point out is that if you do want to use a
variable, you cannot declare it inside the Form_Open sub. Instead,
you must declare the variable at the top of the module, before any
subs or functions:

Option Compare Database
Option Explicit

Dim MyVariable As Long

Private Sub Form_Open()
MyVariable = 42
End Sub
Click to expand...

You could also declare it as static within a function that
initializes it and returns the value after it's initialized. I'm
doing this more and more often with variables that I would normally
declare at module level, and I think it makes for cleaner and
better-organized code.
 
DS wrote:
Ok so at this point I have the variable set On Open of the form at that
seems to be working fine. Just a few questions. Will this work over
and over again as needed once the form is opened. Also is this better
than On Load. Should I store it in a textbox intead of a variable?
Just trying to cover all of my bases, also I don't want to keep having
to look this value up. I just want to set it once on the form and refer
to it as needed.
 
Interesting David,
How would one do this?
Click to expand...

Public Function SomeValue(Optional strSetValue As String) As String
Static strValue As String

If Len(strSetValue)>0 And Len(strValue)>0 Then
strValue = strSetValue
End If
SomeValue = strSetValue
End Function

Now, you may see a problem with that -- there's no way to
de-initialize it. This kind of thing is most useful when you have a
value that is going to be referred to many times in a session, but
looked up only once. It's very useful for things you look up from
tables, or for something like the Windows user logon, which can't
change during the Access session.
 
David said:
Public Function SomeValue(Optional strSetValue As String) As String
Static strValue As String

If Len(strSetValue)>0 And Len(strValue)>0 Then
strValue = strSetValue
End If
SomeValue = strSetValue
End Function

Now, you may see a problem with that -- there's no way to
de-initialize it. This kind of thing is most useful when you have a
value that is going to be referred to many times in a session, but
looked up only once. It's very useful for things you look up from
tables, or for something like the Windows user logon, which can't
change during the Access session.
Click to expand...
OK, that sounds good. Being that I have something that meets that
criteria, he path to the redundant database, I think that would work.
If for any reason I need to change the path during the day would that be
a problem? Would closing the application be a problem? I mean this
de-initializing? Is this a Class Module, Can I refer to it on many
different forms?
Thanks
DS

Public Function REDACT() As String
Static strPath as String
strPath = Nz(DLookup("BackPath","tblBackPath","BackID=1 AND
BackActive=-1"),"")
End Function
 
OK, that sounds good. Being that I have something that meets that
criteria, he path to the redundant database, I think that would
work. If for any reason I need to change the path during the day
would that be a problem? Would closing the application be a
problem? I mean this de-initializing? Is this a Class Module,
Can I refer to it on many different forms?

Public Function REDACT() As String
Static strPath as String
strPath = Nz(DLookup("BackPath","tblBackPath","BackID=1 AND
BackActive=-1"),"")
End Function
Click to expand...

Your static variable hasn't accomplished anything here, as you are
re-initializing it any time you call the function. Second, you
aren't returning your strPath value. You want to do something like
this:

Public Function REDACT(Optional bolReInitialize As Boolean) _
As String
Static strPath as String

If Len(strPath) = 0 Or bolReInitialize Then
strPath = Nz(DLookup("BackPath","tblBackPath", _
"BackID=1 AND BackActive=-1"),"")
End If
REDACT = strPath
End Function

If you call it without the parameter, you get the looked up value
the first time, and the value stored in the static variable all
further times. If pass True as the parameter, then it will look up
the value again.

If the value in the table is going to change during an application
session, then you really don't want a static variable -- you want it
looked up every time.
 
Thanks David,
I appreciate the input. Static variales sound interesting though, but I
guess your right, the possibility of it changing are slim but the
possibility is there. So I'll keep the function, but I will keep this
is mind for future reference.
Thanks
DS
 
Back
Top