I am using Access2007 in XP (please don't judge). So, I have looked everywhere on the Internet. I have even been reading Access 2007 All-in-one for dummies. I think I have done everything right. I have a mainform (frmOrders) linked to a subform(sfrmOrderDetails).
I have an unbound text box in the subform footer (fldItemSum) with control source =Sum([order_details_tbl]![o_unit_price]*[order_details_tbl]![qty]). The value is definitely coming from the table fields, not the subform text fields.
Now, on my mainform, I have a field called order_sub with control source =[sfrmOrderDetails].[Form]![fldItemSum].
Here's the problem:
1. When I open the subform by itself (continuous format), fldItemSum calculation works fine.
2. When I close the subform, then open the mainform containing the subform, both fldItemSum and order_sum both show #Error.
3. I close the mainform, then open the subform again, and I get #Error in fldItemSum.
4. I close the subform and reopen it and the fldItemSum calculation works again.
I am banging my head against the wall. I think there has to be something either wrong with the mainform or the table. I'v tried renaming fields and text boxes. I don't know where to go from here. Here is the db.