Hi All,
I have created a main form called 'school_details_frm', which consists of
the following fields relevant to this problem I am trying to solve:
CostCentre (primary key), SchoolName, VisitHoursPurchased,
PurchasedHoursRemaining (new calculated field added on main form, but not
in
table)
The form consists of a subform called 'visit_details_subfrm', which has
the
following fields linked by CostCentre field (1:M relation****p):
SchoolVisitNumber (primary key), VisitDate, VisitType, VisitStartTime,
VisitFinishTime, HoursCompleted (calculated field added on main form, but
not
in table, currently has the following control source formula:
Format(DateDiff("n",[VisitStartTime],[VisitFinishTime])/1440,"hh:nn"))
I would like for the 'PurchasedHoursRemaining' field on the main form to
be
calculated as: VisitHoursPurchased - Sum of sub records (HoursCompleted)
One way I tried this was by adding a new field in the subform called
‘VisitStatus’ (yes/no data type) and adding the following control
source code
in the 'PurchasedHoursRemaining' field on the main form:
=[VisitHoursPurchased]-DSum("[HoursCompleted]","[visit_details_tbl]![VisitStatus]
= True and [CostCentre] = " & visit_details_subfrm.Form!CostCentre)
However all I receive is an Error# message in the field. Can anyone help?
Many thanks.
Kind Regards
Ammo
|
