Main and subform investment calculation 1

[telephoto]

I have a table of fund names, these have a changeable value.
I have a table of holdings, so a one to many arrangement.
The main form holds the fund name and value, it ID field links it to the holding table, which feeds two subforms (two investors) showing the quantity of shares held.

What I am trying to achieve is an unbound calculated field, which could be on either main or subform, which is the sum of main form's txtValue and each subform's txtQuantity.
Whatever type of cross reference I have tried, all I see is "#Name?"

Please can some-one help?

Telephoto

 

[Scott24x7]

telephoto,
I don't know a great deal about Access, but I've done a lot of data base (VFP in particular) similar things.
First, if the txtValue and txtQuantity values are "numbers saved as text" you'll need to convert them to some numeric value so they can be added together.
Then you'll need to do something where you get the mainform's txtValue value, and sum the matching child records txtQuantity. You'll need a loop for that something like:

   lnAccumulator = 0
   WHILE childtable.ID = parenttable.ID
      lnAccumulator = lnAccumulator + childtable.txtQuantity
      <next record>
   ENDWHILE
   lnAccumulator = lnAccumulator + parent.txtValue

Now you have a value for lnAccumulator, in this case it seems like you'd want to keep that at the parent level, and not at the child level so assign it to a field you added like:

parenttable.txtTotal = lnAccumulator

And of course, you have to manage the data types, numeric versus character, but fundamentally, I think this is what you're after?


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[telephoto]

Thanks for the reply Scott

I think are thinking two different ideas.

Both fields are numeric.
The main form shows one fund/share at a time, when it opens I want to see a textbox showing price x quantity (total value of that investment).
When I update the price I want the textbox to update, if I update the quantity I want an update.
I am looking for a way to reference the subform textbox from the main form and vice versa, so that I can use the after update action to recalculate the unbound textbox.

Main form and subform system is the only way I can see to update the table without resorting to a whole load of VBA.

T

 

[Scott24x7]

If your data is in the tables, you don't need to "access it from the text boxes". You already have a relationship, just perform the calculation you need from the data in the tables, and then if you don't want to store it, but display it on the form instead, then create a textbox, and display the value in it...
So I'm clear... the mainform has a textbox, but that box is getting its data populated from the table. Subform has a textbox that is also getting its value populated from the child table. Don't muck with trying to get data out of the form, just perform your operation on values you retrieve from your table directly, and then place that on the form. That won't require "a whole load of VBA".


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[dhookom]

It's not clear how many calculated values you expect to see. Is it one value per record in the subform(s) or just a total? Are txtValue and txtQuantity the names of text boxes or fields?

I would use only fields in the calculation. You can add a text box anywhere and use DSum() to get your total. I would never use code for something like this.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[telephoto]

Hi Scott - not quite.

Duane, long time no speak, Switzerland? Moved or holiday?

Apologies for delay, whoever said retirement gives you more time was a liar.

The tables hold the last known values and quantities of the investment, these are displayed on the main and sub forms.
When the value is checked it is entered in an unbound textbox "txtNewVal"
For some investments where an income is taken the total quantity of investment units is reduced, so the subform quantity figures also need to be altered (also into an unbound textbox).
What Madam would like is a temporary display of the new number of units multiplied by the new value. If this gives the correct total it proves no finger trouble and the overall value can be seen to rise or fall. The tables can be updated by switching values into the other textboxes (linked to the tables) when next record is clicked.

I appreciate this approach may not be textbook(!) but the initial display is very straightforward in concept.
One thought I have had is to set the txtNewVal as a global variable, then can I set focus on the subform and do the multiplication there, then step to the other subform and do that one? would that be as simple as "docmd subformA.setfocus"?
(I'm not at the computer with the db at the moment, but I'll try it as soon as I can).

Thanks both

T

 

[dhookom]

I've been working in Switzerland for over a year. Not a bad gig.

I am having trouble understanding your situation...
So there is one fund record in the main form. Let's assume there is a primary key FundID.
There are subforms (one named sfrmHoldings) on the main form that display records from the holdings that are linked based on the FundId. The holdings table can have one or more records with the same FundID and each of these records has a [QuantityOfShares] field.

There is a control [txtValue] on the main form that you would like to enter a value and someplace see a calculation of the Sum([QuantityOfShares]) * [txtValue].

Is this correct?

You would start by adding a text box in the subform header or footer:
Name: txtSumQty
Control Source: =Sum([QuantityOfShares])

Then add a text box on the main form with a control source of:

=[txtValue]*[sfrmHoldings].[Form].[txtSumQty]

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[telephoto]

Hi Duane

That was exactly the expression I had been trying to work out.
I believe my problem now is lack of day to day Access involvement leading to trying to over-complicate references
(EG confusion between using {Form}, or its full address via Forms, or just Me! )

This worked straight off the page, thank you.

T

(Totally off topic - you lucky devil, you are living a stone's throw away from some fantastic scenery, some of my favourite in fact).

 

[dhookom]

telephoto,

Good to hear you worked it out.

OT: yes the area is nice. Probably heading up to 3,300 meters on Saturday to hike and take in the scenery.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016