Link to home
Start Free TrialLog in
Avatar of juricta
jurictaFlag for United States of America

asked on

How do I Alternate Color by Group instead of by ROW in MS-Access 2016 report

MS-Access 2016 automatically colors rows in their reports.  These colors alternate by ROW.  I want to alternate the colors by GROUP!  I grouped my report by Invoice (Invoice1, Invoice2,....Invoice100) and want to alternate the colors by Invoice , to include their "Subs"

Example:
Invoice1
    Sub1
    Sub2
Invoice2
    Sub1
Invoice3
    Sub1
    Sub2
    Sub3
Invoice4
    Sub1
    Sub2

Invoice1 AND Sub1, Sub2 would be one color (lets say grey)
Invoice2 AND Sub1, would be alternate color (lets say white)
Invoice3 AND Sub1, Sub2, Sub3 would be Same color as Invoice1 (grey)
Invoice4 AND Sub1, Sub2, would be Same color as Invoice2 (white)

Can any one help?
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

if you are in code behind a subreport and want the back color of the detail section to match the back color of the parent report detail section, you can use this in code (perhaps ReportHeader_Format event of subreport) :

    Me.Detail.BackColor = Me.Parent.Detail.BackColor

Open in new window

(modify AlternateBackColor accordingly -- or set to No Color on property sheet)

if you want to know more about designing reports, here are a couple video tutorials on EE that will be helpful:

Create a Query and Grouped Report and Modify Design using Access
this uses the wizard to make a report -- and then changes what it did
https://www.experts-exchange.com/videos/4514/Create-a-Query-and-Grouped-Report-and-Modify-Design-using-Access.html

Polish Reports in Access
https://www.experts-exchange.com/videos/4559/Polish-Reports-in-Access.html
Avatar of juricta

ASKER

Crystal,

Unfortunately this does not do what I an trying to achieve.  I am attaching two PNG files.  CAPTURE1 is what MS Access 2016 defaults for REPORTS.  CAPTURE2 is what I want to achieve (there will actually be MORE records to show)
Capture1.PNG
Capture2.PNG
perhaps I did not explain well? or maybe I don't understand! are you not trying to set back color to match the section that the subreport is in? maybe you need to set more than just detail -- maybe footer too.  

did you try the VBA code I gave you?
Avatar of juricta

ASKER

I have an InvoiceNumberHeader (records are grouped by this), Detail and InvoiceNumberFooter associated with the header.  EACH record has a minimum of one entry in the Header, Detail, and Footer.    If this is the case - the Header is COLOR1, the Detail is COLOR2 and the Footer is COLOR1 (based on the section's  Format->BackColor and Alternate Back Color (refer to CAPTURE1 from previous post)  I would like all three of these sections to be the "Back Color" for the first invoice number.  For the second invoice number I would like all three areas to be the "Alternate Back Color" (refer to CAPTURE2 from previous post).

The third invoice number would mimic the first invoice number (REGARDLESS of how many records are in the Details section)(ALL three section should have the same color.

Basically the ODD invoice number would have the Header, Detail and Footer color shown in the "Back Color" and the EVEN invoice number would have the "Alternate Back Color"

.
Avatar of juricta

ASKER

I tried the code and it did not work
Sorry, I assumed you were trying to match the main report.  If I understand correctly, you want the detail and group footer to be the same as the group header section ? In that case, use the group header format event to set the BackColor property for detail and group footer to match the group header properties. Depending on what that is, you may want to change AlternateBackColor too.

If this is still not what you are trying to do, please post a screen shot of the design view of the report, thanks
Avatar of juricta

ASKER

Attached is the DesignView of the REPORT.  You are correct in trying to "If I understand correctly, you want the detail and group footer to be the same as the group header section ?".  But I also want the Alternate Back Color to be applied for the next Invoice Number.  This way instead of "every other"  ROW being a COLOR or ALTERNATE color I would have every other Invoice Number be a COLOR or ALTERNATE COLOR

Here is the code you suggested (I think):
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    Me.Detail.BackColor = Me.GroupHeader0.BackColor
    Me.GroupFooter1.BackColor = Me.GroupHeader0.BackColor
End Sub

I have attached the ACCDB database with the table and report.  When you open the report you will see the ALTERNATE rows (EVERY OTHER ROW) being a different color.  What I want, as an example using the last InvoiceNumber (1708140001) , would be for the five line associated with this Invoice to be one color.  The InvoiceNumber above it (1708130001), all four line associate with this invoice would be a different color (and so on for every DIFFERENT invoice)
Database2.accdb
ASKER CERTIFIED SOLUTION
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of juricta

ASKER

Anders.  You are SPOT on with your answer.  I appreciate the help.  Crystal, thank you so much for your help too.  You started me down the correct path.   I have decided to give Anders a few more points because he gave the correct answer even though you were heading that way.  I there are any issues with the point break down I am willing to work with you both as well as Experts-Exchange
you're welcome, and thank you.  

For next time, please know that closing a question is not just awarding points; more important, it is picking posts (as opposed to clicking on anything by an author) that helped  -- that way, others that read this thread can learn more quickly by seeing what was helpful to you.