need help with VBA wording

[drgn_str]

It has been a long time (15years) since I have done anything with VBA coding and I need some help with the wording.

I have a Client Information Form that I would like to have an Account balance that update when you load that client. I have three different tables that have information to be calculated: Beginning July Balances, Charges, and Payment_Adjustments.

Here are the formulas that I need to use (I think they are correct):

TotalFTCharge: DSum("FTFee*FTDaysCharged","Charges","FamilyID=" & [Client Info]![FamilyID])
TotalPTCharge: DSum("PTFee*PTDaysCharges","Charges","FamilyID=" & [Client Info]![FamilyID])
TotalCharges: [TotalFTCharge]+[TotalPTCharge]
TotalPayments: DSum("Amount","Payment_Adjustment","FamilyID=" & [Client Info]![FamilyID])
ClientBalance: [BeginningJulyBalance]![BeginningBalance]]+[TotalCharges]-[TotalPayments]

If someone could point me in the right direction on my next steps I would be forever grateful.

 

[Galaxiom]

The information would be better gathered in a query used as the recordsoure of a subform. It will be several times faster than a bunch of domain functions.

 

[drgn_str]

I initially tried using a query because it is a lot easier to set up than VBA coding but I keep coming up with errors so I thought it would be "easier" to run a module to update the client balance when it loads the client. I know the answer is staring me in the face, I just can't see it yet.

 

[Galaxiom]

All the domain functopns share the FamilyID. You shoud be able to join on this field and pull whatever fields you want form any table.

What error are you getting?

 

[drgn_str]

It says 'You tried to execute a query that does not inclued the specified expression '[TotalCharges]+[Beginning July Balance]![BeginningBalance]-[TotalPayments]' as part of an aggregate function.

That is the last error I received when I ran the query again. The time before that it just said error in each cell and did not give me any numbers or what the error was

The SQL language of the query looks like this:

SELECT Client_Info.FamilyID, DSum("FTFee*FTDaysCharged","Charges","FamilyID=" & [Client Info]![FamilyID]) AS TotalFTCharge, DSum("PTFee*PTDaysCharges","Charges","FamilyID=" & [Client Info]![FamilyID]) AS TotalPTCharge, DSum("[Amount]","Payment_Adjustment","FamilyID=" & [ClientBalance]![FamilyID]) AS TotalPayments, [TotalCharges]+[Begining July Balances]![BeginningBalance]-[TotalPayments] AS ClientBalance
FROM ((Client_Info INNER JOIN [Begining July Balances] ON Client_Info.FamilyID = [Begining July Balances].FamilyID) INNER JOIN Charges ON Client_Info.FamilyID = Charges.FAMILYID) INNER JOIN Payment_Adjustment ON Client_Info.FamilyID = Payment_Adjustment.FAMILYID
GROUP BY Client_Info.FamilyID, DSum("FTFee*FTDaysCharged","Charges","FamilyID=" & [Client Info]![FamilyID]), DSum("PTFee*PTDaysCharges","Charges","FamilyID=" & [Client Info]![FamilyID]), [TotalFTCharge]+[TotalPTCharge], DSum("[Amount]","Payment_Adjustment","FamilyID=" & [ClientBalance]![FamilyID]), [TotalCharges]+[Beginning July Balances]![BeginningBalance]-[TotalPayments]
HAVING (((Client_Info.FamilyID)=[Family ID]));

I am not sure what is going on but everything looks correct to me and it looks like it should work. The problem is probably staring me in the face and laughing at me by now.