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:
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:
2nd Textbox Name: txtSumRecdAmt
Control Source:
3rd Textbox Name: txtSumExpendedAmt
Control Source:
4th Textbox Name: txtDiffEndBalance
Control Source:
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:
Any assistance is much appreciated!
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:
Code:
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:
Code:
=DSum("DiffFeesRecdDeposited","qry_ProgramsAdult_PFRR_Calc","[ProgramID] = " & [Forms]![frmProgramsAdult].[Form]![cbo_ProgramName].[Column](0))
Control Source:
Code:
=DSum("SumRecdAmt","qry_ProgramsAdult_PFRR_Calc","[ProgramID] = " & [Forms]![frmProgramsAdult].[Form]![cbo_ProgramName].[Column](0))
Control Source:
Code:
=DSum("SumExpendedAmt","qry_ProgramsAdult_PFRR_Calc","[ProgramID] = " & [Forms]![frmProgramsAdult].[Form]![cbo_ProgramName].[Column](0))
Control Source:
Code:
=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:
Code:
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!