Cutting to the chase...

I can't get totals to display conveniently on a transactions form that has 
tab pages with subforms in continuous form view.   One tab page has a subfo
rm that shows all the elements, but I can't seem to get it to sum them.

Everything I've tried on the sub form ends up showing an error or nothing a
t all.

While I can pop up a form with just a text box with a correct total in it, 
that's an ungainly solution.

The summary tab page shows the details for a given transaction -
example:

2 prints x $100.00 x +1 sale			+$200
1 painting x $200.00 x +1 sale			+$200
1 check x $400.00 x -1 receipt			-$400

I want to show the $0.00 balance	 on that page	=3D$0

I want updates to show up automatically.

Any ideas?

Charlie

Hi Charlie, this should be quite easy to do, I've just totalled some
textboxes on tab pages without any problem, so tell us more...

"I can pop up a form with just a text box with a correct total in it" - how
do you arrive at that correct total? this might give us a clue.

Regards, Wesley Kendrick

----- Original Message -----
From: "Charlie Goodwin" <cgoodwin@c...>
To: "Access" <access@p...>
Sent: Tuesday, October 22, 2002 9:55 PM
Subject: [access] Help, I'm still totally stumped


Cutting to the chase...

I can't get totals to display conveniently on a transactions form that has
tab pages with subforms in continuous form view.   One tab page has a
subform that shows all the elements, but I can't seem to get it to sum them.

Everything I've tried on the sub form ends up showing an error or nothing at
all.

While I can pop up a form with just a text box with a correct total in it,
that's an ungainly solution.

The summary tab page shows the details for a given transaction -
example:

2 prints x $100.00 x +1 sale +$200
1 painting x $200.00 x +1 sale +$200
1 check x $400.00 x -1 receipt -$400

I want to show the $0.00 balance on that page =$0

I want updates to show up automatically.

Any ideas?

Charlie

In more detail...

? I wonder if the issue is from the fact that several tables get
accessed to obtain a sum.?  But, back to the facts as best I can
order them...

I have a transactions "main" form that has tab page controls with
subforms in continuous form view.

The main form records the contact customer or supplier and a date for
a transaction.   The subforms work with different transaction types;
transactions with art items on one, supplimentary framing items on
another, payments on a third and so on, all on tab control pages.
I have a summary tab page that shows all the elements, but I can't
seem to get it to sum them.

To get very specific, I keep the details showing only in the correct
sub forms by adding a transaction type field that only certain subs
and combos can "see" through their source queries.   A framing
detail of a transaction will only show in the framing sub, because it's
query "passes" that record, and in the summary sub because it;s
source query passes all the related details from that transaction.

This part works like a charm, but is my explanation making any
sense so far?

My issue is on the summary subform that looks at all the details.

I would like to be able to display an up to date total for the current
transaction record.  Everything I've tried on the form ends up showing
an error or nothing at all.

While I can get a correct sum to show up with a separate form that pops
up with just a text box total, that's an ungainly solution.

I based that form on a query that looks at all the details of the transacti
on record.   It just outputs a sum.   The total is the sum of ([NoOfUnits]*
[Price]*[Factor]) for each element of
a transaction.   (Field names are changed to protect the innocent)   The Fa
ctor is just a +1, 0 or -1 transaction type factor that just accounts for w
hether value is outgoing, not billable, or incoming.

THe SQL for the query with all it's ugly names is 

SELECT Sum([NoOfUnits]*[Price]*[InvTranFactor]) AS Tot
FROM TblInvValTranTypes INNER JOIN (TblInv INNER JOIN TblTransInvMatch 
ON TblInv.InvID =3D TblTransInvMatch.InvID) 
ON TblInvValTranTypes.InvTranTypeID =3D TblTransInvMatch.InvTranTypeID
WHERE (((TransID)=3D[Forms]![FrmACosTran]![TxtTransID]))
WITH OWNERACCESS OPTION;

I just pop up the form with a text box set to =3DFormat(Tot, (Currency")

Sorry about the ugly table and field names...


Example:
2 prints sold at $100.00, a painting at $200.00
and an incoming payment of  $400.00

The summary tab page correctly shows the details for a given transaction

2 prints x $100.00 x +1 sale			+$200
1 painting x $200.00 x +1 sale			+$200
1 check x $400.00 x -1 receipt			-$400

I want to show the $0.00 balance			=3D$0

The essential parts come from four tables:

1. Everything starts at a transaction table that lists client and date.
2. The item transacted, NoOfUnits and transaction type come from
	a details table matching the one transaction to many
	transaction details
3. Price comes from a table of transactable items.
4. Factor comes from a lookup table that relates transaction type to factor

Any ideas?

Charlie





> Hi Charlie, this should be quite easy to do, I've just totalled some
> textboxes on tab pages without any problem, so tell us more...
>
> "I can pop up a form with just a text box with a correct total in it" - h
ow
> do you arrive at that correct total? this might give us a clue.
>
> Regards, Wesley Kendrick
> 

Charlie,

What I have done in the past to rectify this problem is to put the sum
in the Form Footer on the subform.  In the form footer, add a text box
called txtTotal or something like that.  The formula for the Control
Source would then be =Sum([txtItemTotal]), assuming txtItemTotal is the
total of each line item, like +200, +200, -400.  

If your total must be on master form, then let me know, I'm sure it can
be done.  You could put a text box on your master form and set it's
control source to the subform's text box, like
Forms!MasterForm!subMySubform!txtTotal.

Try it out.  I hope that helps.

John

-----Original Message-----
From: Charlie Goodwin [mailto:cgoodwin@c...] 
Sent: Tuesday, October 22, 2002 2:55 PM
To: Access
Subject: [access] Help, I'm still totally stumped

Cutting to the chase...

I can't get totals to display conveniently on a transactions form that
has tab pages with subforms in continuous form view.   One tab page has
a subform that shows all the elements, but I can't seem to get it to sum
them.

Everything I've tried on the sub form ends up showing an error or
nothing at all.

While I can pop up a form with just a text box with a correct total in
it, that's an ungainly solution.

The summary tab page shows the details for a given transaction -
example:

2 prints x $100.00 x +1 sale			+$200
1 painting x $200.00 x +1 sale			+$200
1 check x $400.00 x -1 receipt			-$400

I want to show the $0.00 balance	 on that page	=$0

I want updates to show up automatically.

Any ideas?

Charlie

Hi again Charlie

Right - a bit difficult to get your head around all that at this time of
night, but lets keep it simple.

The text box on your popup form - have you tried creating an exact copy on
the subform? if so what happens?
If you get nothing in this textbox it may be because there is nothing to
trigger the query - your popup form triggers the query when it opens, but a
text box on the subform might just be sitting there waiting for something to
happen.  You may need to trigger the query by putting code in the other
textboxes 'On Change' Event for instance.

However, I still dont understand why a simple calculation typed into the
total textbox shouldn't do the trick.

To take your example below,
"The summary tab page correctly shows the details for a given transaction

2 prints x $100.00 x +1 sale +$200
1 painting x $200.00 x +1 sale +$200
1 check x $400.00 x -1 receipt -$400

I want to show the $0.00 balance =$0"

and using fictitious names...

2printsTextBox  contains       $200
1paintingTextBox contains     $200
1checkTextBox contains        $400

balanceTextBox containing calculation      '=2printsTextBox +
1paintingTextBox - 1checkTextBox' should show  $0

any of this help?

Regards, Wesley Kendrick



----- Original Message -----
From: "Charlie Goodwin" <cgoodwin@c...>
To: "Access" <access@p...>
Sent: Tuesday, October 22, 2002 11:15 PM
Subject: [access] Re: Help, I'm still totally stumped


In more detail...

? I wonder if the issue is from the fact that several tables get
accessed to obtain a sum.?  But, back to the facts as best I can
order them...

I have a transactions "main" form that has tab page controls with
subforms in continuous form view.

The main form records the contact customer or supplier and a date for
a transaction.   The subforms work with different transaction types;
transactions with art items on one, supplimentary framing items on
another, payments on a third and so on, all on tab control pages.
I have a summary tab page that shows all the elements, but I can't
seem to get it to sum them.

To get very specific, I keep the details showing only in the correct
sub forms by adding a transaction type field that only certain subs
and combos can "see" through their source queries.   A framing
detail of a transaction will only show in the framing sub, because it's
query "passes" that record, and in the summary sub because it;s
source query passes all the related details from that transaction.

This part works like a charm, but is my explanation making any
sense so far?

My issue is on the summary subform that looks at all the details.

I would like to be able to display an up to date total for the current
transaction record.  Everything I've tried on the form ends up showing
an error or nothing at all.

While I can get a correct sum to show up with a separate form that pops
up with just a text box total, that's an ungainly solution.

I based that form on a query that looks at all the details of the
transaction record.   It just outputs a sum.   The total is the sum of
([NoOfUnits]*[Price]*[Factor]) for each element of
a transaction.   (Field names are changed to protect the innocent)   The
Factor is just a +1, 0 or -1 transaction type factor that just accounts for
whether value is outgoing, not billable, or incoming.

THe SQL for the query with all it's ugly names is

SELECT Sum([NoOfUnits]*[Price]*[InvTranFactor]) AS Tot
FROM TblInvValTranTypes INNER JOIN (TblInv INNER JOIN TblTransInvMatch
ON TblInv.InvID = TblTransInvMatch.InvID)
ON TblInvValTranTypes.InvTranTypeID = TblTransInvMatch.InvTranTypeID
WHERE (((TransID)=[Forms]![FrmACosTran]![TxtTransID]))
WITH OWNERACCESS OPTION;

I just pop up the form with a text box set to =Format(Tot, (Currency")

Sorry about the ugly table and field names...


Example:
2 prints sold at $100.00, a painting at $200.00
and an incoming payment of  $400.00

The summary tab page correctly shows the details for a given transaction

2 prints x $100.00 x +1 sale +$200
1 painting x $200.00 x +1 sale +$200
1 check x $400.00 x -1 receipt -$400

I want to show the $0.00 balance =$0

The essential parts come from four tables:

1. Everything starts at a transaction table that lists client and date.
2. The item transacted, NoOfUnits and transaction type come from
a details table matching the one transaction to many
transaction details
3. Price comes from a table of transactable items.
4. Factor comes from a lookup table that relates transaction type to factor

Any ideas?

Charlie





> Hi Charlie, this should be quite easy to do, I've just totalled some
> textboxes on tab pages without any problem, so tell us more...
>
> "I can pop up a form with just a text box with a correct total in it" -
how
> do you arrive at that correct total? this might give us a clue.
>
> Regards, Wesley Kendrick
>

Thanks all!!!

With your help, it's working.

I found a couple of strange things when I went in to add the text box you s
poke of.   No luck yet... it still didn't work.   For reasons too involved 
to go into here, I had been till now working with expressions such as   con
trolXXX.column(2) * controlABC.column(1) * ControlXYZ.column(3) etc. to yie
ld the item amount in a given detail.  The subform footer just didn't like 
messing about with summing from items like that.

So,I tried adding a field to the query underlying the summary sub that woul
d offer up a total for that detail.   Then I added a textbox bound to that 
- summing on the new simpler expression.   That worked, but I was getting s
trange numbers with many digits cropping up rather that the usual $x.xx cur
rency format.   I sometimes think my machine is posessed.  I went back to a
ll the source tables and got rid of oddball numbers.   Now it works just be
autifully!.

Thanks again!

Charlie






> Charlie,
>
> What I have done in the past to rectify this problem is to put the sum
> in the Form Footer on the subform.  In the form footer, add a text box
> called txtTotal or something like that.  The formula for the Control
> Source would then be =3DSum([txtItemTotal]), assuming txtItemTotal is the
> total of each line item, like +200, +200, -400.
>
> If your total must be on master form, then let me know, I'm sure it can
> be done.  You could put a text box on your master form and set it's
> control source to the subform's text box, like
> Forms!MasterForm!subMySubform!txtTotal.
>
> Try it out.  I hope that helps.
>
> John
>
> -----Original Message-----
> From: Charlie Goodwin [cgoodwin@c...]
> Sent: Tuesday, October 22, 2002 2:55 PM
> To: Access
> Subject: [access] Help, I'm still totally stumped
>
> Cutting to the chase...
>
> I can't get totals to display conveniently on a transactions form that
> has tab pages with subforms in continuous form view.   One tab page has
> a subform that shows all the elements, but I can't seem to get it to sum
> them.
>
> Everything I've tried on the sub form ends up showing an error or
> nothing at all.
>
> While I can pop up a form with just a text box with a correct total in
> it, that's an ungainly solution.
>
> The summary tab page shows the details for a given transaction -
> example:
>
> 2 prints x $100.00 x +1 sale			+$200
> 1 painting x $200.00 x +1 sale			+$200
> 1 check x $400.00 x -1 receipt			-$400
>
> I want to show the $0.00 balance	 on that page	=3D$0
>
> I want updates to show up automatically.
>
> Any ideas?
>
> Charlie
>
>
>

Thanks to all!

With the information that a simple solution was nearby, I got a simple tota
l working.   Then it made sense to try to express the value so it would be 
obvious what a ($92.00) or a $92 sum really means. so I put the following i
n a text box:

=3DSwitch(Sum([Toter])=3D0,"Balance is zero dollars on this transaction",
Sum([Toter])<0,[Forms]![FrmACosTran]![CboTransPandO].Column(1)
& " owes me " & Format(Abs(Sum([toter])),"Currency") & " on this transactio
n",Sum([Toter])>0,"I owe " & [Forms]![FrmACosTran]![CboTransPandO].Column(1
) & "  " & Format(Abs(Sum([toter])),"Currency") & " on this transaction")

Sum([Toter])
is the sum of the detail items

[Forms]![FrmACosTran]![CboTransPandO].Column(1)
pulls a full concatenated name out of a handy combo box

Once I have a balance for the whole transaction, it gets shown on
the form something like:

"I owe Joe Smith $1000.00 on this transaction"
or
"Smith Gallery owes me $1000.00 on this transaction"
or
"Balance is zero dollars on this transaction"

..depending on whether Sum([Toter]) is a positive nember, a negative numbe
r,
or zero.

The expression under the textbox isn't  very elegant looking, but it works 
like a charm.

Thanks to all on getting me headed in the right direction!

Charlie