Textbox Calculator

[Randy]

This might not be possible but I would like to be able to add, multiply etc. within a standard access textbox. If anyone knows where to find more information on this subject I would greatly appreceiate pointing me in that direction.

This function would be similar to QuickBooks textbox caculator.



Thank you,
Randy

[MrFormula]

I found this calculator and wanted to share it with you.

I found the original code at this location:
http://it.toolbox.com/wiki/index.php/Add_a..._Access_TextBox

I have cleaned it up... I Just put the text on the different lines so that Access could read it and I added the Enter function to use the enter key to do the calculation.

Private Sub Qty_KeyDown(KeyCode As Integer, Shift As Integer)

'This is a sample calculator that works within your textbox fields.
'r.l. hane 2006.01.25.
'To use this little calculator, set the form's KeyPreview property to
'Yes and paste this handler into the form's OnKeyDown code.
'This keydown handler responds to the "=" key. If the '=' key
'is pressed in a textbox field that has a DecimalPlaces
'property other than "auto", it attempts to use the Eval
'function to evaluate the math formula typed there. If
'successful it replaces the math formula with the value.
'For example, If you type '2+3*5' into a numeric textbox and
'press '='. The value '17' will be put in it's place. Notice
'that Eval correctly respects the order of operation rules.
'Since it will evaluate any expression, even ones containing
'VBA functions, keep the app in the sandbox or alter it to
'ignore text containing parenthesis characters.

Dim varNewValue As Variant
Dim strErr As String
On Error GoTo SkipOut

' to use the '=' use this line
'If KeyCode = 187 And Shift = 0 Then

'To use the Enter Key (my favorite) use this line
If KeyCode = 13 Then

If Me.ActiveControl.ControlType = acTextBox Then
If Me.ActiveControl.DecimalPlaces <> 255 Then
varNewValue = Eval(Me.ActiveControl.Text)
Me.ActiveControl.Text = varNewValue
KeyCode = 0
End If
End If
End If
Exit Sub
SkipOut: strErr = Error(Err)
On Error Resume Next
KeyCode = 0
MsgBox Left(strErr, InStr(strErr & "@", "@") - 1)
Exit Sub

End Sub

[Randy]

Thank you so much for finding and sharing this code. It is exactly what I have been looking for. I will get back to you and let you know how it works.

Randy

[delaikhi]

Thank you so much for sharing, but I have a couple of questions:
1- The Field is formated as Text, than how we make it display the results in dollar term?
2- Is there any way that after getting the result, one would press Enter key and it will move to the next field? That would be more convenient. Thanks

[Randy]

The fields in the table can be number fields formatted to integer, single or currency etc. with decimal set to 2. When you place the fields on your form remember to set the form's KeyPreview property to Yes and paste the code from above in to the OnKeyDown code Event.
NOTE*** copy only the text between Privet Sub and End Sub and paste it in the generated Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer). If you use the full code from above it won't work.
To make it move to the next textbox on your form after pressing the enter key to finish the calculation change the line KeyCode = 0 to KeyCode = 9. That should do it.

This will work on every text field on your from. To use it like the above example you will need to paste the code in each textbox's OnKeyDown event.
Hope this helps.

[delaikhi]

Yeah I tried as you said, everything seems OK but when I entered something with no calculation the is a dialogue popping up: "your entered expression has invalid syntax?" So, why it is and how to fix this thing?

[Randy]

Could you post your KeyDown code. My sample database will allow a single number without calculations.

[delaikhi]

Yes, this is the code:
Private Sub Order_Amount_KeyDown(KeyCode As Integer, Shift As Integer)


'This is a sample calculator that works within your textbox fields.
'r.l. hane 2006.01.25.
'To use this little calculator, set the form's KeyPreview property to
'Yes and paste this handler into the form's OnKeyDown code.
'This keydown handler responds to the "=" key. If the '=' key
'is pressed in a textbox field that has a DecimalPlaces
'property other than "auto", it attempts to use the Eval
'function to evaluate the math formula typed there. If
'successful it replaces the math formula with the value.
'For example, If you type '2+3*5' into a numeric textbox and
'press '='. The value '17' will be put in it's place. Notice
'that Eval correctly respects the order of operation rules.
'Since it will evaluate any expression, even ones containing
'VBA functions, keep the app in the sandbox or alter it to
'ignore text containing parenthesis characters.


Dim varNewValue As Variant
Dim strErr As String
On Error GoTo SkipOut


' to use the '=' use this line
'If KeyCode = 187 And Shift = 0 Then


'To use the Enter Key (my favorite) use this line
If KeyCode = 13 Then


If Me.ActiveControl.ControlType = acTextBox Then
If Me.ActiveControl.DecimalPlaces <> 255 Then
varNewValue = Eval(Me.ActiveControl.Text)
Me.ActiveControl.Text = varNewValue
KeyCode = 0
End If
End If
End If
Exit Sub
SkipOut: strErr = Error(Err)
On Error Resume Next
KeyCode = 9
MsgBox Left(strErr, InStr(strErr & "@", "@") - 1)
Exit Sub
End Sub

[Randy]

Where your code says KeyCode = 0 change it to KeyCode = 9
Where your code says KeyCode = 9 change it to KeyCode = 0

This should solve your problem... it works perfectly for me.

[Goyooper]

Is there a problem using this in Access 2016?
I have done set the KeyPreview to yes and the OnKeyDown in the Text0 textbox and get the following error. Can someone help

[Micron]

You realize this is an 8 year old thread that was never closed, thus potentially never solved? Consider starting your own, along with information on what you're trying to do (and not just what doesn't work) for a more focused solution.