Re: populate a calculated field

[alicejwz]

Re: populate a calculated field

Hi one and all,
I’m trying to populate a calculated field, an unbound field,on the form with the query in the Form open event. But tt_qty is not showing. The tt_qty calculated field is use to store the sum of bag_quantity field in tbl of a grouped work_ord_num, work_ord_line_num, material_lotnum, insert_lotnum. There should be one row for each work order #.
This works fine in a view. Why it won’t display in Access form? No error message appear.
Thanks for your input>
Ex:
Tbl
work_ord_num work_ord_line_num bag_num material_lotnum insert_lotnum bag_quantity
123456-00 001 1 05111222 50
123456-00 001 2 05111222 50
123456-00 001 3 05222111 05222111 75
123456-00 001 4 05222111 05222111 75


Results
work_ord_num work_ord_line_num material_lotnum insert_lotnum tt_qty
123456-00 001 05111222 50
123456-00 001 05222111 05222111 150

Private Sub Form_Open(Cancel As Integer)
Dim sqlstr As String

sqlstr = "SELECT TOP 100 PERCENT work_ord_num, work_ord_line_num, material_lotnum, insert_lotnum, sum(bag_quantity) as tt_qty "
sqlstr = sqlstr & "FROM dbo.tblBag_data_Deflash GROUP BY work_ord_num, work_ord_line_num, material_lotnum, insert_lotnum "
sqlstr = sqlstr & "ORDER BY work_ord_num, work_ord_line_num, material_lotnum, insert_lotnum;"
Me.RecordSource = sqlstr

End Sub