By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,564 Members | 810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,564 IT Pros & Developers. It's quick & easy.

Include TOTALS in a subform

P: n/a
I have a subform that displays around 10 fields. Most of the values shown
result from some complex calculations. To make it easy, I have stored the
results in a temporary table and then all I need to do is set the
recordsource of the subform equal to the table.

However, it would be very nice if the last row in the subform showed the
totals of the previous rows (of course where applicable). If it were a
listbox (that didn't require scrolling) I would normally include textboxes
below the listbox, which would contain the totals. However, the reason I
used a subform is because horizontal scrolling is necessary and the user may
want to fix the left-hand columns as they scroll.

So what's the best way to include totals in the subform. They must be
displayed at the bottom. I could fill the table with a row that has the
total values. One of the fields could be set to have a value of 'TOTAL'.
So, maybe all I need to do is order the results such that 'TOTAL' is the last
row in the ordered results.

Thanks for any help.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

Oct 13 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
On Fri, 13 Oct 2006 13:44:12 GMT, "rdemyan via AccessMonster.com"
<u6836@uwewrote:

Put the text boxes in the Footer of the Subform, and set the
ControlSource to something like:
=Sum([Price])

-Tom.

>I have a subform that displays around 10 fields. Most of the values shown
result from some complex calculations. To make it easy, I have stored the
results in a temporary table and then all I need to do is set the
recordsource of the subform equal to the table.

However, it would be very nice if the last row in the subform showed the
totals of the previous rows (of course where applicable). If it were a
listbox (that didn't require scrolling) I would normally include textboxes
below the listbox, which would contain the totals. However, the reason I
used a subform is because horizontal scrolling is necessary and the user may
want to fix the left-hand columns as they scroll.

So what's the best way to include totals in the subform. They must be
displayed at the bottom. I could fill the table with a row that has the
total values. One of the fields could be set to have a value of 'TOTAL'.
So, maybe all I need to do is order the results such that 'TOTAL' is the last
row in the ordered results.

Thanks for any help.
Oct 13 '06 #2

P: n/a

"rdemyan via AccessMonster.com" <u6836@uweschreef in bericht news:67b47de96b111@uwe...
So what's the best way to include totals in the subform. They must be
displayed at the bottom. I could fill the table with a row that has the
total values. One of the fields could be set to have a value of 'TOTAL'.
So, maybe all I need to do is order the results such that 'TOTAL' is the last
row in the ordered results.
Would be easy enough to include a field 'sortorder' in your temptable.
Sortorder = 0 for all records except the last one where sortorder = 1
Then sort the subform(query) on sortorder AND your other sorting fields.

But what if there are many records in the subform?
The row with the totals won't be visible *always*

I would include textboxes on the mainform just as you would do with listboxes.

Arno R
Oct 13 '06 #3

P: n/a
Ok. Got that to work.

How can I highlight the TOTAL row so that it is clear to the user that it
contains the totals of the rows above?

Thanks.

Arno R wrote:
>
>So what's the best way to include totals in the subform. They must be
displayed at the bottom. I could fill the table with a row that has the
total values. One of the fields could be set to have a value of 'TOTAL'.
So, maybe all I need to do is order the results such that 'TOTAL' is the last
row in the ordered results.

Would be easy enough to include a field 'sortorder' in your temptable.
Sortorder = 0 for all records except the last one where sortorder = 1
Then sort the subform(query) on sortorder AND your other sorting fields.

But what if there are many records in the subform?
The row with the totals won't be visible *always*

I would include textboxes on the mainform just as you would do with listboxes.

Arno R
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

Oct 13 '06 #4

P: n/a
Except how do you handle the totals when the sub form is horizontally
scrolled?
Arno R wrote:
>
But what if there are many records in the subform?
The row with the totals won't be visible *always*

I would include textboxes on the mainform just as you would do with listboxes.

Arno R
--
Message posted via http://www.accessmonster.com

Oct 13 '06 #5

P: n/a

"rdemyan via AccessMonster.com" <u6836@uweschreef in bericht news:67b4fe7fb25dc@uwe...
Ok. Got that to work.

How can I highlight the TOTAL row so that it is clear to the user that it
contains the totals of the rows above?

If you have A2k or better you could use conditional format

Arno R
Oct 13 '06 #6

P: n/a

"rdemyan via AccessMonster.com" <u6836@uweschreef in bericht news:67b5ceeb9ead0@uwe...
Except how do you handle the totals when the sub form is horizontally
scrolled?
Arno R wrote:
>>
But what if there are many records in the subform?
The row with the totals won't be visible *always*

I would include textboxes on the mainform just as you would do with listboxes.

Arno R
I would handle that the same as I handle this post:
I mean: Topposting is hard to follow....
==Look carefully what is meant ;-)

No kidding:
If the users HAVE to scroll horizontally then you would carefully look for 'room' on the mainform.
Put all the total-controls in a place where it is obvious what is meant.
BUT since this is NOT so obvious I guess I would try to avoid the horizontal scroll !
Try to make the controls in the subform smaller so that they 'fit' or ....
Make 2 (or 3) subforms instead of one and place the totals the 'usual way'.

Arno R
Oct 13 '06 #7

P: n/a
Thanks.

I've heard of this and have done a bit of research, but can't figure out how
to implement it for a row that is not the selected one.

Arno R wrote:
>Ok. Got that to work.

How can I highlight the TOTAL row so that it is clear to the user that it
contains the totals of the rows above?

If you have A2k or better you could use conditional format

Arno R
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

Oct 13 '06 #8

P: n/a

"rdemyan via AccessMonster.com" <u6836@uweschreef in bericht news:67b7ec3faaad7@uwe...
Thanks.

I've heard of this and have done a bit of research, but can't figure out how
to implement it for a row that is not the selected one.
No need to select the row.
Do some more research and look at the help.

If you used the sortorder 'trick' I gave you then use something like:
Use Expression is [SortOrder]=1 and highlight the field (forecolor, backcolor, fontbold, whatever)

Arno R
Oct 13 '06 #9

P: n/a
I'm having trouble using the following for the formatting:

Dim fcd As FormatCondition
With Me.CalcsBreakdownSubForm.Form!USER_TITLE
With .FormatConditions
.Delete
Set fcd = .Add(acExpression, acEqual, "[USER_TITLE]", "TOTAL")
fcd.BackColor = vbRed
End With
End With

This works if I use acFieldValue, but it formats the column. What I want is
the row. Note that right now I'm just testing this so I'm setting the
backcolor to red.

Thanks for any help.

--
Message posted via http://www.accessmonster.com

Oct 13 '06 #10

P: n/a
Never mind. No need to reply. I axed the code and used the menu bar
Conditional Formatting and was able to get it to work.

rdemyan wrote:
>I'm having trouble using the following for the formatting:

Dim fcd As FormatCondition
With Me.CalcsBreakdownSubForm.Form!USER_TITLE
With .FormatConditions
.Delete
Set fcd = .Add(acExpression, acEqual, "[USER_TITLE]", "TOTAL")
fcd.BackColor = vbRed
End With
End With

This works if I use acFieldValue, but it formats the column. What I want is
the row. Note that right now I'm just testing this so I'm setting the
backcolor to red.

Thanks for any help.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

Oct 13 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.