Grand Totals

  • Thread starter Thread starter Les Coover
  • Start date Start date
L

Les Coover

Access 2000

Year Payment
2002 20
2002 10
2003 15
2003 30

tblYear:Field: Year

tblPayment:Fields: Year, Payment

One to many relationship from tblYear:Year to tblPayment:Year

Form with subform calculates total payments for year selected.

But how can I calculate Grand Total for all years on the same
form and get the field to update as new data is entered?

Les
 
Les Coover said:
Access 2000

Year Payment
2002 20
2002 10
2003 15
2003 30

tblYear:Field: Year

tblPayment:Fields: Year, Payment

One to many relationship from tblYear:Year to tblPayment:Year

Form with subform calculates total payments for year selected.

But how can I calculate Grand Total for all years on the same
form and get the field to update as new data is entered?

Les
Click to expand...

Here's how I would do it although maybe someone will come along with an
easier method.

Jack

------------------

Put a Text box at the bottom of your Form called "TotalPayments". For its
"Control Source" put:
=GetTotalPaymentAmount()

In your subform, put the following in the "After Update" event of your
"Payment" text box.

[Forms]![YourFormName]!TotalPayments.Requery

Put the following function in the code of your form:

Public Function GetTotalPaymentAmount() As Currency

Dim ThePaymentTotal As Currency, strSQL As String
Dim dbs As ADODB.Connection, rst As ADODB.Recordset

strSQL = "SELECT Sum([tblPayment].[Payment]) " _
& " AS [PaymentTotal] FROM [tblPayment]"

ThePaymentTotal = 0

' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the database
Set rst = New Recordset ' open a recordset

' open the query
rst.Open strSQL, dbs, adOpenStatic, adLockReadOnly

If rst.RecordCount > 0 Then
ThePaymentTotal = rst!PaymentTotal
End If

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing
GetTotalPaymentAmount = ThePaymentTotal
End Function
 
Thanks Jack

Exactly what I needed. I Appreciate your Help.

Les


Treebeard said:
Les Coover said:
Access 2000

Year Payment
2002 20
2002 10
2003 15
2003 30

tblYear:Field: Year

tblPayment:Fields: Year, Payment

One to many relationship from tblYear:Year to tblPayment:Year

Form with subform calculates total payments for year selected.

But how can I calculate Grand Total for all years on the same
form and get the field to update as new data is entered?

Les
Click to expand...

Here's how I would do it although maybe someone will come along with an
easier method.

Jack

------------------

Put a Text box at the bottom of your Form called "TotalPayments". For its
"Control Source" put:
=GetTotalPaymentAmount()

In your subform, put the following in the "After Update" event of your
"Payment" text box.

[Forms]![YourFormName]!TotalPayments.Requery

Put the following function in the code of your form:

Public Function GetTotalPaymentAmount() As Currency

Dim ThePaymentTotal As Currency, strSQL As String
Dim dbs As ADODB.Connection, rst As ADODB.Recordset

strSQL = "SELECT Sum([tblPayment].[Payment]) " _
& " AS [PaymentTotal] FROM [tblPayment]"

ThePaymentTotal = 0

' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the database
Set rst = New Recordset ' open a recordset

' open the query
rst.Open strSQL, dbs, adOpenStatic, adLockReadOnly

If rst.RecordCount > 0 Then
ThePaymentTotal = rst!PaymentTotal
End If

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing
GetTotalPaymentAmount = ThePaymentTotal
End Function
Click to expand...
 
Back
Top