Execute code from string

[Georgios Liakopoulos]

Hi,
I wish to create a form which will allow the user to enter a function
which will use fields (parameters) from a table to calculate the values
of another field. Since the parameters and the function itself is not
constant, I want to 'translate' the input formula to code. So, here is
an example:
Parameters: parA, parB, parC, etc.
User enters function: strCalc = "parD = (parA + parB) / parC"
I can make a DAO recordset (rst) of the table and also convert the
string to look like: strCalc = "rst!parD = (rst!parA + rst!parB) /
rst!parC".
The problem is: how will I execute this code?
I've tried:
Eval(strCalc)
but produces an error: 'Runtime error 2423: The expression you entered
has an invalid .(dot) or ! operator or invalid parentheses'
The same error is produced even with simpler functions like strCalc =
"rst!parD = rst!parA".

Thanks for any help

 

[Access Developer]

There are multiple ways to accomplish what you want, some simpler than
others... the built-in "Eval" function is the easiest. What it does... let
me emphasize... is NOT "execute code" but "evaluate expressions". From your
description, it would seem that you created an invalid expression which it
could not evaluate.

Rather than launch off on another approach, I'd suggest you need to pursue
the specifics and try to determine what you need to do to make that a valid
expression.

If you want additional help, you'll need to capture the exact content that
you gave the Eval function and post it here. Maybe someone will be able to
offer a useful suggestion. It wouldn't _hurt_ to tell us what version of
Access and Windows you are using, as well.

But, if you want the user to have the full power of Access and VBA, you
should give them just that, not try to "protect them from themselves".

 

[Georgios Liakopoulos]

Hello,
I've tried to give a simple description - therefore I did not posted the
actual code not system details (actually its Access 2007 on Windows 7
64-bit). I think that what's wrong is the (!) characters. Instead of
using the
strCalc = "rst!parD = (rst!parA + rst!parB) / rst!parC"
I will pass the values of the recordset fields to common variables like
parAval, parBval, etc.
Thank you for your reply and the very good description of the Eval function.
Giorgos

 

[Georgios Liakopoulos]

Here it is:

....
For m = 1 To fieldnum 'fieldnum = 2
fieldval(m) = rst(fieldname(m)) 'I've got the values of fields(1) and (2)
Next m
Debug.Print calculation >>>>>>>>>>> Prints: fieldval(1) + fieldval(2)
Debug.Print fieldval(1) >>>>>>>>>>> Prints: 138
Debug.Print fieldval(2) >>>>>>>>>>> Prints: 45

'So, I want to pass the calculation formula to calculate the sum of 138
and 45 and store it to field(0)
rst.Edit
rsinstance(fieldname(0)) = Eval(calculation)
rst.Update
....

Code stops one line before the end with error: Run-time '2425': The
expression you entered has a function name that db1 can't find

I thing that the Eval function does not accept user-defined variables.
Is this a possible reason? How can I overcome this?

Thanks again

 

[John W. Vinson]

I've tried to give a simple description - therefore I did not posted the
actual code not system details (actually its Access 2007 on Windows 7
64-bit). I think that what's wrong is the (!) characters. Instead of
using the
strCalc = "rst!parD = (rst!parA + rst!parB) / rst!parC"
I will pass the values of the recordset fields to common variables like
parAval, parBval, etc.

Try using rst.Fields("ParA") instead to see if avoiding the ! delimiter helps.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Gloops]

Georgios Liakopoulos wrote, on 20th March 2012 10:59 UTC + 1 :

rsinstance(fieldname(0)) = Eval(calculation)
rst.Update
...

Code stops one line before the end with error: Run-time '2425': The
expression you entered has a function name that db1 can't find

Could you show us the declaration of rsinstance ?

Can it be imagined that Eval must be prefixed with the good reference ?
This would be Application.Eval.
I only propose this because I migrated an Access 95 base to Access 97,
and such prefixes were necessary in that context.

To come back to the general case, I typed this :

calculation = "now() + 1"
? Eval(calculation)

and this displayed the date of tomorrow.

Does it answer your question about Eval accepting user-defined variables ?

 

[Georgios Liakopoulos]

Well, its been some days since I last tried this...Thank you all for
your answers!!

I solved this issue by replacing the variable name with the actual value:

---------------
For m = 1 To factors - 1
If IsNull(rsinstance(fieldname(m))) Then
GoTo Bypass_calculation
End If
fieldval(m) = rsinstance(fieldname(m))
Next m
.Edit
calc(1) = Replace(calculation, "fieldval(1)", fieldval(1))
For n = 2 To factors - 1
calc(n) = Replace(calc(n - 1), "fieldval(" & n & ")", fieldval(n))
Next n
rsinstance(fieldname(0)) = Eval(calc(n - 1))
.Update
---------------------

By the way, here is the declaration for the rst:
Dim rsinstance, rswhichmeta_id As DAO.Recordset

Thans again

 

[Gloops]

Georgios Liakopoulos wrote, on 26th March 2012 14:17 UTC + 1 :

By the way, here is the declaration for the rst:
Dim rsinstance, rswhichmeta_id As DAO.Recordset

I have an hesitation about this syntax as opposed to

Dim RsInstance As DAO.Recordset, RsWhichmeta_id As DAO.Recordset

One classical question about the first declaration : is rsinstance a
recordset or a variant ? I am not quite sure about the answer.

You could have a surprise by typing this between declaration and
initialization :
Debug.Print "Var1 : " & TypeName(Var1)
Debug.Print "Var2 : " & TypeName(Var2)

 

[Gloops]

Georgios Liakopoulos wrote, on 26th March 2012 14:17 UTC + 1 :

calc(n) = Replace(calc(n - 1), "fieldval(" & n & ")", fieldval(n))

I have to admit I should not have had the idea to write that.
It seems you did something powerful.
Feel free to explain how it runs, if you like, that will allow some
other people to use what you did.

Or maybe I understand better next week

 

[Douglas J Steele]

No question about it: VBA does not allow "short circuiting" in declarations.

rsInstance is declared as a Variant. Only rswhichmeta_id will be a DAO
recordset.

"Gloops" wrote in message
Georgios Liakopoulos wrote, on 26th March 2012 14:17 UTC + 1 :

By the way, here is the declaration for the rst:
Dim rsinstance, rswhichmeta_id As DAO.Recordset

I have an hesitation about this syntax as opposed to

Dim RsInstance As DAO.Recordset, RsWhichmeta_id As DAO.Recordset

One classical question about the first declaration : is rsinstance a
recordset or a variant ? I am not quite sure about the answer.

You could have a surprise by typing this between declaration and
initialization :
Debug.Print "Var1 : " & TypeName(Var1)
Debug.Print "Var2 : " & TypeName(Var2)