Solved

Access 2003:  Report - Group Totals - a diff. approach - getting error

Posted on 2011-03-23
12
332 Views
Last Modified: 2012-05-11
Hi EE,

Can group fields on the report be affected with vba logic?

I tried to total the credits during detail process by accumulating to a public variable.
If total > 0, print the student's academic standing field
If total = 0, print 'IN PROGRESS"


In the group's print event  and even the format event tried this, and am getting an error:

Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As Integer)

Debug.Print pg_lngTotCredits

If pg_lngTotCredits = 0 Then
   Me.txtTermStanding = "IN PROGRESS"
End If

pg_lngTotCredits = 0


End Sub


tx for your ideas and advice, sandra
0
Comment
Question by:mytfein
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
12 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 35201987
Sandra,

<<Can group fields on the report be affected with vba logic?>>

  Anything that alters controls should be in the OnFormat event.  Also, when totalling yourself, you need to protect the calculations like this:

  If FormatCount = 1 then
     ' Do Totalling.
  End If

  Often, the print engine will retreat up the page to re-format it.  Detail and other sections OnFormat event may get fired multiple times before they are printed.  Ditto for OnPrint, which is PrintCount.

 And this is code, but you need to pull in the field:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)


If FormatCount = 1 then
Debug.Print pg_lngTotCredits
If pg_lngTotCredits = 0 Then
   Me.txtTermStanding = "IN PROGRESS"
Else
   Me.txtTermStanding = <some other control which is hidden with the field data required>
End If

pg_lngTotCredits = 0
End if


End Sub

  txtTermStanding should be an un-bound field.    

HTH,
JimD.
0
 

Author Comment

by:mytfein
ID: 35202013
Hi Jim,

tx for writing...

now i understand my error, i was trying to change a bound field

ok, let me get back to you ...  tx, s
0
 

Author Comment

by:mytfein
ID: 35202462
Hi Jim,

i understand your ideas

however, when i go to subreport2  to add a hidden text field to the group footer
and then hit save,  Access aborts....

uploaded mdb below   ... is it possible for you to add your ideas please, i'm curious if you also get
an aborted situation or out of memory msg

tx, s
db2.zip
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 58
ID: 35202722

 No problem adding a text control to the footer here.

JimD.
0
 

Author Comment

by:mytfein
ID: 35208426
tx for checking Jim..... s
0
 

Author Comment

by:mytfein
ID: 35208759
Hi Jim,

it works now... tx... so much....ss
2011-03-25-rpt.GIF
0
 

Author Comment

by:mytfein
ID: 35208863
Hi Jim,

Just wondering if you would know why, if in the above example
   there are 3 groups,
   
   yet the group format event got triggered 5 times
         
   i'm wondering if access first prints out the detail
       and afterwards superimposes the group logic

 (  i come from a diff. era as a cobol pgmr where first we process the detail records, and when
          there was a change in key, we executed group footer/control break logic)

    the end result looks great, just wondering about the 2 extra breaks....

    if you have any thoughts, pls advise, tx, s
0
 
LVL 58
ID: 35208893
Sandra,

<<Just wondering if you would know why, if in the above example
   there are 3 groups,
   
   yet the group format event got triggered 5 times
>>

  It's what I was refering to above in my first comment.  Based on properties, like Keep together, the report engine may need to retreat up the page and reformat it again.  This can happen several times in the course of trying to figure out the page layout honoring (if it can) all the properties that have been set.

  This can happen for the detail as well as any of the grouping levels.  That's why both OnFormat and OnPrint events have counts (FormatCount and PrintCount).

JimD.
0
 

Author Comment

by:mytfein
ID: 35209074
Thx Jim,

a) i understand what you said earlier and above...
i guess i was not so clear in my question....

     when the group even got triggered 5 times,  the If formatCount =1  logic was true 5 times,
as well too.

    so if the formatCount is equal to 1 5 times, when is formatCount = to another value

b) this report is a subreport that acts as column2
the preview screen above prints that way too

the curious thing, is that when i run the entire parent report that has a subreport1 to act as column1

on the col2 report,  the 3rd grouping is sometimes not showing the horizontal line that i put in
the footer column when IT PRINTS, but the line shows in print preview....

i never noticed that before.... so i commented out the group footer logic, but the 3rd line separator
is still not showing

this should probably be a new post....  just venting... to see if you have any opinions on this matter,
tx, s
0
 

Author Comment

by:mytfein
ID: 35209155
Hi Jim,

i even deleted col2's subreport, copies col1's subreport, and it still does not print the right side
3rd goup's separator line......

it would take a little time for me to create test data out of this real student and then i could upload
the mdb....

tx, s
0
 

Author Comment

by:mytfein
ID: 35209177
Hi Jim,

i see the problem in this test mdb
    i would think that the last group would end with a separator line that i drew in the footer
          but is not showing for the last group


i will open a new post....
and write back with the link, tx, s
db2.zip
0
 

Author Comment

by:mytfein
ID: 35209527
Hi Jim,

you don't have to spend time on the line issue....

an EE expert just explained it to me here....

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26909493.html

tx again, s
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question