causing a control to update as working on the form

[Guest]

i have a 3 simple tables, one which stores transaction information, the other
2 are simply for the type of transaction and the categories that each
transaction is. the main table called "trasactions" is the basis for a form
called "transactions".

in the form there is a spot to type the subtotal of a reciept or invoice,
then the user is required to type in the GST and PST as well. a calculated
text box control is at the bottom of the form and it simply adds up the other
3 boxes.

the problem is that this calculated box isnt updating as the user types in
the other three. is there a way to cause the thing to recalculate everytime
the user updates one of the other three fields? i have tried setting the
event handlers that i thought might do what it want on the form and the
fields and had them call upon a macro to cause it to requery. nothing seemed
to do the trick. at this point i am pretty sure i have to create some special
coding, but i have no idea where to start.

so if anyone can make it really plain where to put it, and what to put in it
would be appreciated. i have no attempted coding to share as i am really new
to programming.

just so you know the fields are: [Subtotal] [GST] [PST] and the calculated
field is called total and contains =[Subtotal] + [GST] + [PST].

 

[Bob Hairgrove]

i have a 3 simple tables, one which stores transaction information, the other
2 are simply for the type of transaction and the categories that each
transaction is. the main table called "trasactions" is the basis for a form
called "transactions".

in the form there is a spot to type the subtotal of a reciept or invoice,
then the user is required to type in the GST and PST as well. a calculated
text box control is at the bottom of the form and it simply adds up the other
3 boxes.

the problem is that this calculated box isnt updating as the user types in
the other three. is there a way to cause the thing to recalculate everytime
the user updates one of the other three fields? i have tried setting the
event handlers that i thought might do what it want on the form and the
fields and had them call upon a macro to cause it to requery. nothing seemed
to do the trick. at this point i am pretty sure i have to create some special
coding, but i have no idea where to start.

Don't use a macro, use VBA code.

Use the AfterUpdate event of each of the three controls. You can
create one function for all three; instead of writing "[Event
Procedure]" in the properties list for OnAfterUpdate, you can write
"=MyFunc()" assuming that MyFunc is the name of your function, which
should look like this one-liner:

Private Function MyFunc()
Me.Recalc
End Function

so if anyone can make it really plain where to put it, and what to put in it
would be appreciated. i have no attempted coding to share as i am really new
to programming.

just so you know the fields are: [Subtotal] [GST] [PST] and the calculated
field is called total and contains =[Subtotal] + [GST] + [PST].

 

[Guest]

Hello

and thanks! this is a great help! and a great place to learn! WooHoo!

 

[Guest]

Hello

well i thought i had it. there is one slight problem.

the calculated field is just after the PST one. if i type in the PST one and
hit tab or enter the form moves to the next record. i need this thing to
update before then so the data entry person can see the change before moving
on to the next reciept.

should i use the after key press handler instead? suggestions?

 

[Guest]

one other question...

is there a way to calculate a default value? in the GST and PST fields it
would be nice if i could get it to put in the amount that should be there
based on the Subtotal. it would be a simple calculation. for instance if i
was to put in a default calculation for the GST i would put =[Subtotal]*.07.
i tried this by putting it right in the default value property of the field
in the table, but it choked on it. is there a different way to do this?

 

[Bob Hairgrove]

Hello

well i thought i had it. there is one slight problem.

the calculated field is just after the PST one. if i type in the PST one and
hit tab or enter the form moves to the next record. i need this thing to
update before then so the data entry person can see the change before moving
on to the next reciept.

should i use the after key press handler instead? suggestions?

Looks like the calculated field has Enabled set to False. Of course,
you can't edit the value, but if you set Enabled=True and Locked
=True, it can still receive the focus. When you leave the PST field,
you will stay on the current record until hitting Enter, Tab or
PageDown again, but the focus will be in the calculated field. You can
run some code from the OnGotFocus or OnEnter events of that control if
you need to.

Setting calculated controls Enabled=True and Locked=True has the
additional advantage that you can copy & paste the control contents
via the clipboard to another application.

Another way would be to use the BeforeUpdate eventof the form to
validate user input or display a message box with the calculated
value. This will run after the control's AfterUpdate event has
finished, but before moving to the next record.

Yet another way would be to set focus to the first field either by
using the SetFocus method in code of the PST field's OnExit or
OnLostFocus event, or by changing the form's Cycle property to 1 (i.e.
current record). However, you won't be able to use the Tab key to
navigate records anymore -- use PageUp and PageDown instead.

 

[Bob Hairgrove]

one other question...

is there a way to calculate a default value? in the GST and PST fields it
would be nice if i could get it to put in the amount that should be there
based on the Subtotal. it would be a simple calculation. for instance if i
was to put in a default calculation for the GST i would put =[Subtotal]*.07.
i tried this by putting it right in the default value property of the field
in the table, but it choked on it. is there a different way to do this?

I'm not sure ... is [Subtotal] your calculated field? If so, you have
to make sure that it doesn't cascade (i.e. endless recursive updates).

 

[Guest]

Hello

no. subtotal is a place where they enter the amount of the subtotal of the
reciept or invoice. then the GST and PST are usually 7% of that and the
calculated total field is where the other 3 are added up.

by the way, thanks, the total field is working great now!

the trick is that i would like to have the GST and PST automatically
calculate what they should be, but be changeable by the user. this seems like
a default value to me, but a calculated one. i tried putting a formula
(=[Subtotal]*.07) in the default spot for the GST and PST. the database
choked on it.

Thanks for your help!

one other question...

is there a way to calculate a default value? in the GST and PST fields it
would be nice if i could get it to put in the amount that should be there
based on the Subtotal. it would be a simple calculation. for instance if i
was to put in a default calculation for the GST i would put =[Subtotal]*.07.
i tried this by putting it right in the default value property of the field
in the table, but it choked on it. is there a different way to do this?

I'm not sure ... is [Subtotal] your calculated field? If so, you have
to make sure that it doesn't cascade (i.e. endless recursive updates).