Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Formatting group totals in Access

Posted on 2015-02-18
9
Medium Priority
?
145 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
[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
  • 5
  • 2
  • 2
9 Comments
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 1000 total points
ID: 40616812
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
ID: 40617490
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 39

Expert Comment

by:PatHartman
ID: 40617537
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Rick Norris
ID: 40617778
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
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 1000 total points
ID: 40619139
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
ID: 40619247
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
ID: 40619320
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
ID: 40619465
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
ID: 40627756
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

610 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