FORM Calculations. Please Help!!!!

[Guest]

Hi,

I'm building a form ontop of a table. I have three fields
1. Link #
2. Gain/Loss
3. Total Gain/Loss.

For Example
Link # 1 Gain/Loss: $5.00 Total Gain/Loss: $1.00
Link #1 Gain/Loss: $-4.00 Total Gain/Loss: $1.00

I have built a calculation into the Gain/Loss. If I change another field
within the form, the Gain/Loss will change. What I'm having problems with
is, how do I get the Total Gain/Loss to change if the Gain/Loss changes per
that Link#?

Thanks in advance
AccessFitz

 

[Dirk Goldgar]

Hi,

I'm building a form ontop of a table. I have three fields
1. Link #
2. Gain/Loss
3. Total Gain/Loss.

For Example
Link # 1 Gain/Loss: $5.00 Total
Gain/Loss: $1.00 Link #1 Gain/Loss: $-4.00
Total Gain/Loss: $1.00

I have built a calculation into the Gain/Loss. If I change another
field within the form, the Gain/Loss will change. What I'm having
problems with is, how do I get the Total Gain/Loss to change if the
Gain/Loss changes per that Link#?

Is [Total Gain/Loss] supposed to be the sum of all the calculated
[Gain/Loss] values for all the records on the form? If so, then you
need to repeat the expression that calculates [Gain/Loss] inside the
Sum() function in the ControlSource expression of [Total Gain/Loss];
something like this example:

[Gain/Loss] ControlSource: = [Price]-[Cost]

[Total Gain/Loss] ControlSource: = Sum([Price]-[Cost])

 

[Guest]

Dirk,

I'm trying to do the same thing on another form. This form is just a little
different. Instead of group the trades by Link #. I want to group them by
BIN(Broker Identification Number). I've copied the form and changed the
control source of the Total Gain/Loss to =DSum("((([Trade Date NAV]-([Corr
Date NAV]+[Dist Rate]))*[Shares]))","W0005 Table","[BIN]=" & [BIN]).

When I click on Form view, I get the #Error message in the fields. Do you
know what I need to do to correct this?

Dirk,

Works like a charm... Thank You very much...

Dirk,

I would like to have the recordset updatable. I tried building a
query, but as soon as you establish a relationship between the two,
you can't change any fields.

The Gain/Loss is a calculation within the the text box in the form.
I can build in the SQL if need be. The formual is =((([Trade Date
NAV]-([Corr Date NAV]+[Dist Rate]))*[Shares])). This calculation is
fine. When I change a NAV, the Gain/Loss will change. I just can't
seem to get the Total Gain/Loss to change as well. Remember the
Link# groups the trades together.

The Forms recordsource is a table called Trade Information Table.
The form that I am building is an exact replica of the Trade
Information Table. The specs on the table are Trade Date NAV, Corr
Date NAV, Dist Rate, Shares, Gain/Loss, Total Gain/Loss, and Link #.

Let me know if you need additional information.

It would be nice to compute the grouped sum in the query, and we could
do it that way if you didn't need the form to be updatable.
Unfortunately, once you include any aggregate function in a query, even
in a subquery, the Jet database engine makes the whole query
nonupdatable. So we're stuck with doing it in a controlsource
expression.

You can create a text box for [Total Gain/Loss] and set its
controlsource to this expression:

=DSum("(([Trade Date NAV]-([Corr Date NAV]+[Dist
Rate]))*[Shares])","Trade Information Table","[Link #]=" & [Link #])

Note that the above expression was all entered on one line, though it
will have been broken to multiple lines by the newsreader.

Even with that controlsource expression in place, you'll need to add
code in the AfterUpdate event of the form to recalculate the text box.
It might look like:

Private Sub Form_AfterUpdate()
Me.Recalc
End Sub

You may need a similar line of code for the form's AfterDelConfirm
event. When you change the base values in the current record, the text
box won't reflect the changes until the current record is saved.

Note. by the way, the your table shouldn't have fields for "Gain/Loss"
or "Total Gain/Loss", because these are calculated fields that are
wholly dependent on other fields in the table.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)