Setting a Variable

[Bob Quintal]

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

Yes, but you have to Dim the variable above the first private
[sub|function] declaration

 

[tina]

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

 

[Douglas J. Steele]

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

 

[Douglas J. Steele]

<picky>
That should have been

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

</picky>

 

[DS]

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

 

[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

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

 

[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

 

[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

Thanks Douglas,
This works fine.
But I'm still interested in this class module thing.
DS

 

[David W. Fenton]

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

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]

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.

 

[DS]

Interesting David,
How would one do this?
ds

 

[David W. Fenton]

Interesting David,
How would one do this?

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.

 

[DS]

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.

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

 

[DS]

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