use value of variable in a statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ynot - 31 May 2007 20:19 GMT

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

Reply to this Message

Marshall Barton - 31 May 2007 21:59 GMT

>I would like to use the value of a variable in a statement within a module
>like
[quoted text clipped - 5 lines]
>If Location = strTestvariable then ................... I would like
>strTestvariable replaced with its content "Chicago"

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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Ynot - 31 May 2007 22:17 GMT

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....

>>I would like to use the value of a variable in a statement within a module
>>like
[quoted text clipped - 20 lines]
> see all of your code related to the variable before we can
> provide a more specific explanation.

Reply to this Message

Bob Hairgrove - 31 May 2007 23:03 GMT

>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
[quoted text clipped - 10 lines]
>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.

--
Bob Hairgrove
NoSpamPlease@Home.com

Reply to this Message

Ynot - 01 Jun 2007 00:24 GMT

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

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

On Thu, 31 May 2007 14:17:07 -0700, "Ynot" <a_ursoNOSPAM@hotmail.com>
wrote:

>Sorry for lack of clarity, let me restate the issue. I want the
>"statement"
[quoted text clipped - 13 lines]
>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.

--
Bob Hairgrove
NoSpamPlease@Home.com

Reply to this Message

Douglas J. Steele - 01 Jun 2007 00:49 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> 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
[quoted text clipped - 54 lines]
> Bob Hairgrove
> NoSpamPlease@Home.com

Reply to this Message

Ynot - 01 Jun 2007 01:41 GMT

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?

> Are you saying that AuthFunction contains the name of a field in the
> recordset?
[quoted text clipped - 61 lines]
>> Bob Hairgrove
>> NoSpamPlease@Home.com

Reply to this Message

Douglas J. Steele - 01 Jun 2007 12:00 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Doug,
>
[quoted text clipped - 78 lines]
>>> Bob Hairgrove
>>> NoSpamPlease@Home.com

Reply to this Message

rwr - 01 Jun 2007 14:49 GMT

> Doug,
>
[quoted text clipped - 8 lines]
>
> if compvalue = "Home" then.....

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

Reply to this Message

Marshall Barton - 01 Jun 2007 02:04 GMT

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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Ynot - 01 Jun 2007 15:34 GMT

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

>>Sorry for lack of clarity, let me restate the issue. I want the
>>"statement"
[quoted text clipped - 27 lines]
> trying to achieve instead of how you are trying to go about
> it.

Reply to this Message