# Calculate Total Amount in Live Paradox \$ Fields?

Hello!

I have a form which is connected to a table. On the form I have dbeditboxes for the Unit Price, Tax, Qty, and Total Due. The Unit Price and Tax are \$Money fields. I need to automatically create a total with

(Unit Price * Qty) + Tax

whenever the user makes a change in the form, and fill the Total dbeditbox with the result. However, I do not have a great deal of experience working with live datasets and am having difficulting creating the total. I have tried many methods as well as OnCalcFields.

I am using Delphi 5 and the BDE with paradox tables.

Any help or direction or help on these matters would be greatly appreciated.

Thanking you in advance...

-Summerset
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
hi -summerset,

you can fire a second query in the afterpost-event, which calsulates your total like

procedure TForm1.Query1AfterPost(DataSet: TDataSet);
begin
query2.Close;
query2.Sql.Text := 'select sum((Unit Price * Qty) + Tax) as total from thetable where ID = YourCurrentID';
query2.Open;
EditTotal := query2.FieldByName('Total').AsString;  //maybe a formatting is to do here
end;

hope this helps

meikl

0
Commented:
appendix,

not tested on typos,
maybe you should it also fire in the afterdelete-event

meikl
0
Author Commented:
Hi kretzschmar !

Thank you for the quick reply. I see what you are saying, I did not do a good job f explaining everything. The form is actually being used to input a new record in the table. All of the dbeditboxes are blank -- the form is being opened and I call:

DBNavigator.BtnClick(nbInsert)

When all the dbeditboxes are filled in, I have a Post Button which calls:

DBNavigator.BtnClick(nbPost)

and all the values in the dbeditboxes are written.

As the user fills in the Unit Price, Qty and Tax, I want to update the dbeditbox that holds the Total so that when they press the Update button that calls: DBNavigator.BtnClick(nbPost) the value will be posted.

If I use the dbedit.text value, it seems unstable and revers back to \$0.00 when the box is tabbed or clicked.

Hope that makes sense. Should I use a sparate form with textboxes to do this type of input? So it is not live? It was a matter of convenience to use the same form for editing, and adding to the table.

Thanks again!

-S
0
Commented:
hi summerset,
you mean your record, which will be edited, has a field which should be calculated by the three other fields, right?

well two solutions i guess:

the easier one, the result is seen after the post event, by using the before-post event

procedure TForm1.Table1BeforePost(DataSet: TDataSet);
begin
Table1.FieldBYName('Total').AsFloat :=
(Table1.FieldBYName('UnitPrice').AsFloat *
Table1.FieldBYName('Quantity').AsFloat) +
Table1.FieldBYName('Tax').AsFloat;
end;

the second is to assign to eachfield (UnitPrice,Qty,Tax) an OnExit-event like, which will calculate the Total by leaving a dbedit-field

procedure TForm1.DBEdit1Exit(Sender: TObject);
begin
Table1.FieldBYName('Total').AsFloat :=
(Table1.FieldBYName('UnitPrice').AsFloat *
Table1.FieldBYName('Quantity').AsFloat) +
Table1.FieldBYName('Tax').AsFloat;
end;

Remark
you can assign all fields to this one procedure,
you can combine this two ways

hope i've now understood your q right and
that this helps

meikl
0
Commented:
appendix,
instead of asFloat you can also
use AsCurrency for your total field
meikl
0
Author Commented:
Thanks, kretzschmar !

-S
0
Author Commented:
Oh -- please use the "answer question" with text below so I can give you the points.

-S
0
Commented:
This is one of the few places where I'd not use db-aware components.  I'd use edit boxes with all the on change events pointing to the same handler which computes your value.  Be sure to check for blank .text properties and substitute zero as needed.  Then, a bit of coding with the post button to actually do the post when the user is happy with the input.

db-aware is great, up to point, and, although kretzschmar's comment is also a valid answer, I think you will find it might cause other side effects.  If you are under an edict to use data-aware, then you may need to keep constantly aware of the current datasource's state.  I did do something similar a while back, in D2, and dimly recall having quite a time of it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
hi summerset,

? can't answer because another has answered and you've accepted this :-(

well, one point is correct by synature
you must ckeck the state, if you use the exit-event.

procedure TForm1.DBEdit1Exit(Sender: TObject);
begin
if (Table1.State = dsEdit) or
(Table1.State = dsInsert) then
Table1.FieldBYName('Total').AsFloat :=
(Table1.FieldBYName('UnitPrice').AsFloat *
Table1.FieldBYName('Quantity').AsFloat) +
Table1.FieldBYName('Tax').AsFloat;
end;

to sysnature,
i guess you've stolen me some points, with an answer, which says nothing new. for future use, please comment first and let the questioner decide

meikl
0
Author Commented:
Sorry meikl!!!

Was in a hurry and thought it was you. I will post a new question called:

"For kretzschmar"

It will be worth 200 points. Please answer it, and I will make sure you get the points!

-S
0
Commented:
Sorry guys, I didn't realize that points were so important.  A question was asked that I thought I could provide an answer for, didn't know everyone would get upset if I didn't call it a comment.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.