Using a form's text box as an adding machine

[Malcolm P]

I have a form, Expenses, which updates a table, Expenses.
On the form is a text box which updates the table's field
ExpAmt. Currently, using the form, when I input to this
text box I can only enter a single number: ie, 12.50.
I would like to be able to enter a series of numbers into
the text box and have Access act as an adding machine by
totaling the numbers entered and using the sum as the
number which updates the table.

Futher Explaination:
On the form I have a one textbox into which I input an
amount. This amount that I input comes from pieces of
paper that I am given. These pieces of paper sometimes
have two or more amounts on them that I have to add on an
adding machine: ie, 1.25 + 10.00 + .25 + 1.00 = 12.50. I
then take the total, 12.50, and input into the form. What
I want the form's textbox to do is have it function as
the adding machine, so that I can skip using the adding
machine. I want to be able to click onto the textbox and
enter the following keystrokes 1.25 + 10.00 + .25 + 1.00
{enter}, have it total the series of numbers and give me a
sum which it then uses as the entry and updates the
related table.

EXAMPLE of other programs that do the above:
I use an accounting software package called QuickBooks
(which is prehaps one of the most used small business
accounting programs in the country). When I input to
QuickBooks it allows me to do what I am trying to do in
Access. QuickBooks lets me do addtion, subtraction,
multiplication and divison.

Can I get Access 2002 to do what QuickBooks does?

 

[Allen Browne]

Place an unbound text box on the form, where the user can enter the
calculation.

In its AfterUpdate event, enter:
Me.MyField = Eval(Me.txtCalc)
where "MyField" is the name of the textbox where you want to see the result,
and txtCalc is the name of the text box where the calculation is entered.

 

[Malcolm P]

That works!!!!
Now, however, this causes other considerations:
1> the TxtCalc text box displays the input (eg:
12.50+2.00+1.25)after the cursor leaves that box. I would
want this to erase after it updates MyField.
2> the Form now has an extra text box, TxtCalc. I do not
want the user to see two text boxes (TxtCalc and MyField).
The user should only see one text box to the right of the
label, "enter expense amount", which shows the input to
TxtCalc and the update number to MyField as though it is
just one text box.

 

[Allen Browne]

To erase the entry box:
Me.txtCalc = Null

You can set the ControlSource of the MyField box to the name of your field,
and set its Visible property to No so the user does not see it if you wish.

Alternatively:
1. Set the Tab Stop property of MyField to No.

2. Set txtCalc to the same tab order spot as the field (View | Tab Order).

3. Place txtCalc behind MyField (Format | Send to back).

4. In the Got Focus event procedure of MyField:
Me.txtCalc.SetFocus

5. In the Got Focus event of txtCalc:
Me.txtCalc = Me.MyField

 

[Malcolm P]

I setup the "Altenatively".
Visually it works. When I type in a series of numbers it
does what I wanted: does the calculation, shows the
result and the result updates the related table.
Problems encountered:
1> When I enter the text box, following the tab
order, I get an error message, "Micosoft Access can't find
the macro 'Me.'. I just click OK and then it allows me to
finish entering.
2> After finishing entering a record, when I go to
the next record the TxtCalc entry from the previous record
is still visible. You suggested Me.TxtCalc = Null but I
do not know where to put this.
3> If I exit the text box without making an entry, I
get a Microsoft Visual Basic error message, "Run-time
error '94': Invalid use of Null". When I click onto
the Debug button it opens a code page and Me.ExpAmt = Eval
(Me.TxtCalc) is highligthed.

 

[Allen Browne]

Hi Malcom

The idea was to set the On Got Focus property to:
[Event Procedure]
Then click the Build button beside that to open the code window.
Place the code in there.
That should handle the message about "Can't find macro Me".

 

[Malcolm P]

Thanks for seeing that one through.
It works great!!!!!!!!!!!!!

I'll be posting another question. Hope to get your input
again.