DSum problem.

[kowalski]

Hi All,
I have the following tables and am trying to populate a text box on a form, and store that value in a field TotalSegLengths

tbl_Segments
LineNum SegLength


1 3
2 9
2 10

tbl_Lines
LineNum TotalSegLengths
1
2
3

So when I create a new record in tbl_Lines from the form (Form is bound to tbl_Lines) it should query the tbl_Segments, add up the SegLengths for the corresponding LineNum and store that value in TotalSegLengths.

So for example, with the data as per above, from the form, create a new record with LineNum = 2 and it should display 19 in a text box and store 19 in the TotalSegLengths field.

I was trying to use DSum in the text box Control Source but was just returning an #Error in the text box.

Thanks!

[June7]

Why save the calculated total? Calculate when needed. Create a query that joins the two table. Make the query an aggregate (GROUP BY) or use report Grouping & Sorting with aggregate calcs functionality.

Show your DSum expression. Something like:
= DSum("SegLengths", "tbl_Segments", "LineNum=" & [LineNum])

Results of textbox ControlSource expression will not automatically save to table. Will need code (I use only VBA) to save the calculated result.