Total Control Value in SubForm

[libraccess]

I'm trying something totally new to me here
I have a two controls on a continuous subform the row source for both is a query and Column(2) is a Name, Column(3) is a number.
These are displayed by setting Column Count and Column widths to suit.
The whole picture is - the Main Form will have a Calculated Value
I want to enter a number of entries in the subform and depending on the value allocated to each Name (location) I will then allocate a portion of the Total Cost to each name.
It's taken me ages to get a cascading combos where the 1st is on the Main and the Second is on the subform now I cannot total this value on my subform so until I can do this I cannot get a percent to allocate each Location Cost (does this make sense)
I'd hoped this might work but I don't think Column count can be used in an expression
=Sum([DestinationNo].[Column](3))
Also the subform record source is not the table where the values are stored which I feel is part of the problem

[June7]

Do you really need to do this on form? It really sounds like something that should be calculated on a report.

Want to provide project for analysis?

[libraccess]

This is one of those situations where I have implemented a DB and someone has now decided that more info is needed (the DB is about 10MB, 60 objects, quite a few charts which seems to bump it up.)
The data for this calculation comes from a few different tables and I fear if I attempt to add Foreign Keys and more relationships to accommodate things I will (stuff it up) "technical term"
This form (or maybe as you say report could be better will be used in the DB as a temporary calculator ( currently these costs are worked out on 3 different spreadsheets) and inserted manually into different spreadsheets. Although now they are entered into the DB
So it would be better to calc these costs and then be directly entered in the in the appropriate controls


I realise this seems all a bit vague, hopefully you can make some suggestions here anyway.
Like take retirement or a job as a lollipop man
Attachment 7929

[June7]

First, there is no combobox named DestinationNo so that is one error in the Sum. Second, I can't get Sum or DSum to work, don't understand why.

So this is what I did:

1. RecordSource for frmCalculate
SELECT OriginName, IDOrigin FROM tblOrigin WHERE [IDOrigin]=[Forms]![frmCalculate]![OriginName];

2. Code in AfterUpdate event for combobox OriginName:
Me.Requery

3. RecordSource for frmSubDest
SELECT tblOriginDestination.*, DistanceFactor FROM tblDestination RIGHT JOIN tblOriginDestination ON tblDestination.IDDestination = tblOriginDestination.DestinationNo;

4. Change second combobox Text10 to a textbox, ControlSource of DestinationFactor, Locked Yes, TabStop No

5. Set frmSubDest as Datasheet view. While in Datasheet view, place cursor into subform then click Totals from the ribbon. This will open a Totals row in the subform. Select the Sum aggregate function for the DestinationFactor column. Save the form. When new record is committed, the total will adjust. Can't refer to this sum because it is not in a control but can see the total.

[libraccess]

wow once again thankyou for your help on this as I had spent time trying allsorts
you know where the more make changes the messier it becomes. Maybe you don't maybe it's just me.
OK so now I am going to finish the form and use a report to summarise what I have , I'm thinking that maybe better as you suggested.
Thanks again