Unable to retrieve Public variable in other modules ...

[Guest]

I have defined a public variable in a form.
I am unable to access it from another form.
Will I be able to access it in a query as well?

Thank you,
-Me

 

[Duane Hookom]

A public variable in a form is public to that form only. You can create a
public variable in a standard module.
Variables are not available to queries. You can write a very small function
that returns the variable:

Function GetMyVariable() as String
GetMyVariable= strMyVariable
End Function

You can then use GetMyVariable in your query (or almost anywhere else).

 

[Guest]

Duane,

How do I create a public variable in a Standard module? (excuse me for my
ignorance )

Thank you,
-Me

 

[Guest]

If you have a varialbe you say is Public and it is visible in one form but
not the other, it is not truely a Public variable. A true public variable is
declared in a standard module and dimmed:
Public strSomeVariable as String

Queries can't see any variable. The easiest way to do that is create a
Static function in a standard module or a Public function with a static
variable, depending on how your query will use it. Be aware that to use a
function in a query, you have to pass it an argument.
If you call a function like:
ReturnMeSomeThing()
It will only execute one time. If you want it to execute for each row it
has to have an argument:
ReturnMeSomeThing([SomeField])

Here is a simple static function that you can assign a value to and it will
return that value when called with that argument missing.

You initially set the value like you would a public variable:
GetSSN("1112234334")
Now, when you call it:
strSocSec = GetSSN
strSocSec will contain 111223434

Static Function GetSSN(Optional ByVal varNewSSN As Variant) As String
Dim varSSN As Variant

If Not IsMissing(varNewSSN) Then
varSSN = varNewSSN
End If
GetSSN = Nz(varSSN, "")
End Function

Now, since we need to pass an argument to the query for it to work on each
row, we can set up a second optional argument so the function will know what
to do with it.

In this example, we want to set the fee multiplier to .25, so we do the same
as before:

AddFee(.25)

Then if we want to use it in a calculation
AddFee(, 300)
It will return 75
Note the comma before the 300 so the function knows the first argument is
missing.


Static Function AddFee(Optional ByVal varNewFee As Variant, _
Optional ByVal varAdder As Variant) As Long
Dim varFee As Variant

If Not IsMissing(varNewFee) Then
varFee = varNewFee
End If

If Not IsMissing(varAdder) Then
AddFee = varAdder * varFee
Else
AddFee = Nz(varFee, 0)
End If
End Function

 

[Douglas J. Steele]

In the database, select Modules from the list of objects (or the tab,
depending on what version of Access you're using) and click on the New
button. Alternative, while you're in the VB editor, you can select Module
from the Insert menu.

Once you've got the module, create it the same way as you did in the form.

 

[Guest]

Can a query see a truely Public variable?
I was under the impression (all these years) that a query can't see any
variable.

BTW, the report issue I was having (thanks again), I tried creating a temp
table to use as a record source, but it did not work. We decided to resolve
the problem by modifying the requirements doc.

 

[Guest]

Thanks Doug,

I will try it and let you all know on Tue.

-Me

 

[Guest]

Thanks Klatuu,

I will try it and let you all know on Tue.

-Me

If you have a varialbe you say is Public and it is visible in one form but
not the other, it is not truely a Public variable. A true public variable is
declared in a standard module and dimmed:
Public strSomeVariable as String

Queries can't see any variable. The easiest way to do that is create a
Static function in a standard module or a Public function with a static
variable, depending on how your query will use it. Be aware that to use a
function in a query, you have to pass it an argument.
If you call a function like:
ReturnMeSomeThing()
It will only execute one time. If you want it to execute for each row it
has to have an argument:
ReturnMeSomeThing([SomeField])

Here is a simple static function that you can assign a value to and it will
return that value when called with that argument missing.

You initially set the value like you would a public variable:
GetSSN("1112234334")
Now, when you call it:
strSocSec = GetSSN
strSocSec will contain 111223434

Static Function GetSSN(Optional ByVal varNewSSN As Variant) As String
Dim varSSN As Variant

If Not IsMissing(varNewSSN) Then
varSSN = varNewSSN
End If
GetSSN = Nz(varSSN, "")
End Function

Now, since we need to pass an argument to the query for it to work on each
row, we can set up a second optional argument so the function will know what
to do with it.

In this example, we want to set the fee multiplier to .25, so we do the same
as before:

AddFee(.25)

Then if we want to use it in a calculation
AddFee(, 300)
It will return 75
Note the comma before the 300 so the function knows the first argument is
missing.


Static Function AddFee(Optional ByVal varNewFee As Variant, _
Optional ByVal varAdder As Variant) As Long
Dim varFee As Variant

If Not IsMissing(varNewFee) Then
varFee = varNewFee
End If

If Not IsMissing(varAdder) Then
AddFee = varAdder * varFee
Else
AddFee = Nz(varFee, 0)
End If
End Function

I have defined a public variable in a form.
I am unable to access it from another form.
Will I be able to access it in a query as well?

Thank you,
-Me

 

[Douglas J. Steele]

That's absolutely correct, but Duane had already given that answer.

I was simply answering the specific question "How do I create a public
variable in a Standard module?"