use value of variable in a statement

  • Thread starter Thread starter Ynot
  • Start date Start date
Y

Ynot

I would like to use the value of a variable in a statement within a module
like
Dim strTestvariable as string

strTestvariable = "Chicago" ' This value will be passed by another
module or a form


If Location = strTestvariable then ................... I would like
strTestvariable replaced with its content "Chicago"

Thanks for the help
 
Ynot said:
I would like to use the value of a variable in a statement within a module
like
Dim strTestvariable as string

strTestvariable = "Chicago" ' This value will be passed by another
module or a form


If Location = strTestvariable then ................... I would like
strTestvariable replaced with its content "Chicago"
Click to expand...


Variables are not "replaced", but you will get the **value**
of the variable will be used so you will get the desireable
effect.

Why do you think this is a problem? If you tried it and it
didn't do what you want, then I suspect that the variable's
declaration is not in the same scope as the code the uses
the value of the variable. Or, maybe forgot to set the
variable's value. Or, maybe you set the variable's value in
one scope and reference it in another.

If you can't figure out your problem, then we will need to
see all of your code related to the variable before we can
provide a more specific explanation.
 
Thanks Marshal

Sorry for lack of clarity, let me restate the issue. I want the "statement"
to be modified and the variable name to be replaced with the variable
content. better example


strcompare = "screenName"

If strcompare = "ynot" then... This
statement would execute as :

If screenName = "ynot" then......

That is where I want the value of the variable to replace the variable name.
I thought there were special characters to wrap the variable name in so that
the name is replaced by the content... BUT I can't find it....
 
Sorry for lack of clarity, let me restate the issue. I want the "statement"
to be modified and the variable name to be replaced with the variable
content. better example


strcompare = "screenName"

If strcompare = "ynot" then... This
statement would execute as :

If screenName = "ynot" then......

That is where I want the value of the variable to replace the variable name.
I thought there were special characters to wrap the variable name in so that
the name is replaced by the content... BUT I can't find it....
Click to expand...

Check out the Eval() function ... this might be what you want.
 
Maybe I'm not getting it and still can't find an example of what I want to
do but I do remember seeing it done. Here is the exact code. I have tried
several variations of brackets etc so that The variable "AuthFunction" is
replaced in the IF statement with the value in the variable.

************************************************************************************
Public Function checkAuth(Badge, AuthFunction) As String
Dim strsql, rtxx As String
Dim rst As Recordset

strsql = "SELECT authtocoll, "
strsql = strsql & "authtodel, "
strsql = strsql & "authtoMachine, "
strsql = strsql & "authtoadmin, "
strsql = strsql & "authoffhourentry, "
strsql = strsql & "authtomaint "
strsql = strsql & "FROM members WHERE MEMBERBADGENUMBER = " & Badge & ";"
Set rst = CurrentDb.OpenRecordset(strsql)



If rst.[Eval("AuthFunction")] <> True Then
rtxx = MsgBox("You are not authorized for this function",
vbCritical)
checkAuth = False
End If

rst.Close

End Function

******************************************************************************************






Sorry for lack of clarity, let me restate the issue. I want the
"statement"
to be modified and the variable name to be replaced with the variable
content. better example


strcompare = "screenName"

If strcompare = "ynot" then... This
statement would execute as :

If screenName = "ynot" then......

That is where I want the value of the variable to replace the variable
name.
I thought there were special characters to wrap the variable name in so
that
the name is replaced by the content... BUT I can't find it....
Click to expand...

Check out the Eval() function ... this might be what you want.
 
Are you saying that AuthFunction contains the name of a field in the
recordset?

If rst.Fields(AuthFunction) <> True Then
rtxx = MsgBox("You are not authorized for this function", vbCritical)
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ynot said:
Maybe I'm not getting it and still can't find an example of what I want to
do but I do remember seeing it done. Here is the exact code. I have
tried several variations of brackets etc so that The variable
"AuthFunction" is replaced in the IF statement with the value in the
variable.

************************************************************************************
Public Function checkAuth(Badge, AuthFunction) As String
Dim strsql, rtxx As String
Dim rst As Recordset

strsql = "SELECT authtocoll, "
strsql = strsql & "authtodel, "
strsql = strsql & "authtoMachine, "
strsql = strsql & "authtoadmin, "
strsql = strsql & "authoffhourentry, "
strsql = strsql & "authtomaint "
strsql = strsql & "FROM members WHERE MEMBERBADGENUMBER = " & Badge & ";"
Set rst = CurrentDb.OpenRecordset(strsql)



If rst.[Eval("AuthFunction")] <> True Then
rtxx = MsgBox("You are not authorized for this function",
vbCritical)
checkAuth = False
End If

rst.Close

End Function

******************************************************************************************






Sorry for lack of clarity, let me restate the issue. I want the
"statement"
to be modified and the variable name to be replaced with the variable
content. better example


strcompare = "screenName"

If strcompare = "ynot" then... This
statement would execute as :

If screenName = "ynot" then......

That is where I want the value of the variable to replace the variable
name.
I thought there were special characters to wrap the variable name in so
that
the name is replaced by the content... BUT I can't find it....
Click to expand...

Check out the Eval() function ... this might be what you want.
Click to expand...
 
Doug,

THANX!!!! That solves my problem. I tested it and it works. THANX!!!!

Was I wrong though??? That is still bothering me!!!
Isn't there a pair of characters or something that will allow a replacement
of a variable with the content of the variable in a statement prior to
execution?

Like:

if compvalue = "Home" then.....

Where "compvalue" is replaced by its content like "home" or "office" or
"mobile" etc prior to the compare?



Douglas J. Steele said:
Are you saying that AuthFunction contains the name of a field in the
recordset?

If rst.Fields(AuthFunction) <> True Then
rtxx = MsgBox("You are not authorized for this function", vbCritical)
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ynot said:
Maybe I'm not getting it and still can't find an example of what I want
to do but I do remember seeing it done. Here is the exact code. I have
tried several variations of brackets etc so that The variable
"AuthFunction" is replaced in the IF statement with the value in the
variable.

************************************************************************************
Public Function checkAuth(Badge, AuthFunction) As String
Dim strsql, rtxx As String
Dim rst As Recordset

strsql = "SELECT authtocoll, "
strsql = strsql & "authtodel, "
strsql = strsql & "authtoMachine, "
strsql = strsql & "authtoadmin, "
strsql = strsql & "authoffhourentry, "
strsql = strsql & "authtomaint "
strsql = strsql & "FROM members WHERE MEMBERBADGENUMBER = " & Badge & ";"
Set rst = CurrentDb.OpenRecordset(strsql)



If rst.[Eval("AuthFunction")] <> True Then
rtxx = MsgBox("You are not authorized for this function",
vbCritical)
checkAuth = False
End If

rst.Close

End Function

******************************************************************************************






Sorry for lack of clarity, let me restate the issue. I want the
"statement"
to be modified and the variable name to be replaced with the variable
content. better example


strcompare = "screenName"

If strcompare = "ynot" then... This
statement would execute as :

If screenName = "ynot" then......

That is where I want the value of the variable to replace the variable
name.
I thought there were special characters to wrap the variable name in so
that
the name is replaced by the content... BUT I can't find it....
Click to expand...

Check out the Eval() function ... this might be what you want.
Click to expand...
Click to expand...
 
Ynot said:
Sorry for lack of clarity, let me restate the issue. I want the "statement"
to be modified and the variable name to be replaced with the variable
content. better example


strcompare = "screenName"

If strcompare = "ynot" then... This
statement would execute as :

If screenName = "ynot" then......

That is where I want the value of the variable to replace the variable name.
I thought there were special characters to wrap the variable name in so that
the name is replaced by the content... BUT I can't find it....
Click to expand...


Sorry, but either that example does not demonstrate what you
are trying to do or you do not understand how to use
variables.

Regardless, there is no way to "replace" part of a
statement. It is possible to use a variable's value as the
name of an item in a collection or to evaluate an expression
contained in a variable, but the VBA language has no
capability for substitution.

Maybe it would help if you explainrd what objective you are
trying to achieve instead of how you are trying to go about
it.
 
I'm not aware of that capability. I don't believe Eval will do it: certain a
test I ran just now didn't work.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ynot said:
Doug,

THANX!!!! That solves my problem. I tested it and it works. THANX!!!!

Was I wrong though??? That is still bothering me!!!
Isn't there a pair of characters or something that will allow a
replacement of a variable with the content of the variable in a statement
prior to execution?

Like:

if compvalue = "Home" then.....

Where "compvalue" is replaced by its content like "home" or "office" or
"mobile" etc prior to the compare?



Douglas J. Steele said:
Are you saying that AuthFunction contains the name of a field in the
recordset?

If rst.Fields(AuthFunction) <> True Then
rtxx = MsgBox("You are not authorized for this function", vbCritical)
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ynot said:
Maybe I'm not getting it and still can't find an example of what I want
to do but I do remember seeing it done. Here is the exact code. I have
tried several variations of brackets etc so that The variable
"AuthFunction" is replaced in the IF statement with the value in the
variable.

************************************************************************************
Public Function checkAuth(Badge, AuthFunction) As String
Dim strsql, rtxx As String
Dim rst As Recordset

strsql = "SELECT authtocoll, "
strsql = strsql & "authtodel, "
strsql = strsql & "authtoMachine, "
strsql = strsql & "authtoadmin, "
strsql = strsql & "authoffhourentry, "
strsql = strsql & "authtomaint "
strsql = strsql & "FROM members WHERE MEMBERBADGENUMBER = " & Badge &
";"
Set rst = CurrentDb.OpenRecordset(strsql)



If rst.[Eval("AuthFunction")] <> True Then
rtxx = MsgBox("You are not authorized for this function",
vbCritical)
checkAuth = False
End If

rst.Close

End Function

******************************************************************************************






Sorry for lack of clarity, let me restate the issue. I want the
"statement"
to be modified and the variable name to be replaced with the variable
content. better example


strcompare = "screenName"

If strcompare = "ynot" then... This
statement would execute as :

If screenName = "ynot" then......

That is where I want the value of the variable to replace the variable
name.
I thought there were special characters to wrap the variable name in so
that
the name is replaced by the content... BUT I can't find it....

Check out the Eval() function ... this might be what you want.
Click to expand...
Click to expand...
Click to expand...
 
Ynot said:
Doug,

THANX!!!! That solves my problem. I tested it and it works. THANX!!!!

Was I wrong though??? That is still bothering me!!!
Isn't there a pair of characters or something that will allow a replacement
of a variable with the content of the variable in a statement prior to
execution?

Like:

if compvalue = "Home" then.....
Click to expand...

cut

I don't think Access has anything like what you want. A number of years
ago I converted a Visual FoxPro program I wrote to Access and had to
chang the FoxPro version of Eval to use code like Doug gave you.

As I remember it FoxPro used a @ or & in front of the variable.
I think the reason it doesn't work is because of how the code is complied.

Ron
 
Marsh,

Thanks for the reply. What I was trying to accomplish is a generic security
routine that can be called to evaluate authorization to perform a particular
function. I wanted to call the routine with the name of the function and
let the routine check authorization, set a return code or issue appropriate
messages. The "fields" parameter using the field name on the recordset
allowed me to solve the problem.

Thanks to everyone for the help and direction.

Tony
 
Back
Top