EVal Function

[Wahab]

Hi
I want to use Eval function on bound form to add some figures , but Im
getting error, Why?
Please help me. My code is like this:

If Not IsNull(TxtTest) Then
TxtTest = Eval(TxtTest.Value)
End If
End Sub
is there any other way to make calculation like excel in that field =3+2=5
in bound form?
Thanks and regards
Wahab

 

[Dirk Goldgar]

Hi
I want to use Eval function on bound form to add some figures , but Im
getting error, Why?
Please help me. My code is like this:

If Not IsNull(TxtTest) Then
TxtTest = Eval(TxtTest.Value)
End If
End Sub
is there any other way to make calculation like excel in that field =3+2=5
in bound form?

I don't see anything wrong with that code, so long as you only enter valid
expressions or numbers in the text box. You should probably add some
error-handling to trap invalid expressions and either return an error value
of some sort, or just leave the value unchanged.

What error are you getting, and in what circumstances? In what event do you
have this code? Note that entering

=3+2

would raise an error, because the leading "=" is not understood by Access.
But entering

3+2

works for me in my test, giving the result 5. I used this test code in the
AfterUpdate event of a text box:

Private Sub Text0_AfterUpdate()

On Error Resume Next
With Me.Text0
If Not IsNull(.Value) Then
.Value = Eval(.Value)
If Err.Number <> 0 Then .Value = CVErr(5)
End If
End With

End Sub

 

[Wahab]

I don't see anything wrong with that code, so long as you only enter valid
expressions or numbers in the text box. You should probably add some
error-handling to trap invalid expressions and either return an error value
of some sort, or just leave the value unchanged.

What error are you getting, and in what circumstances? In what event do you
have this code? Note that entering

=3+2

would raise an error, because the leading "=" is not understood by Access.
But entering

3+2

works for me in my test, giving the result 5. I used this test code in the
AfterUpdate event of a text box:

Private Sub Text0_AfterUpdate()

On Error Resume Next
With Me.Text0
If Not IsNull(.Value) Then
.Value = Eval(.Value)
If Err.Number <> 0 Then .Value = CVErr(5)
End If
End With

End Sub

Thanks Mr. Drik
I want to use this code in Bound Form, Actually I have Unbound form, and sub
form is bound one, Im using this code to subform which is bound one. When
Im trying this code in unbound form it work, can you try your code to Bound
field and check please

 

[Dirk Goldgar]

I want to use this code in Bound Form, Actually I have Unbound form, and
sub
form is bound one, Im using this code to subform which is bound one.
When
Im trying this code in unbound form it work, can you try your code to
Bound
field and check please

It will work in a control that is bound to a text field, but not in a
control that is bound to a number field, because Access will automatically
reject the non-numeric value you entered before your code has a chance to
evaluate and replace it.

What you could do, to use this method and store the result, is to use an
unbound control, load the control with the field's value in the form's
Current event, and then use the control's AfterUpdate event to calculate the
result, update the control, *and* update the field. The actual number field
would have to be part of the form's recordsource, but not necessarily
displayed on the form.

Suppose your field was named "MyField", and you wanted to use the (unbound)
control "txtMyField" to display and edit it. You'd have code something like
this:

'----- start of example code -----
Private Sub Form_Current()

Me.txtMyField = Me.MyField

End Sub

Private Sub txtMyField_AfterUpdate()

On Error Resume Next

With Me.txtMyField
If Not IsNull(.Value) Then
.Value = Eval(.Value)
If Err.Number <> 0 Then
.Value = CVErr(5)
Else
Me.MyField = .Value
End If
End If
End With

End Sub
'----- end of example code -----

Note that the unbound control txtMyField isn't going to "undo" the same way
a bound control would. This may or may not be a problem. If you need to
make it "undo" along with the bound controls on the form, you'll have to
write your own mechanism for saving the original value, recognizing the Undo
event, and restoring the original value.

 

[Wahab]

It will work in a control that is bound to a text field, but not in a
control that is bound to a number field, because Access will automatically
reject the non-numeric value you entered before your code has a chance to
evaluate and replace it.

What you could do, to use this method and store the result, is to use an
unbound control, load the control with the field's value in the form's
Current event, and then use the control's AfterUpdate event to calculate the
result, update the control, *and* update the field. The actual number field
would have to be part of the form's recordsource, but not necessarily
displayed on the form.

Suppose your field was named "MyField", and you wanted to use the (unbound)
control "txtMyField" to display and edit it. You'd have code something like
this:

'----- start of example code -----
Private Sub Form_Current()

Me.txtMyField = Me.MyField

End Sub

Private Sub txtMyField_AfterUpdate()

On Error Resume Next

With Me.txtMyField
If Not IsNull(.Value) Then
.Value = Eval(.Value)
If Err.Number <> 0 Then
.Value = CVErr(5)
Else
Me.MyField = .Value
End If
End If
End With

End Sub
'----- end of example code -----

Note that the unbound control txtMyField isn't going to "undo" the same way
a bound control would. This may or may not be a problem. If you need to
make it "undo" along with the bound controls on the form, you'll have to
write your own mechanism for saving the original value, recognizing the Undo
event, and restoring the original value.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thank you very much Mr. Dirk , its working properly.