'Macro' Substitution of Variables

[Dr. Know]

Used to program extensively in Clipper S87 (!)
I have been using Access for a couple of weeks.
I have run into a problem when using public/privately declared
variables in queries and text boxes. I figured a work around, using a
function to retrieve the variable value, and it works OK.

Now I would like to create a function that would return the value of a
variable name passed to it. In Clipper, preceding the name with &
would work, but not in Access. Is there an Access equivalent??

The function would be something like this:

Public Function GetVar( varname as string) as variant
getval = &varname
end function

i.e. - var1 = "this" var2 = "that"

GetVar("var1") returns "this
GetVar("var2") returns "that"

Is this possible?

I'm still trying to get over the grief involved in highlighting a
record row without enduring screen flickering and ridiculous,
convoluted code...

Thanks,


Dr. Know

 

[John Vinson]

Now I would like to create a function that would return the value of a
variable name passed to it. In Clipper, preceding the name with &
would work, but not in Access. Is there an Access equivalent??

Eval(varname)

 

[Albert D. Kallal]

Used to program extensively in Clipper S87 (!)
I have been using Access for a couple of weeks.
I have run into a problem when using public/privately declared
variables in queries and text boxes. I figured a work around, using a
function to retrieve the variable value, and it works OK.

I did a good stint in FoxPro. However, when using ms-access, you will find
that you have to go through paradigm change in how you think, and how you
write software. You still seem to be thinking in terms of x-base, and that
apporach will NOT work with ms-access.

For example, while we don't
have macro substitution, virtually ALL things in ms-access are a collection.
The
result of this is that I do NOT miss, or even need macro substitution. For
example, if you have several forms open, and wanted at run-time to resolve
to
a particular form, and a particular control on the screen you could go:


dim strForm as string
dim strCtl as string


strForm = inputbox("What form")
strCtl = inputbox("Display what field")

msgbox "result = " + forms(strForm)(strCtl)

So, resolving to a particular field, or control on a screen can be done with
a simple string value.

It is most important to have a good understanding of how objects and
collections are referenced in ms-access. While you can use the Eval(), with
objects and collections you likely will not need to use the "concept" of
macro substitution at all. I don't miss, or EVER need the feature ONCE I
learned how ms-access and collections work.

Check out the following on how to reference objects:

http://www.microsoft.com/accessdev/articles/bapp97/chapters/ba05_2.htm

I'm still trying to get over the grief involved in highlighting a
record row without enduring screen flickering and ridiculous,
convoluted code...

Not sure what/how you are tying to do things, but perhaps you are not using
the right control for the right job? Take a look at the following screen
shots to get some ideas for displaying data as a grid. Once you get the hang
of what control to use, then you will realize how lame the "browse" command
used in the x-base variants of yester years was....

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

 

[Dr. Know]

John Vinson said:

Eval(varname)

Hmmm.... Either I'm a moron, or this doesn't work in Access 2000.

I've tried imbedding this into VB RunSQL statements, unbound text
boxes in the default value field as -- =eval("dspWebId") --,
and the function:
------------------------------------------------------------------------------------
Public Function GetVar( varname as string) as variant
getval = Eval(varname)
End Function
------------------------------------------------------------------------------------
What I want it to return:

var1 = "this" var2 = "that"

GetVar("var1") returns "this"
GetVar("var2") returns "that"

---------------------------------------------------
What it does:
Crashes into debugger with a "can't find varname" error.

Any Ideas?


Dr. Know

 

[david epsom dot com dot au]

In Access, public variables are not by default in
a public collection. If you wish to refer to variables
by name, you must put them into a collection.

There are a few default collections you can use:
the Forms collection and the Tables collection
spring to mind. If you want to refer to a variable
by name, you can add a control or property with
that name to a form. Or you can add a field of that
name to a table (or stored querydef).

Or you can create your own collection of named items
and refer to that.

The advantage of using the native Access collections
rather than your own collection is that Access SQL
can see items in the Forms/Reports/Queries/Tables
collections. If you are going to use a Function as
the interface anyway, you can just create your own
collection of named items and ignore the Forms/Reports
Queries/Tables etc.

(david)

 

[Dr. Know]

Albert D. Kallal said:

I did a good stint in FoxPro. However, when using ms-access, you will find
that you have to go through paradigm change in how you think, and how you
write software. You still seem to be thinking in terms of x-base, and that
apporach will NOT work with ms-access.

Thanks for the input. I had a look at some of the screens on your
pages, and they look good.

I think I have a fair grip on the object oriented approach to Access,
as I have been programming (D)HTML / JAVA and such for a few years.

I *used* to do 6502 and 80x6 ASM, C/C++ as well as Clipper stuff.

For example, while we don't
have macro substitution, virtually ALL things in ms-access are a collection.

I've had good fun with form and control objects and properties, and
the ease of referencing them in code, BUT...

It still doesn't solve my problem of having a variable - not a text
box or control - refusing to imbed into an SQL query without
workarounds.

This is a hybrid system that must work with information from a foreign
(and archaic) DDMS system, and some of the information is stored in
files on disk. I have been retrieving this information using file
stream objects (I was amazed at how easy this was) and then generate
queries based upon these variables.

User input, text boxes and selection lists have no bearing on the
queries or their criteria.

Not sure what/how you are tying to do things, but perhaps you are not using
the right control for the right job? Take a look at the following screen
shots to get some ideas for displaying data as a grid. Once you get the hang
of what control to use, then you will realize how lame the "browse" command
used in the x-base variants of yester years was....

Could be, but with a continuous form, there seems to be no easy way to
scroll through displayed records with a highlighted row of all fields
in the current record. The record count can easily be over 100,000.
I assume the first example, left side is a list box? Grid?

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

Nice screens and logic - I'll study them for more good ideas.

Thanks,
Greg





Dr. Know

 

[Paul Overway]

You are passing a string value...and then expecting the same string value
back. Why bother?

Eval is used to evaluate an expression. The reason you get an error is
"this" and "that" are not variables or functions that can be evaluated. So,
the variables must be declared somewhere in your module, i.e.,

Public var1 As Variant
Public var2 As Variant

Public Function GetVar( varname as string) as variant
getval = Eval(varname)
End Function

Private Sub OtherProcess()

var1 = "this"
var2 = "that"
End Sub

Now, you can use GetVar("var1") in a query expression. If the Jet SandBox
is set to default, you can just use Eval("var1") in your query....skipping
the GetVar function...but using your GetVar function is ok and negates the
effect of any SandBox setting other than the default.

Just a style thing....but I rarely find a real need for public variables.
You should limit variable scope to the maximum extent possible...it will
help with debugging and is safer than using a public variable that could get
set to an unexpected value from an unexpected place. The only time I ever
use a public variable is for objects...and even then rarely. So, code above
might be revised as follows instead:

Private var1 As Variant
Private var2 As Variant

Public Function GetVar( varname as string) as variant
getval = Eval(varname)
End Function

Private Sub OtherProcess()

var1 = "this"
var2 = "that"
End Sub


This way....var1 and var2 can only be assigned values within the
module...less surprises to debug.

 

[Dr. Know]

Paul Overway said:

You are passing a string value...and then expecting the same string value
back. Why bother?

Hmmm.... Perhaps there is some misunderstanding here...

I'm passing the NAME of a variable as a string, and expecting a return
of the VALUE contained in the named variable. This is for queries,
that don't seem to want to accept anything but objects as parameters.

Eval is used to evaluate an expression. The reason you get an error is
"this" and "that" are not variables or functions that can be evaluated. So,
the variables must be declared somewhere in your module, i.e.,

Public var1 As Variant
Public var2 As Variant

Public Function GetVar( varname as string) as variant
getval = Eval(varname)
End Function

Private Sub OtherProcess()

var1 = "this"
var2 = "that"
End Sub

Exactly the same code I tried, but with the minor distinction of the
declaration of var1 and var2 as Singles rather than Variants.

Now, you can use GetVar("var1") in a query expression. If the Jet SandBox
is set to default, you can just use Eval("var1") in your query....skipping
the GetVar function...but using your GetVar function is ok and negates the
effect of any SandBox setting other than the default.

That's is what I'm after... If it will work...
I'm trying to create a generic routine to retrieve ANY variable's
value by NAME from within queries, without the need to have multiple
functions for each variable.

Just a style thing....but I rarely find a real need for public variables.
You should limit variable scope to the maximum extent possible...it will

I'm aware of the hazards and implications of variable Scope. ;-)

This example just happened to be of system-wide variables that need to
be referenced from multiple forms and queries.

help with debugging and is safer than using a public variable that could get
set to an unexpected value from an unexpected place. The only time I ever
use a public variable is for objects...and even then rarely. So, code above
might be revised as follows instead:

Private var1 As Variant
Private var2 As Variant

Public Function GetVar( varname as string) as variant
getval = Eval(varname)
End Function

Private Sub OtherProcess()

var1 = "this"
var2 = "that"
End Sub

I would assume that the declarations of the private variables var1 and
var2 could be included in the Sub, rather than the form declaration,
in order to further limit their scope.

This way....var1 and var2 can only be assigned values within the
module...less surprises to debug.

Asuming you want to reference them within the scope of only that
module... ;-)

Thanks for the input.
I'll try the change in the variable declarations ASAP.

Greg





Dr. Know

 

[david epsom dot com dot au]

Now, you can use GetVar("var1") in a query expression.

I don't think so. Unless it has changed in more recent
versions of Access?

(david)

 

[Dr. Know]

Dr. Know said:

Paul Overway said:

I tried EVAL() only because it was suggested earlier in this thread...

But here is the *exact code* I tried:

Module Level Code
----------------------------------------------------------------------------------
Public var1 as Single '(also tried as variant, & declared in sub code)
....
----------------------------------------------------------------------------------
Public Function GetVar(varname As String) As Variant
GetVal = Eval(varname)
End Function
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------

Form Level Code in Procedure
---------------------------------------------------------------------------------
var1 = 2057
msgbox( GetVar("var1") )
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
Crashes with "FormName can't find the name 'var1' in the expression"
----------------------------------------------------------------------------------
Something is very wrong here... I'm using the EXACT code you
presented, and what I initially tried on my own, and neither works.
Not in a MsgBox, not assigning to another variable, not in a RunSQL
command string, and not in a form text box.

?

Greg






Dr. Know

 

[Dr. Know]

david epsom dot com dot au said:

In Access, public variables are not by default in
a public collection. If you wish to refer to variables
by name, you must put them into a collection.

Therein lies the rub. I cannot seem to reference variables of ANY
scope as 'objects' in SQL query statements - I am just prompted for
the values of the variables - defined and declared or not.

Previous ideas for creating a 'universal function' for retrieving
variable values using Eval() just don't work, although separate
functions returning values for each variable do - resulting in
bloated, stupid code.

And the prospect of creating dummy box objects on all the forms and
stuffing them with variable values doesn't seem to be a very eloquent
solution to this problem - again - stupid, sloppy code.

Or you can create your own collection of named items
and refer to that.

So I guess I'm off to figure out how to do this with object
collections...

Thanks,
Greg


Dr. Know

 

[Arvin Meyer]

I think everyone may be trying to EVALuate the string when all you want to
do is pass a value, using a function, to a query. All 3 of these work:

Public Const TheString As String = "Arvin"

Public Function Foo()
Foo = TheString
End Function

Select Foo() As Whatever From Table1
------------------------------------

Public Function Bar() As String
Bar = "Arvin"
End Function

Select Bar() As Whatever From Table1

------------------------------------

Public Function FooBar(str As String) As String
FooBar = str
End Function

Select FooBar("Arvin") As Whatever From Table1
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Dr. Know]

Arvin Meyer said:

I think everyone may be trying to EVALuate the string when all you want to
do is pass a value, using a function, to a query. All 3 of these work:

Hello and Thanks for the input!

That is what I want to do, as explained in the earlier posts. The
Eval() business came up as someone's possible substitute for the old &
operator in x-Base systems...

------------------------------------
Public Const TheString As String = "Arvin"

Public Function Foo()
Foo = TheString
End Function

Select Foo() As Whatever From Table1
------------------------------------

Public Function Bar() As String
Bar = "Arvin"
End Function

Select Bar() As Whatever From Table1

These work, but each variable or constant(?!) requires it own
dedicated fuction... not very useful as a generic function to
retrieve many different variable values...

------------------------------------

Public Function FooBar(str As String) As String
FooBar = str
End Function

Select FooBar("Arvin") As Whatever From Table1

This doesn't work.
This function simply returns the name of the variable in passed to it.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

OK. As I understand it, the inline VB RunSQL command apparently
requires the use of either an Object or a Function as values for the
as EXPx placeholders. So, this is snippet of the SQL Code I want to
use the variable value in:
--------------------------------------------------------------------------------------------------------
.... WHERE (((ECNHMaster.InvoiceDate) Between
[Forms]![RebateReport]![DateRange1] And
[Forms]![RebateReport]![DateRange2]) AND
((ECNHMaster.OrderTaker) = strWebID )) ...
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
strWebID is a public string variable.
The first two wrapper functions will work, but hardcoding 35 different
routines for various variables is pretty lame...
As in this:
--------------------------------------------------------------------------------------------------------
Public Function GetWebID() as string
GetWebID = strWebID
End Function

....((ECNHMaster.OrderTaker) = GetWebID() )) ...
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------

BUT - The Generic Function apparently will never work!?

--------------------------------------------------------------------------------------------------------
Public Function GetVar(varname as string ) as string
GetVar = varname
End Function
-----
....((ECNHMaster.OrderTaker) = GetVar("strWebID") )) ...
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
This function returns the value "strWebID"

Arrgg... It CAN'T be this difficult... Or can it?

Thanks,
Greg







Dr. Know

 

[Arvin Meyer]

This doesn't work.
This function simply returns the name of the variable in passed to it.

Oh! but it does work, or I would have posted it as "untested." If you are
having a problem, you haven't built is as a Public Function in a standard
module.

-------------------------------------------------------------------------- ------------------------------
Public Function GetVar(varname as string ) as string
GetVar = varname
End Function

No it's not. But you haven't defined what strWebID is, and that's where your
code is falling down. What/where have you set or declared strWebID? Where
have you set its value or passed a value to it? From the looks of what
you've got, strWebID is a string value, not a variable.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Paul Overway]

oops....my bad....I shouldn't have paid any attention to John's post. Eval
requires a string that can be evaluated as an expression....that is not what
you are passing. If you were passing something like:

Eval("1<2")

You'd get true. Or Eval(somevariable & ">1"). Or Eval(somevariable &
"In(1,2,3,4)"). Hopefully you get the idea.

What you need is a collection of variables..and code to assign and retrieve
values for members of the collection. Something like this:

Private colVariables As New Collection

Sub AddVariable(varName As String, Value As Variant)

colVariables.Add Value, varName

End Sub

Sub RemoveVariable(varName As String)

colVariables.Remove varName

End Sub

Sub RemoveAllVariables()

Set colVariables = Nothing

End Sub

Function GetVariable(varName As String) As Variant

GetVariable = colVariables(varName)

End Function

 

[Albert D. Kallal]

I *used* to do 6502 and 80x6 ASM, C/C++ as well as Clipper stuff.

Golly, I do remember writing 6502 stuff on my AppleII!

It still doesn't solve my problem of having a variable - not a text
box or control - refusing to imbed into an SQL query without
workarounds.

Both a function, and form expression can be used as conditions in the query
builder.

So, if you have UN-bound form named "frmCoolPrompt", you can restrict the
query to a particular city by placing the following expression in the query
builder:

= forms!frmCoolPrompt!txtCity

You can also do the same with a function.

However, the best solution is NOT to imbed any kind of variables, or even
references to forms in your queries. (that gets way too messy). While you
most certainly can put a form expression in a query (that is what most
people do), I tend to avoid this.

The above approach means that your query is attached to a particular
expression, and that is not good. Further, we really want to avoid global
vars and functions all over the place also.

So, while the above idea of using a function, or a form expression works
quite fine in a query I am going to suggest something even better.

When you have a query, and you need some parameters, where is the query
being sent to?

Answer:
99% of the time, the results are needed in a form, or a report.

So, simply build the extra conditions in YOUR code and pass that to the
form, or to the report. My most favourite feature in ms-access is that both
forms, and reports have what is called a "where" clause. That where clause
is simply a valid sql "where" without the word where.

So, lets assume we have a nice form. Lets also assume we want to prompt the
user for a invoice number BEFORE we load the form (it would be silly to load
up that form with more records then need BEFORE any real work is to be done
here. So, lets prompt the user for the record we want).

strIvNum as string

strIvNum = Inputbox("What invoice number")

docmd.OpenForm "frmCustomer",,,"InvoiceNumber = " & strIvNum


Done! Using the above means that we do NOT have to use a query with
parameters, since both forms and reports have the ability to accept any
parameters "on the fly".

What this means is simply we can build a nice report prompt form, and then
pass the "where" clause as above. This means our sql is:

clean to write,
clean to look at,
can be re-used in many places since there is NO expressions that TIE sql
to variables, or even form expressions (this is the most important
feature!).
clean and free of global expressions (again somthing to advoid)

So, take a look at the following report screens:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

In the above, I might ask for a sales man, and what city. The code used in
the above screen shots is:

dim strWhere as string


if isnull(txtCity) = false then
strWhere = "(City = '" & txtCity & "')"
end if

if isnull(txtSalesMan) = false then
if strWhere <> "" then
strWhere = strWhere & " and "
endif
strWhere = strWhere & "(SalesMan = '" & txtSalesMan & "')"
end if

Note how the 2nd set of if statement is written (it appends conditions to
the strWhere). You can use that 2nd if set to add as MANY extra
prompts/conditions you want on the prompt screen.

Also, you simply build up your where clause in code very easy doing this,
and not even bother touching the sql or query that the form, or report is
based on.

 

[Albert D. Kallal]

WHERE (((ECNHMaster.InvoiceDate) Between
[Forms]![RebateReport]![DateRange1] And
[Forms]![RebateReport]![DateRange2]) AND
((ECNHMaster.OrderTaker) = strWebID ))


Dim strWhere As String

strWhere = "between " & dateRange1 & " and " & DateRange2

strWhere = BuildCriteria("InvoiceDate", dbDate, strWhere)

strWhere = strWhere & " and (OrderTaker = " & strWedID & ")"

DoCmd.OpenReport "your report", acViewPreview, , , strWhere

 

[John Vinson]

oops....my bad....I shouldn't have paid any attention to John's post.

My bad, actually! Brainfade there... Eval() is NOT useful in this
context. Sorry about that!

 

[david epsom dot com dot au]

Therein lies the rub. I cannot seem to reference variables of ANY

scope as 'objects' in SQL query statements - I am just prompted for

SELECT application.CurrentDb.properties("fred") AS Expr1,
tbl__All.strAll FROM tbl__All;


If I want global variables visible in SQL, I can add them
to the database properties, or I can put them into a table
(either as a row or column item).

And the prospect of creating dummy box objects on all the forms and
stuffing them with variable values doesn't seem to be a very eloquent
solution to this problem - again - stupid, sloppy code.

If I want "local" variables then adding them to a form is
a reasonable approach. A form, like a table, is a basic
object of an Access application, and eloquence is something
only a native speaker can judge adequately.

If I want global variables, a form is still a reasonable
approach if I want to display the values.

You do not, of course, have to add a control to a form
object in order to get a property. Access will create
a property of the form object to match any local variables
you create.

However, adding a property or item to a form is equivalent
to adding a field to a table. It is a relational change.
If the number of variables changes dynamically, it is better
to use a row item rather than a column item. Thinking about
database normalisation can give you a clue as to how the
variables should be handled.

I personally use (in this order of preference) tables,
dynamic sql, forms, and functions using named parameters
(to tables, variable lists, defined types, collections,
and searched arrays)

Your decision could be influenced by the number, nature and
use of the named variables that you have.

(david)

 

[Dr. Know]

John Vinson said:

My bad, actually! Brainfade there... Eval() is NOT useful in this
context. Sorry about that!

That's OK - Stuff Happens!

I imagine it is hard to get a handle on ALL the details of EVERY
project that passes through here! Enough to give you nightmares...

It had to go out, one way or another, so I just used the multiple
functions rather than the genetic routine. I'll work on a more
generic routine later.

I'm just used to the simple & solution in x-base for such things,
and the complexity of solving this minor problem had me flabbergasted.

And to everyone who responded, Thanks!

Greg






Dr. Know