Calculate sum from Sub form (DataSheet View) column

[Shallo9]

Hello All,

In the main form I've got the actual amount for example 1000 and
I have a sub form in data sheet view which has two columns
ROW Amount Recieved Outstanding Amount
1 100 900
2 200 700
3 300 400

I want this Outstanding Amount to be calculated depending on the Amount_Recieved, I can do this using Recordset but I want this to be updated automatically evertime a particular record has been amended or changed. For example if I change the Amount_Recieved in the first row to 150 the subsequent Outstanding_Amount should update for all the rows.

I've got a logic where I can store the Actual_Amount from the main form into a variable and substract the Sum of all Amount_Recieved until the current row to get the consequent Outstanding_Amount.

EX: if I had to calculate the Outstanding_Amount of Row 1 then my formula would be Actual_Amount - Sum of all the Amount_Recieved upto Row 1.

for Row 3 it would be Actual_Amount - Sum of all the Amount_Recieved upto Row 3. which would be 1000-(100+200+300) = 400.

But i need help to implement this.

Thanking you in Anticipation.

 

[Alansidman]

This is an untested suggestion. In the footer of the main form, try this formula

MainformTextBox - Dsum("AmountRecived","TableHoldingTheseValues","MainFormRecordID=RecordID")

Look at this for DSum tutorial
http://www.techonthenet.com/access/functions/domain/dsum.php

 

[Shallo9]

This is an untested suggestion. In the footer of the main form, try this formula

MainformTextBox - Dsum("AmountRecived","TableHoldingTheseValues","MainFormRecordID=RecordID")

Look at this for DSum tutorial
http://www.techonthenet.com/access/functions/domain/dsum.php

Please look at the attached xl sheet and the formulae in it.

I want this functionality in a datasheet form of MS ACCESS


Many Thanks

 

[Alansidman]

Did you try my suggestion in the earlier post. Your excel spreadsheet was exactly how I envisioned your problem. If you did try it, what was the result? If you didn't try it--why not?

You would have been better served if you posted your database so that we can experiment with what you have and not try to recreate what we think you have.

 

[vbaInet]

Adapting Alansidman's solution slightly, one of:

MainformTextBox - DSum("[COLOR=Red]AmountReceived[/COLOR]","[COLOR=Red]TableName[/COLOR]","[COLOR=Red]ID[/COLOR] [COLOR=Blue]<=[/COLOR] " & [COLOR=Red]ID[/COLOR])

MainformTextBox - (SELECT [COLOR=Red]AmountReceived [/COLOR]FROM [COLOR=Red]TableName [/COLOR]WHERE [COLOR=Red]ID[/COLOR] [COLOR=Blue]<=[/COLOR] " & [COLOR=Red]ID[/COLOR])

Amend the bits in red.

Note, option 2 is faster and can only be used in a query. Option 1 can be used in a query or directly in the Control Source of a textbox.

 

[Shallo9]

Adapting Alansidman's solution slightly, one of:

MainformTextBox - DSum("[COLOR=red]AmountReceived[/COLOR]","[COLOR=red]TableName[/COLOR]","[COLOR=red]ID[/COLOR] [COLOR=blue]<=[/COLOR] " & [COLOR=red]ID[/COLOR])

MainformTextBox - (SELECT [COLOR=red]AmountReceived [/COLOR]FROM [COLOR=red]TableName [/COLOR]WHERE [COLOR=red]ID[/COLOR] [COLOR=blue]<=[/COLOR] " & [COLOR=red]ID[/COLOR])

Amend the bits in red.

Note, option 2 is faster and can only be used in a query. Option 1 can be used in a query or directly in the Control Source of a textbox.

Hi Yah,

I've been working on both these options since yesterday, but for some reason if it works it generates some random number (Very Inappropriate).

Actually let me tell you one more thing, both these tables have a foreign key reference. In this case CreditContID in both the main form and sub form with a Master Child relationship.

So now I want the criteria to be Where Mainform.CreditContID = Subform.CreditContID and Subform.<<current>>PaymentID < = Subform.PaymentID

But for some reason this doesn't works. If you could please shed some light on this that would of a great help.

Many Thanks

 

[vbaInet]

Oops... option 2 is missing the Sum() aggregate function.

MainformTextBox - (SELECT [COLOR=Red]Sum([/COLOR]AmountReceived[COLOR=Red]) [/COLOR]FROM TableName WHERE ID <= " & ID)

In any case, the first one should have worked based on your requirements.

By the way, I don't understand your spreadsheet in post #3. I don't see how you derived 20000.

 

[Shallo9]

View attachment 40288Oops... option 2 is missing the Sum() aggregate function.

MainformTextBox - (SELECT [COLOR=red]Sum([/COLOR]AmountReceived[COLOR=red]) [/COLOR]FROM TableName WHERE ID <= " & ID)

In any case, the first one should have worked based on your requirements.

By the way, I don't understand your spreadsheet in post #3. I don't see how you derived 20000.[/QUOTE]


Hi,

Please find attached my test DB which is causing me a pain. Reg the 20000 in the spreadsheet, I just declared it as that value would be passing from the main form.

Have a look at the form in my DB and you'd get a clear picture.

"Actual Premium" is the value passing to the subform (Payments) and then the rest we've been discussing in the past.

Let me know if u need any more info in this regards.

Thank you ever so much for your help.

FYI : I use Access 2010.

 

[vbaInet]

Most of us don't use 2010 so you will need to save it as a 2007 db.

Here's how:

http://office.microsoft.com/en-us/a...se-in-an-earlier-file-format-HA010341553.aspx

 

[Shallo9]

Most of us don't use 2010 so you will need to save it as a 2007 db.

Here's how:

http://office.microsoft.com/en-us/a...se-in-an-earlier-file-format-HA010341553.aspx

Hi,

Please find attached the updated version of the DB.

Many Thanks

 

[vbaInet]

I don't see where you've applied the code?

 

[Shallo9]

I don't see where you've applied the code?

Hi Again,

Please look into the Control Source of the currAmtOutstand control on sfrmPayments form


Many Thanks

 

[vbaInet]

Why didn't you tell us that you added an extra criteria? These little bits of extra information may seem insignificant to you but to us they are important.

Perform a manual calculation and tell me exactly what values you expect for those three records.

 

[Shallo9]

Further to your message, I am not sure which record are you referring to but any ways, If you are referring to the record with CreditContID =2 then the actual premium is 50,000 and everytime they receive a payment amount (in this case the payment recieved was 200, 100 and 200.

The outstanding amount in the first row shoule be Actual Premium (50000) - 200 which should be 49800

The outstanding amount in the Second row shoule be Actual Premium (50000) - (200+100) which should be 49700

The outstanding amount in the Third row shoule be Actual Premium (50000) - (200+100+200) which should be 49500

NB please note there are two hidden fields which hold a primary key for payments table and a foreign key referring to the CreditContID in the main table.

Please look at the Relationship chart for the actual columns.

I might've done it wrong but I've also tried with your actual suggestion with no success

[Forms]![frmTempMain1]![currPremium]-DSum("[currCmtRcvd]","[tblPayments]","[PaymentsID]<=" & [PaymentsID])

 

[vbaInet]

See the attached (which uses option 2).

 

[Shallo9]

See the attached (which uses option 2).

Hi Yah,

Thank you for your help ...this one works fine but doesn't allow me to add any more payments to it or amend the existing payments. It almost locks the table.
It prompts Recordset not updateable.
I've tried changing the properties like Data Entry

Thanks

 

[vbaInet]

It's because the query (i.e. qryPayments) is using a GROUP BY clause that's why it's not updateable.

I've reverted back to option 1 (the slightly slower version). See attached.

You could also use a subform which will be faster than option. Look into that.

 

[Shallo9]

It's because the query (i.e. qryPayments) is using a GROUP BY clause that's why it's not updateable.

I've reverted back to option 1 (the slightly slower version). See attached.

You could also use a subform which will be faster than option. Look into that.

Thank you Very much VBAINET, You've been a great support to me. I am playing around with that function now. A quick question regarding the Dsum function, How do I decide whether to use [ ] brackets or not and also where to use the " " double quotes.

 

[vbaInet]

You're welcome!

Double quotes and single quotes are interchangeable. You can use one or the other (most times).

Square brackets are only needed when a name contains spaces or special characters.

 

[Shallo9]

Hi vbaInet,

Sorry struck again...I have to mention this. I orginally asked for help with a simpler version and with your kind help I've managed to work out on dealing with the actual thing....A quick question: Can i change the Control Source at Runtime? I've got two different criteria now and I've tried two ways the first one: In the control Source I used the below formulae. Please note Both parts the true part (in Bold) and the false part (in Italic) works perfectly alright when used separately only when I try to use in conjunction with the Iff statement they fail.

I tried using with code but then can't specify which record are we trying to check.


=IIf( [qrypayments]![CurrencyID] <> 3,

(IIf(IsNull([currAmtRcvd]),"",[Forms]![frmTempMain1]![currNonUAEPrem]-DSum(" currAmtRcvd","qrypayments"," [qrypayments]![CreditContID] =" & [Forms]![frmTempMain1]![CreditContID] & " AND PaymentsID <= " & [PaymentsID] & "AND [qrypayments]![CurrencyID] <> 3"))),

(IIf(IsNull([currAmtRcvd]),"",[Forms]![frmTempMain1]![sfrmUAEMem].[Form]![currPremiuminAED]-DSum(" currAmtRcvd","qrypayments"," [qrypayments]![CreditContID] =" & [Forms]![frmTempMain1]![CreditContID] & " AND PaymentsID <= " & [PaymentsID] & "AND [qrypayments]![CurrencyID] = 3"))))


Thanking you in anticipation