FORM Calculations. Please Help!!!!

[AccessFitz]

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])

 

[AccessFitz]

Dirk,

Thanks for your response. Let me explain a little more. The Link # groups
the trades together. Each trade will have a Gain/Loss. The Total Gain/Loss
should be the net of the Gain/Loss per Link #.

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

Link #3 Gain/Loss: $10.00 Total Gain/Loss:
$9.00
Link #3 Gain/Loss: $-1.00 Total Gain/Loss:
$9.00

What I'm hoping to do is, a calculation is built into the Gain/Loss. If I
change other fields in the Gain/Loss calculations, is it possible for the
Total Gain/Loss to change? Keep in mind that the Link# groups the trades,
therefore creating the Total Gain/Loss calculation? Is this possible?

Thanks
AccessFitz

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])

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

(please reply to the newsgroup)

 

[AccessFitz]

Dirk,

I was trying some code that goes something like this.

SELECT [Trade Information Table].[Link #], Sum([Gain/Loss])

Dirk,

Thanks for your response. Let me explain a little more. The Link # groups
the trades together. Each trade will have a Gain/Loss. The Total Gain/Loss
should be the net of the Gain/Loss per Link #.

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

Link #3 Gain/Loss: $10.00 Total Gain/Loss:
$9.00
Link #3 Gain/Loss: $-1.00 Total Gain/Loss:
$9.00

What I'm hoping to do is, a calculation is built into the Gain/Loss. If I
change other fields in the Gain/Loss calculations, is it possible for the
Total Gain/Loss to change? Keep in mind that the Link# groups the trades,
therefore creating the Total Gain/Loss calculation? Is this possible?

Thanks
AccessFitz

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])

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

Thanks for your response. Let me explain a little more. The Link #
groups the trades together. Each trade will have a Gain/Loss. The
Total Gain/Loss should be the net of the Gain/Loss per Link #.

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

Link #3 Gain/Loss: $10.00 Total
Gain/Loss: $9.00
Link #3 Gain/Loss: $-1.00 Total
Gain/Loss: $9.00

What I'm hoping to do is, a calculation is built into the Gain/Loss.
If I change other fields in the Gain/Loss calculations, is it
possible for the Total Gain/Loss to change? Keep in mind that the
Link# groups the trades, therefore creating the Total Gain/Loss
calculation? Is this possible?

If I understand you properly, it's possible without too much trouble,
but I think it's going to involve a secondary query (which could be
expressed as a subquery, a derived table, or even a DSum expression).
For more specific advice, I need a bit more information.

Does the resulting recordset have to be updatable, except for the
calculated fields?

Am I right in understanding that the [Gain/Loss] field is itself a
calculated field? Is it calculated in the query, or by a controlsource
expression?

If the form's recordsource is a query, please post the SQL of the query.
If it's a table, please post the details of the relevant fields.

 

[AccessFitz]

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.
Thanks
AccessFitz

If I understand you properly, it's possible without too much trouble,
but I think it's going to involve a secondary query (which could be
expressed as a subquery, a derived table, or even a DSum expression).
For more specific advice, I need a bit more information.

Does the resulting recordset have to be updatable, except for the
calculated fields?

Am I right in understanding that the [Gain/Loss] field is itself a
calculated field? Is it calculated in the query, or by a controlsource
expression?

If the form's recordsource is a query, please post the SQL of the query.
If it's a table, please post the details of the relevant fields.

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

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.

 

[AccessFitz]

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)

 

[AccessFitz]

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)