bit of code help

[dkirk02]

Ok, here we go. I have an main form that pulls up general case info by case id (unique value) It has a subform that brings all accounts and acct info (from another tbl)related to each id.
Each case could have multiple accounts. Each account has a balance. On the main form, I need to be able to calculate the total case balance, the sum of the balances for all accounts related to that case. Following is the code I am currently using. Right now, I can only get it to calculate the dsum of the accounts related to the first record in the tbl. I think the problem is how to tell it to calculate all records from the account tbl that have the same case id as on the main form. Any ideas?

Private Sub Form_Open(Cancel As Integer)
Total balance.Value = DSum("[Account Info]![balance]", "Account Info", "[Account Info]![Case Id] = [Forms]![Edit Case Info]![Case ID]")
End Sub

Thanks in advance for any help!

 

[chrismcbride]

Maybe I am oversimplifing the problem, but I would think that if you want a Sum of all Balances, then you don't want to have any Criteria in you DSum.

Total balance.Value = DSum("[Account Info]![balance]", "Account Info", "[Account Info]![Case Id] = [Forms]![Edit Case Info]![Case ID]")

would look more like

total_balance = DSum("balance", "[Account Info])

Chris

 

[dkirk02]

Firstly, thank you both for your help!

Pat-
The case id is the unique value throughout this db and is an autonumber. So, I tried the first code that you suggested. However, I am having the same results as before, it is calculating, but it is only summing the balances of the first record in the db, not the record currently displayed on the form.

 

[Rich]

Can't you put the sum in the subform footer and refer to it on the main form.