Calculating a subform

[Keiath]

Hi Guys

Hope you can help

I have a subform on Tab page, it has a id "site code" that then displays on the subform all entries with the site code. after the site code is various fields and i need to add the columns up whats the best way to do this?

Thanks

 

[CJ_London]

In the footer of the subform create an unbound control (lets call it 'TtlCtrl1')

In the controlsource for that control put =sum(Ctrl1) where Ctrl1 is the name of the control on your subform you wish to add.

 

[Keiath]

In the footer of the subform create an unbound control (lets call it 'TtlCtrl1')

In the controlsource for that control put =sum(Ctrl1) where Ctrl1 is the name of the control on your subform you wish to add.

This is what I did calling the textbox Addupofrec

=Sum([Forms]![Arrears and Income Report subform]![Actually Received])

Then need to display the result on another subform so i have

=[Forms]![Arrears and Income Report subform]![Addupofrec]

but it comes up an Name?

Using 2010

 

[CJ_London]

You do not refer to the forms collection when referring to a control in the same form - you refer to the Me collection (which is the default for a form).

Try

=Sum([Actually Received])

or

=Sum(Me.[Actually Received])


And with regards the second bit

Then need to display the result on another subform so i have

=[Forms]![Arrears and Income Report subform]![Addupofrec]

If the subform is in the same form as your other subform you use the Parent collection so

=Parent.[Arrears and Income Report subform].form.[Addupofrec]

 

[Keiath]

You do not refer to the forms collection when referring to a control in the same form - you refer to the Me collection (which is the default for a form).

Try

=Sum([Actually Received])

or

=Sum(Me.[Actually Received])


And with regards the second bit


If the subform is in the same form as your other subform you use the Parent collection so

=Parent.[Arrears and Income Report subform].form.[Addupofrec]

Hi I dont know if the first part works as I cant see the results and the second part i am getting #Name?

 

[Keiath]

I have zip database up and enclosed

the only site that has any data is 0289S

 

[CJ_London]

Your error implies you have a spelling mistake or the control is not there

• Is your subform called [Arrears and Income Report subform]?
• And is it a subform on the main form which holds the subform where you are trying to display the data?
• And it is not a subform in a subform?

I cant see the results

, Sounds ike you are using a datasheet. Temporarily change the subform to a continous form, you should be able to see it there

 

[CJ_London]

I've had a look at your db - your 'calling' field is on the main form, not a subform so your code needs to be:

=[Arrears and Income Report subform].form.[Addupofrec]

You had also missed out the form part of the source as highlighted in red

 

[Keiath]

I've had a look at your db - your 'calling' field is on the main form, not a subform so your code needs to be:

=[Arrears and Income Report subform].form.[Addupofrec]

You had also missed out the form part of the source as highlighted in red

Thank you so much that solved it

might i touble you now for a more complex issue

you can see on tabs "Invoice" that look like this

Invoice Date PO Invoice Contractor Expense Code Lease Code Invoice Amount Status

28/09/2013 34606 1141 Platinum Landscapes 55 A 240.00
03/09/2013 35311 2021 Clean And Green 63 B 167.00
02/08/2013 35311 2015 Clean And Green 63 A 174.00
19/08/2013 34606 1121 Platinum Landscapes 55 A 240.00

Then though a query i group every thing an sum by expense type (55, 63 ect and by code (A, B C ect)

Site Code Lease Code Expense Code SumOfInvoice Amount
0289S A 52 £986.88
0289S A 54 £878.65
0289S A 55 £24,188.72
0289S A 58 £892.20
0289S A 62 £4,791.23

On a form that will be called Income and Exp

I need to lookup the details in other using a combo box on new page i want to select my expence code and from that fetch the sumofinvoice according to ecp type and code, Hope that makes sence

As I need to put togeather a full P&L whats your advise on this?

Thanks again

 

[CJ_London]

Hi Keiath,

Please click on the Thanks! button.

Re new issue, I'm about to go into meetings so cannot respond for a few hours. Since this is a new issue, I recommend you start a new thread so someone else can perhaps help - I'll look for it on my return and see what I can suggest.

Thanks