DSum Values using cmd Button on Subform (1 Viewer)

hmorgan

New member
Local time
Today, 12:50
Joined
Apr 16, 2014
Messages
2
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:

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

MarkK

bit cruncher
Local time
Today, 10:50
Joined
Mar 17, 2004
Messages
8,178
It looks like this process does a lot of extra work. What I recommend for you going forward is learn how to write Aggregate Queries, which you can do quite simply in the query design grid by hitting the button with the Sigma character on it. That essentially allows you to write the Sum() right into the query. Here's a sample of what the SQL might look like . . .
Code:
SELECT OrderID, Sum(Quantity * UnitCost) As OrderTotal
FROM OrderDetail
WHERE CustomerID = 1234
GROUP BY OrderID
So it's a Select query, but it returns the Sum() of many records inside the query, and for a specific customer, but also for specific Orders. Notice the GROUP BY clause! So you can see how powerful that is and how many DSum()s you might be able to eliminate, because you could write a query like this with the Sum()s of many fields in one query, and that query would return the sums of many fields, for many orders, and perhaps even many customers!!!

Then, learn how to open Recordsets, which use queries to make all that data available to coded loops in real-time.

1) Write aggregate queries. 2) Open and read from recordsets. These are essential skills in using relational databases.

Hope this helps,
 

Users who are viewing this thread

Top Bottom