Calculation Question

[CarolOB]

I have a database containing two tables - members and family and I have a main form (named members2) and a subform (named family_subform1). On the main form (members2), I have a field named LockerFee which normally contains the value of $10.00. On the subform, I can have multiple LockerFees depending on how many family members there are. What I want to do is to have the LockerFee field on my main form display a sum of all the lockerfees in the family subform plus the value on the main forms lockerfee. I do have the two tables joined together based on a memberID #.
I have created a query that does produce the correct total, but I can't seem to correctly "tie" that query to the main form. I keep getting an #Error or #Name? displaying in the field.
I would REALLY appreciate any help you can offer me.

Thank you. CarolOB

 

[UncleJack]

Think in terms of values in the tables rather than values on the forms. You can set the LockerFee text box on the form to the values in the tables by writing an expression that sums them. Try setting the RecordSource for you form value to something like this:

=Me!LockerFee + DSum("LockerFees", "FamilyMembersTable", "FamilyID = Forms!MainForm!FamilyID&quot

This expression considers that you have a FamilyID value displayed on the form to which all the family members belong. The DSum function will add up all the locker fees for this familyID group provided they are stored in the FamilyMembersTable.

Uncle Jack

 

[CarolOB]

Hi Uncle Jack,
Thank you for your response. I've tried it out, but I haven't yet been able to get it to work. I'll keep trying and will let you know if & when I get it to work.

Thanks CarolOB