Change alternate back colour in detail section when field value changes

[Micron]

Maybe this isn't necessary as others might have a firm grip on the requirement, but I'm wondering if there is a query behind this form. In that case, running it and copy/paste to Excel then format the cells so as to represent exactly what you want, then post that as a pic. Just throwing that out there in case you don't end up getting what you want from Paul's excellent expertise.

[pbaldy]

Something like this?

[Micron]

Crikey. So it's not "when the field value changes" as in in the table it was A yesterday, but I just changed it to B.
It's when the sorted value in a report column/field changes. Duhh.

[pbaldy]

I may have misunderstood.

[tuniwes]

Something like this?

Hi Paul, yes, spot on, that's exactly what I am trying to achieve

[orange]

Interesting. Can we see the code that makes his happen?

[pbaldy]

I'm on my iPad now, so I'll post it when I get to my computer. It's what I described in post 2, with an additional Boolean variable flipped each time it changes.

[pbaldy]

Here's the core code. The variables are declared at the top of the report module, the project variable is set in the load event. Feels like a kludge, but it works.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If strProject <> Me.Project Then
        strProject = Me.Project
        booChanged = Not booChanged
    End If
    If booChanged Then
        Me.Detail.BackColor = RGB(204, 204, 204)
        Me.Detail.AlternateBackColor = RGB(204, 204, 204)
    Else
        Me.Detail.BackColor = vbWhite
        Me.Detail.AlternateBackColor = vbWhite
    End If
End Sub

[orange]

Thanks Paul.

[tuniwes]

Here's the core code. The variables are declared at the top of the report module, the project variable is set in the load event. Feels like a kludge, but it works.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If strProject <> Me.Project Then
        strProject = Me.Project
        booChanged = Not booChanged
    End If
    If booChanged Then
        Me.Detail.BackColor = RGB(204, 204, 204)
        Me.Detail.AlternateBackColor = RGB(204, 204, 204)
    Else
        Me.Detail.BackColor = vbWhite
        Me.Detail.AlternateBackColor = vbWhite
    End If
End Sub

Wow....Thanks Paul, have put the code into the detail on format event but I'm struggling where to declare the variables.
Could you post the syntax and where to put it please

Best

Ian

[June7]

For this particular situation, declare these variables in the report code module header section just below the Option lines.

Option Compare Database
Option Explicit
Private strProject As String, booChanged As Boolean

http://access-excel.tips/excel-vba-d...obal-variable/

Then suggestion is for code in the report Load event to set the initial value of strProject

Private Sub Report_Load()
strProject = Me.Project
End Sub

[tuniwes]

Thanks June....I have tried to follow your instructions

Paul's code in the detail format event
Declare the variable at the top
Code in the on load event

But still not working for me

The help and knowledge that you guys freely give to people like me is fantastic and I really want to learn how these things work to better my knowledge

Paul, is there any chance that you could upload the example that I put up with your code in place.

I would really like to understand it and further my knowledge as opposed to just implementing it and being none the wiser

Thanks both

Ian

[June7]

What does 'not working' mean - error message, wrong results, nothing happens?

I tested Paul's code in your db and it does work. Even without the Load event code, just the Detail format event and the declared variables. I used vbRed as the color instead of the RGB() code so it really stands out for demonstration.

Set the textboxes BackStyle to transparent so the row color will show through.

However, if not already noted, Format event triggers only in PrintPreview or direct to printer, not ReportView. And since buttons on report work only in ReportView, your requirements for this report are in conflict. Cannot have both. Unless Conditional Formatting can be made to produce the same effect.

[Micron]

your requirements for this report are in conflict.

which begs the question, what is the point of putting a bunch of buttons on a report if you want it to behave like a form?

Anyway, you might be able to incorporate 1 field in your report query and use a subquery to get the Project from the prior record - PriorProject (as in SELECT TOP 1...). Using conditional formatting on a textbox that lies underneath all the detail section controls (those controls you set to be transparent) you would alter the color with the condition ([Project] <> [PriorProject]). This idea is entirely untested and the logic may not work to your satisfaction, assuming it's doable. Certainly, I suspect that if there is one ProjectA record, the next is ProjectB, the 3rd is ProjectC, they will all be colored, which may not be what you want. To me, this is one of those things for which a lot of work is required for little benefit. Sure it may impress, but is it really worth the effort when you could introduce a Project grouping? Sadly, reports often don't have a long life span. As soon as management starts reviewing them, they soon want something added or tweaked and your report goes out the window.

[tuniwes]

What does 'not working' mean - error message, wrong results, nothing happens?


However, if not already noted, Format event triggers only in PrintPreview or direct to printer, not ReportView. And since buttons on report work only in ReportView, your requirements for this report are in conflict. Cannot have both. Unless Conditional Formatting can be made to produce the same effect.

June

As you say works perfectly, I hadn't realised that it would not work in report view, only preview and print

Thanks everybody for helping out, I'll mark as solved

Best

Ian