INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Sum total in a text field on a form from linked child table records
2

Sum total in a text field on a form from linked child table records

Sum total in a text field on a form from linked child table records

(OP)
Hello

I have a form with two tables, the child being ORDERS that is linked by ChildOrder=urnorder and RelationalExpr=urnorder. Linked order item have a unique number (example 550) in the urnorder N(10) field. This works as expected.

I did find this link: http://www.tek-tips.com/viewthread.cfm?qid=1729040 but I've been able to adapt it.

I am aware of COUNT TO and also tried SCAN...ENDSCAN adding up the totals but as there 10's of thousands of linked orders this is taking some considerable time. I'm also aware that with reports you just change the field on the report to sum.

thisform.text1.value doesn't give any information about how to store totals from table in this scenario.

My question is, how can I sum total in a text field on a form from linked child table records from a field called TOTAL N(8,3)?

Any pointers would be appreciated.

Thank you

Steve Williams

RE: Sum total in a text field on a form from linked child table records

You'll have to hook the code in whatever changes to make you need to recalculate. I'll guess that the parent table is Customers or something like that, so you need to do the calculation when the record pointer moves in Customers. Since you don't say how you're showing that table, it's hard to know where that is, but regardless, I'd create a custom method to do the calculation and call it form anything that moves that record pointer.

As for how to do the calculation, SUM TO or SELECT SUM(xxx) should both be fast as long as you have the right index tag on Orders. Based on what you've said, you need a tag on URNOrder.

As for where to put the total, you can either simply push it into the relevant textbox:

CODE

ThisForm.Text1.Value = m.nMyTotal 

or you can store it in a form property (say, nCustTotal) and set that property as the ControlSource for the textbox. Then, you just need to call the Textbox's Refresh method to update the display.

In fact, it occurs to me that you could call the code to do the calculation from the textbox's Refresh method. Then, when you do whatever changes things, you can just call the textbox refresh:

CODE

ThisForm.Text1.Refresh() 

Tamar

RE: Sum total in a text field on a form from linked child table records

(OP)
Hey Tamar

I appreciate the post and suggestions which I'll look into and post back.

Thank you

Steve Williams

RE: Sum total in a text field on a form from linked child table records

Quote (Steve-vfp9user)

how can I sum total in a text field on a form from linked child table records from a field called TOTAL N(8,3)?

Maybe I am over simplifying things, but unless the TOTAL values in the Child table are going to change while the user is in the Form, why not just run a SQL Query of the child table and accumulate the expression-specific totals on your Form.Load or Form.Init method?

Then as you change whatever you need to change on the form, the totals will be ready and waiting (result from a SEEK into your SUMData) for you to display.

Yes there would be 1 wait time while those totals were initially accumulated, but no wait each time they were needed.

Good Luck,
JRB-Bldr




RE: Sum total in a text field on a form from linked child table records

(OP)
The sum command worked. I hadn't heard of that before and found it in the help file.

Appreciate the quick reponse.

Thank you

Steve Williams

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close