DSum Values using cmd Button on Subform

[dipitiduda2]

Hello All: Since I'm a newbie, I need assistance in getting my DSum calculations to appear on a subform when I click on a button. The problem is I have to click on the btn 4 times to display all of the values in each of the controls within the subform's Detail area. I want to click on the btn only one time and have the values populate the Detail and Footer textboxes. Let me explain.

I have a main form 'frmProgramsAdult' that has a subform 'subfrm_ProgramsAdult_PFRRs' linked using Master/Child Fields on the main table PK 'ProgramID'.

A. I have a query 'qry_ProgramsAdult_PFRR_Calc' that performs the calculations - see SQL below:

SELECT tbl_ProgramsAdult_PFRRAccts.PFRRID, ([FeesRecdDeposited]-[FeesDisbursed]) AS DiffFeesRecdDeposited, ([BeginBalance]+[TotalFeesRecdDeposited]+[InterestRecd]+[FeesRecdCSProg]+[OtherRevenueAdjustments]) AS SumRecdAmt, ([AdminOpsAllocation]+[CampusUmbrellaAllocation]+[ExcessFundsLECSAllocation]+[ActualRewardsPaid]+[BankFeesPaid]+[OtherExpensesAdjustments]) AS SumExpendedAmt, ([TotalRecdAmount]-[TotalExpendedAmount]) AS DiffEndBalance
FROM tbl_ProgramsAdult_PFRRAccts
WHERE (((tbl_ProgramsAdult_PFRRAccts.PFRRID)=[Forms]![frmProgramsAdult]![subfrm_ProgramsAdult_PFRRs].[Form]![PFRRID]));

B. The DSum textboxes for the 4 values coming in from the query are located in the subform's footer with the Control Source as such:

Note: cbo_ProgramName is the Combobox control on the main form; Column(0) is the PK 'ProgramID'

1st Textbox Name: txtDiffFesRecdDeposited
Control Source:

=DSum("DiffFeesRecdDeposited","qry_ProgramsAdult_PFRR_Calc","[ProgramID] = " & [Forms]![frmProgramsAdult].[Form]![cbo_ProgramName].[Column](0))

2nd Textbox Name: txtSumRecdAmt
Control Source:

=DSum("SumRecdAmt","qry_ProgramsAdult_PFRR_Calc","[ProgramID] = " & [Forms]![frmProgramsAdult].[Form]![cbo_ProgramName].[Column](0))

3rd Textbox Name: txtSumExpendedAmt
Control Source:

=DSum("SumExpendedAmt","qry_ProgramsAdult_PFRR_Calc","[ProgramID] = " & [Forms]![frmProgramsAdult].[Form]![cbo_ProgramName].[Column](0))

4th Textbox Name: txtDiffEndBalance
Control Source:

=DSum("DiffEndBalance","qry_ProgramsAdult_PFRR_Calc","[ProgramID] = " & [Forms]![frmProgramsAdult].[Form]![cbo_ProgramName].[Column](0))

C. Unfortunately, I have to click on the btn 3 more times to populate the 4 bound textboxes in the Detail area.

Note: The controls have the same Name and Control Source with the Record Source = tbl_ProgramsAdult_PFRRAccts.

1st: TotalFeesRecdDeposited
2nd: TotalRecdAmount
3rd: TotalExpendedAmount
4th: EndBalance

D. Finally, the code behind the cmd btn 'cmd_CalcRefresh' I am clicking to bring in
the 4 values is as follows:

Private Sub cmd_CalcRefresh_Click()
On Error GoTo cmd_CalcRefresh_Click_Err
   
    Me.[TotalFeesRecdDeposited].SetFocus
    Me.[TotalFeesRecdDeposited].Value = Me.[txtDiffFeesRecdDeposited].Value
    Me.[TotalRecdAmount].SetFocus
    Me.[TotalRecdAmount].Value = Me.[txtSumRecdAmt].Value
    Me.[TotalExpendedAmount].SetFocus
    Me.[TotalExpendedAmount].Value = Me.[txtSumExpendedAmt]
    Me.[EndBalance].SetFocus
    Me.[EndBalance].Value = Me.[txtDiffEndBalance].Value
    Me.Recalc
    DoCmd.RunCommand acCmdRefresh
    
    Me.cmd_UpdatePFRR.Enabled = True
        
cmd_CalcRefresh_Click_Exit:
    Exit Sub
cmd_CalcRefresh_Click_Err:
    MsgBox Error$
    Resume cmd_CalcRefresh_Click_Exit
End Sub

Any assistance is much appreciated!

[June7]

These summary calcs can't be done directly in the subform footer section?

[dipitiduda2]

Hi June7: Yes, they are being done in the footer section, but to make a long story short - they need the Access form to mirror the paper-based forms as closely as possible. That means that I need the values on the footer section to be replicated in the Details section. And, the users need to be able to recalculate the values as they make revisions. I thought that if the values were visible in the footer I could just assign them to the textboxes in the detail section. If not, I will find some other way to show the user what those calculations are from the footer section. Thanks!

[June7]

What I meant was, SQL aggregate functions (Sum, Avg, Count) won't get the results you want in footer section?

Why would summary totals be shown in detail section?

If you are trying to get running balance in the detail section, that is difficult in query or form, easier in report.

[dipitiduda2]

June7: Yes, the values are shown in the footer section just fine. They are being summed in the query and displayed within the 4 textboxes on the form's footer.

As for the summary totals - I'm displaying them in the detail section is so the user can check those amounts against the hard copy version they have on file. Also, there are users who will be entering figures into the database and they need the process to be as close to the old paper-based method as possible.

Regarding the running balance - It won't work for the users to have the running balance represented on a report because then that means they have to run a report to check just these 4 figures. That's extra work that isn't worth it for our staff.

I'm not sure what the solution will be, but if I can code the cmd btn 'Recalculate' to display the figures in the detail section on 1 button click instead of 4 button clicks that should be sufficient for my users.

What I don't understand is why I can't simply take the calculated values - as displayed in the form's footer section - and display those same values in the detail section. The values already appear to be available to the form in the footer section after I click the Recalculate button 1 time after the form is open for editing.

I will play around with the code some more to see if I can find a workaround. Thank you very much!

[June7]

If you are using DSum in the footer section, then DSum should also work in textbox in detail section or in the form RecordSource sql. Domain aggregates can be noticeably slow.

Part of the problem may be that the expressions in subform reference combobox on main form. Weird as it will sound, subforms load data before the main form.

Also, I am surprised the DSum expressions work because syntax for referencing combobox doesn't look right. The red part should not be needed.

[Forms]![frmProgramsAdult].[Form]![cbo_ProgramName].[Column](0))

Regardless, I don't understand why requires 4 clicks to recalculate the textboxes.

Have you step debugged? Refer to link at bottom of my post for debugging guidelines.

[dipitiduda2]

Ok, I fixed the DSum expression for each textbox - thank you. I debugged as much as I could, but did not understand how to use watches, debug.assert, etc...so I appreciate the info. I need to move onto another task so I can mull it about, but will post back when I get the issue of btn clicks resolved - or not. Cheers!