Alternate Back Colour working strangely

[DavidAustin]

alternate_back_colour_problem.png

Hi all, another day another query!

I have made a report based on a query that produces a list of all the files that have been added to the database since the last report was produced. The files have been stored away in boxes in real life. The problem is with the alternate back color of the detail. I was hoping it would alternate each row of the report, however it alternates each row in each box and resets to the original back colour for a new box (make sense?)

Aka (see attachment) I was hoping it would go white, grey, white, grey etc. for the whole document but instead it goes:
Box 16 white, grey, white, grey
Box 17 white
Box 18 white, grey

I'm hoping there's a simple fix but all help is appreciated!

[twinnyfo]

David,

Underfortunately, that is how the Alternate coloring works....

[jforbes]

It's not really that hard to control this yourself. Here is some example code that alternates color every time the GroupHeader is printed:

Put this Global declaration at the top of the Report Module:

Private iCount As Long

This fires off the Paint Event of a Group Header. This doesn't seem to work in the Group OnFormat; I'm not sure why, but I haven't really cared enough to find out either.

Private Sub GroupHeader0_Paint()
 
    Dim lColor As Long
 
    iCount = iCount + 1    
    If iCount Mod 2 = 0 Then
        lColor = -2147483604
    Else
        lColor = vbWhite
    End If
 
    ' Set you Control's Background color here...
    Me.FirstField.BackColor = lColor
    Me.SecondField.BackColor = lColor
 
End Sub

[DavidAustin]

I tried putting this in the group header paint section as you said but it didn't work. After trying it in a few places, it finally worked in the Detail_Format event but it works perfectly now. Thanks for all your help again, this forum is the best!

[DavidAustin]

Sorry to revive an old thread but it's directly related to the code and question above. Is there a way to do this alternation but ensure that it counts from the bottom upwards? I need the final record on the page to be the alternate colour and then alternate up the page. I'm not sure it will work because I assume it does the formatting of the report going down the page.

[jforbes]

I don't think it would be that difficult. The trick would be to initialize the iCount variable as an odd or even number (0 or 1) to begin with. Probably with a DCount() of the Reports recordset and WhereClause. The WhereClause would be key.

Actually, you could probably cheat and just initialize the iCount to the DCount() of records and it would work itself out.

[DavidAustin]

I am probably misunderstanding this, but wouldn't the DCount count all of the records that are being produced for the query and not just the ones that are going to fit on each page of the report?

[jforbes]

Ah, shoot. I got myself mixed up with another, similar, question.

I haven't figured out how to count how many records are on the page as long as the CanGrow Property is being used; Access' painting process becomes a little unreliable with the CanGrow Property.

If you aren't using the CanGrow Property, then you should be able to reset the iCount variable on a Page Header OnFormat. And since the Detail Records will be the same size, you should be able to set the iCount variable to an Odd or Even to give you the correct alternating scheme.

You might want to do this even if you have the CanGrow Property set to On, so that each page always starts out with the same background color. I don't do this as I like to see the continuity over the pages; for us it is just another visual check that everything is in order.

[DavidAustin]

I'm still a bit lost with this all I'm afraid. I'm not using the CanGrow property. In my mind, I think I need the procedure to go like this:

Populate page 1 of report -> count number of records on page -> format backcolour of records based on how many records on the page -> Move to page 2 -> reset counter -> populate page 2 etc.

I understand resetting the iCount variable to 0 in each page header - makes sense so you can then work out how many records are on the page.

[DavidAustin]

Would there be a way to use the page footer to determine the background colour of the last record and then, if it's the wrong colour, reformat the detail again by starting from the iCount being 1 instead of 0?

[jforbes]

Both of these processes sound good and logical, but they both would require either looking ahead to the end of the page (when at the top of the page) or going backwards to the top of the page after the bottom of the page has been reached. Neither of these options are available. Or al teast that I know of.

It might be easiest to look at this as a Forward-Only Recordset. While moving through it, nothing in the past can be modified and the future is not visible. So when a new page is reached, the Report would need to predict what is going to happen. This is where doing a DLookup on the Page Header to reset the iCount to a value that would, after the rest of the page is formatted, give you the backcolor that is desired.

A quick and dirty test would to set iCount to 0 for your PageHeaderSection_Format() and see what happens. Then set iCount to 1 to see what happens. Who know, maybe this is all you need.

[DavidAustin]

Ah if it goes forward only then it appears I will be stuck. Many thanks for the help as always!