Calculation/expression stored in a field

  • Thread starter Thread starter stefania nj
  • Start date Start date
S

stefania nj

Hi,
I do not know if this is doable, but I hope so.
I store the calculation expression that needs to be performed between
texboxes in a form in a table field.
for example
frm.T6 +frm.T8

I would like to grab that expression
and do the following
vCalc=frm.T6 +frm.T8

But I get the string and not the calculation. How can I grab that
expression and actually have the expression work like it was a line of
code?

Thanks for all your help.

Stefania
 
Hi,
I do not know if this is doable, but I hope so.
I store the calculation expression that needs to be performed between
texboxes in a form in a table field.
for example
frm.T6 +frm.T8

I would like to grab that expression
and do the following
vCalc=frm.T6 +frm.T8

But I get the string and not the calculation. How can I grab that
expression and actually have the expression work like it was a line of
code?

Thanks for all your help.

Stefania
Click to expand...

Try using the Eval() function: if txtMyExpression contains

"frm.T6 + frm.T8"

then

vCalc = Eval(txtMyExpression)

should do the calculation, if the pieces for it are available.
--

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
 
Try using the Eval() function: if txtMyExpression  contains

"frm.T6 + frm.T8"

then

vCalc = Eval(txtMyExpression)

should do the calculation, if the pieces for it are available.
--

             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 alsohttp://www.utteraccess.com
Click to expand...

John,
Thank you for your reply.
It still does not work. I get a run time error 2482 can't find 'frm'
But if I write the line of code it works perfectly.
Thanks again.
Stefania
 
John,
Thank you for your reply.
It still does not work. I get a run time error 2482 can't find 'frm'
But if I write the line of code it works perfectly.
Thanks again.
Stefania
Click to expand...
John,
It works if the expression is the following:
Val(forms!frmQAAdmin!T6) +val(forms!frmQAAdmin!T7)

If that is what I need to do that is what I'll do.

Thank you for your assistance.

Stefania
 
John,
It works if the expression is the following:
Val(forms!frmQAAdmin!T6) +val(forms!frmQAAdmin!T7)

If that is what I need to do that is what I'll do.
Click to expand...

What's the context? Where are you trying to use this?

A Query will not have any reference to a "frm" object, even if it's been set
in code.

Just the NEED to do this suggests that your table design might not be properly
normalized: what are T6 and T7 and so on? Might you be "storing data in
fieldnames"?
--

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
 
What's the context? Where are you trying to use this?

A Query will not have any reference to a "frm" object, even if it's been set
in code.

Just the NEED to do this suggests that your table design might not be properly
normalized: what are T6 and T7 and so on? Might you be "storing data in
fieldnames"?
--

             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 alsohttp://www.utteraccess.com
Click to expand...

Hi John,
T1, T2 etc. are the names of the textboxes on the form and they are
not names of field on a table. The form is unbound.
I call a function in the enter event of a textbox:
This is the function:
Public Function performCalculation() As Boolean
Dim strCtlname As String
Dim i As Integer
Dim sql As String
Dim vCalc
Dim sngCalc As Long
Dim rst As DAO.Recordset
Dim frm As Form
On Error GoTo Err_performCalculation

Set frm = forms!frmQAAdmin
'get control name and order
strCtlname = frm.ActiveControl.Name
i = Right(strCtlname, Len(strCtlname) - 1)
'check if control has a calculation
sql = "SELECT FIELD_CALCULATION FROM TMP_ADMIN WHERE
FORM_ADMIN_ITEM_ORDER_ID = " & i & ";"
Set rst = CurrentDb.OpenRecordset(sql)
With rst
.MoveFirst
If Not IsNull(!FIELD_CALCULATION) Then


vCalc = Eval(!FIELD_CALCULATION)
frm.ActiveControl.value = vCalc
End If
End With
performCalculation = True
Exit_performCalculation:
performCalculation = False
Exit Function

Err_performCalculation:
Select Case Err.Number
Case 11
MsgBox "Division by zero is not allowed. Please Enter all
correct required amounts for calculation!"
strCtlname = "T" & i - 1
If frm.Controls(strCtlname).Visible = True Then
frm.Controls(strCtlname).SetFocus
Else
strCtlname = "C" & i - 1
frm.Controls(strCtlname).SetFocus
End If
Resume Exit_performCalculation

Case 13
MsgBox "Please Enter all Field required for calculation!"
strCtlname = "T" & i - 1
If frm.Controls(strCtlname).Visible = True Then
frm.Controls(strCtlname).SetFocus
Else
strCtlname = "C" & i - 1
frm.Controls(strCtlname).SetFocus
End If
Resume Exit_performCalculation
Case Else
DoCmd.Echo True
Call LogError(Err.Number, Err.Description,
"performCalculation of Module modQA")
Resume Exit_performCalculation
'MsgBox "Error " & Err.Number & " (" & Err.Description & ")
in procedure performCalculation of Module modQA"
End Select
End Function
 
What's the context? Where are you trying to use this?

A Query will not have any reference to a "frm" object, even if it's been set
in code.

Just the NEED to do this suggests that your table design might not be properly
normalized: what are T6 and T7 and so on? Might you be "storing data in
fieldnames"?
--

             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 alsohttp://www.utteraccess.com
Click to expand...

Hi John,
I am using an unbound form "frmQAAdmin" which has 15 textboxes that
are visible only if available for a particular selection.
With in that selection there could be calculations that I need to
perform using the value in the textboxes

This is the function called on the enter Event of each textbox that is
called:

Public Function performCalculation() As Boolean
Dim strCtlname As String
Dim i As Integer
Dim sql As String
Dim vCalc
Dim sngCalc As Long
Dim rst As DAO.Recordset
Dim frm As Form
On Error GoTo Err_performCalculation

Set frm = forms!frmQAAdmin
'get control name and order
strCtlname = frm.ActiveControl.Name
i = Right(strCtlname, Len(strCtlname) - 1)
'check if control has a calculation
sql = "SELECT FIELD_CALCULATION FROM TMP_ADMIN WHERE
FORM_ADMIN_ITEM_ORDER_ID = " & i & ";"
Set rst = CurrentDb.OpenRecordset(sql)
With rst
if .recordcount>0 then
.MoveFirst
If Not IsNull(!FIELD_CALCULATION) Then


vCalc = Eval(!FIELD_CALCULATION)
frm.ActiveControl.value = vCalc
End If
End if
End With
performCalculation = True
Exit_performCalculation:
performCalculation = False
Exit Function

Err_performCalculation:
Select Case Err.Number
Case 11
MsgBox "Devision by zero is not allowed. Please Enter all
correct required amounts for calculation!"
strCtlname = "T" & i - 1
If frm.Controls(strCtlname).Visible = True Then
frm.Controls(strCtlname).SetFocus
Else
strCtlname = "C" & i - 1
frm.Controls(strCtlname).SetFocus
End If
Resume Exit_performCalculation

Case 13
MsgBox "Please Enter all Field required for calculation!"
strCtlname = "T" & i - 1
If frm.Controls(strCtlname).Visible = True Then
frm.Controls(strCtlname).SetFocus
Else
strCtlname = "C" & i - 1
frm.Controls(strCtlname).SetFocus
End If
Resume Exit_performCalculation
Case Else
DoCmd.Echo True
Call LogError(Err.Number, Err.Description,
"performCalculation of Module modQA")
Resume Exit_performCalculation
'MsgBox "Error " & Err.Number & " (" & Err.Description & ")
in procedure performCalculation of Module modQA"
End Select
End Function
Any way your suggestion of the Eval function saved me.

Thanks again,
Stefania
 
John;

Storing calculated values was a new feature introduced in Access 2010,
right?

ANd it's been a key part of SQL Server for the past 11 years.

No wonder you don't know anything about it.. MVPs are systematically
biased against REAL DATABASES

-Aaron
 
Back
Top