Solved

Formatting group totals in Access

Posted on 2015-02-18
9
125 Views
Last Modified: 2016-02-11
Gentlemen:

Hope this will be easy points for someone.  

I have attached a PDF of the first page of a "sanitized" report.  I am getting a total for a group of accounts...  That's working correctly; however, when there is only ONE account in a grouping code I just want to print the detail, and NOT the automatic sub-total.  Also, a minor point....  Would like an underline at the appropriate location....  

See the attached PDF for the first page of the report that shows what I need.

Any and all assistance is greatly appreciated.

Thanks,
Rick Norris+AccessReport.pdf
0
Comment
Question by:Rick Norris
  • 5
  • 2
  • 2
9 Comments
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
Add a counter field to the detail section.  It can be hidden.  Its controlsource should be:
=1
It's group by property should be over group.
Add the same control to the total section.
Set the CanGrow/CanShrink properties of the total section to yes.
In the Format event of the total section, check the value of the counter.  If it is greater than 1, hide the Underline, the label, and the total field.  With nothing visible to print, Access will "shrink" the total section.
0
 

Author Comment

by:Rick Norris
Comment Utility
Pat:

Understand above verbiage; however used the below formula in the "on Format" event in the total section....  Must be doing something wrong....  totals still show, and the value of Const1 on the total line is ONE.

Comments please.


=IIf([Const1]=1,[2013BALANCE].[Visible]=False,"")
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I don't believe you can affect the state of a control this way.  You need to write VBA in the Format event for the section

If Me.Const1 = 1 Then
    Me.2013Balance.Visible = False
    Me.lbl2013Balance.Visible = False
    Me.objLine.Visible = False
Else
    Me.2013Balance.Visible = True
    Me.lbl2013Balance.Visible = True
    Me.objLine.Visible = True
End If
0
 

Author Comment

by:Rick Norris
Comment Utility
I think I may ALMOST be there....  I just tested with just the one total (2013Balance) to see if I could just turn off it's visible property.  I put a stop at the below section; however, code never executed????

In the "on Format" event of the property sheet for GroupFooter2 it is now set to:  [Event Procedure].  I'm very adept at C#, but this is really getting to me....  should be soooo simple...  What am I missing????

Code I have now is:

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
If Me.Const1 = 1 Then
    Me.AccessTotals2013BALANCE.Visible = False
Else
    Me.AccessTotals2013BALANCE.Visible = True
End If
End Sub
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 250 total points
Comment Utility
What is Const1?  The code below works; it is looking at the value of a field.  

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

   If Me![Sales] >= 10000 Then
      Me![txtSales].FontBold = True
      Me![txtSales].FontName = "Arial Black"
      Me![txtSales].FontSize = 12
      Me![txtSales].ForeColor = vbRed
      Me![txtSales].BackColor = vbYellow
      Me![imgGoldStar].Visible = True
   Else
      Me![txtSales].FontBold = False
      Me![txtSales].FontName = "Arial"
      Me![txtSales].FontSize = 9
      Me![txtSales].ForeColor = vbBlack
      Me![txtSales].BackColor = vbWhite
      Me![imgGoldStar].Visible = False
   End If
      
End Sub

Open in new window


As far as group totals are concerned, if they are in a group footer, you can just make the whole footer section visible or invisible as needed:

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

   If Me![txtMailingCity].Value = "Kerhonkson" Then
      Me.Section(acGroupLevel1Footer).Visible = False
   Else
      Me.Section(acGroupLevel1Footer).Visible = True
   End If
   
End Sub

Open in new window


Note that in reports, you may need to reference a control rather than a field.
0
 

Author Comment

by:Rick Norris
Comment Utility
Helen:

Thanks for the post....  Const1 is a new field that I set to a default value of 1.. (Yes, I made sure each record had the 1 after I created the new field).   I put this in the detail of the report, and summed...  When there is only one line of detail for a group, then I just get the value of 1 in the footer of that group.  It seems your code for "group" (last example) should work perfectly; however, when I implement your suggestion, nothing happens.....  I even  put a "break point" on  the first line of code.....  did not even break when I ran????

Here's code I used... though still not working.

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
  If Me![AccessTotalsConst1].Value = 1 Then ' breakpoint this line, yet no break occured?
      Me.Section(GroupFooter2).Visible = False
   Else
      Me.Section(GroupFooter2).Visible = True
   End If
End Sub


This should not be THAT hard....  I'm obviously doing something wrong   :-(

Thanks
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
Try giving the control bound to AccessTotalsConst1 the "txt" prefix, then reference the control in your code.  sometimes there can be confusion when a control has the same name as its field, and in reports I have often found that you will only get the desired results by referencing a control rather than a field.  In my second code sample, I first referenced MailingCity (the field), and that didn't work, but when I changed the reference to txtMailingCity.Value, it worked.

Also, you could use a Debug.Print statement to see the value of the control.

And finally, make sure the event procedure is listed in the properties sheet for that section footer.  Sometimes this doesn't happen if you just create the code in the module.
0
 

Accepted Solution

by:
Rick Norris earned 0 total points
Comment Utility
Helen/Pat:

Tried above... still no go....  Yes, I prefixed with "txt"...  Yes,"event Procedure" is listed in the property sheet for the proper section footer....   I consider myself a very good c# programmer; however, this access report writer is kicking my bu**!

I have attached the actual database...  (with everything striped out except the essential parts....  One table, and one report).

ANY assistance you or could give is  most appreciated.....  I just want to  hide the footer totals  when there is only one item in the detail.....

Thanks again.
DBfor-Experts-Exchange.accdb
0
 

Author Closing Comment

by:Rick Norris
Comment Utility
Helen/Pat:

I really need a kick  in the head....  everything is working correctly....  To show now "green" I happen to be with the access report writer....  I was just right clicking on the report and selecting "open".  I thought that should "fire" the code....  I FINALLY noticed the print preview....  HUGE BUTTON....  How did I miss it????  After I actually did a REAL print preview I was able to see that the code was firing, AND most importantly working correctly.

Sorry for all the hassle after I had the solution!!

Both of you gave valuable insight, therefore I am splitting the points.

Thanks again!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now