Cannot do a subtotal within a subform

Access 2003

In the Northwind.mdb database, there is an "Orders" form, and this form has a "Orders Subform"
within it. In the "Orders Subform", there is a field named "OrderSubtotal", and this field sum up
all of the value in the field "ExtendedPrice". This works.

Form Name: Orders
Sub Form Name: Orders Subform
Field Name: ExtendedPrice
The location of field is in the detail section of Orders Subform

Field Name: OrderSubtotal
Control Source: =Sum([ExtendedPrice])
The location of field is in the form footer of Orders Subform

////////////////////////////////////////
I have a form with a subform, just like in the above configuration, but the subform recordsource
is not a select query but a union query. The subform also has a sum field, but it gets an error
when I run the form.

Main Form Name : Search
Field Name: Total_Payout_In_Main_Form
Control Source =SubForm_Search_3.Form!Total_Payout

SubForm Name: SubForm_Search_3
RecordSource: Q_LIST_ALL_METRIC_NAME_FOR_AN_EMPLOYEE_H1_H2_FY_PAYOUT (This is a union query)
In the Detail section of the form there is a field name Payout_Value

In the Form Footer section of the form there is a field name Total_Payout
Control Source=Sum([Payout_Value])

The text box Payout_Value that reside in the SubForm_Search_3 is a numeric field

In summary there are three fields
1) Payout_Value --> reside in the Detail Section of the SubForm_Search_3
2) Total_Payout --> reside in the Form Footer Section of the SubForm_Search_3
3) Total_Payout_In_Main_Form --> reside in the Detail Section of the form Search

When the form Search is opened, it suppose to show the value of the field Total_Payout_In_Main_Form,
I have no error message.
In the text box Total_Payout_In_Main_Form, there is a text that read "#Error". There is no number in this textbox.
In the text box Total_Payout in the subform footer there is a text that said  "#Error".
This is the same technique that was use in the Order form of the Northwind.mdb database. Start Free Trial