Sum subform fields into another subform

Sum subform fields into another subform

Hi, I have a form set up as follows (irrelevant fields excluded):

Projects (main form)
Schedule (subform)
Estimated_Work_Hours (user input field)
Associated_Task (lookup to the main Tasks form)
Tasks (subform)
Estimated_Work_Time (calculated field)

I am trying to get a script that runs on user input of Estimated_Work_Hours. I need it to do this:

For each row in the Task subform, sum the Estimated_Work_Hours fields in the Schedule subform for the lines where Associated_Task matches the Task ID in the Tasks subform row, and input the result in Estimated_Work_Time.

(I hope that makes sense - sorry for the similar field names!)

I've tried various things, including the code below, but this returns the error message 'Lookup traversal in a Criteria Exceeds the limit of 2'.

Can anyone nudge me in the right direction with this?

Thanks in advance.

  1. totalschedtime = 0;
  2. for each taskline in Tasks_Subform
  3. {
  4. for each schedline in Projects.Schedule[Projects.Schedule.Project_title.ID=taskline.Project.ID]
  5. {
  6. totalschedtime = totalschedtime + schedline.Estimated_Work_Hours;
  7. }
  8. input.taskline.Estimated_Work_Time = totalschedtime;
  9. }