• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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
0
summerset
Asked:
summerset
  • 5
  • 4
  • 2
1 Solution
 
kretzschmarCommented:
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
 
kretzschmarCommented:
appendix,

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

meikl
0
 
summersetAuthor 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
kretzschmarCommented:
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
 
kretzschmarCommented:
appendix,
instead of asFloat you can also
use AsCurrency for your total field
meikl
0
 
summersetAuthor Commented:
Thanks, kretzschmar !

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

-S
0
 
synatureCommented:
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
 
kretzschmarCommented:
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
 
summersetAuthor 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
 
synatureCommented:
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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now