Change alternate back colour in detail section when field value changes

[tuniwes]

Hi


Below is an extract of a report grouped by Work Centre.
I would like to change/switch to the alternate background colour only when the project field changes in value to group the background on like projects together.
The value of the project field is infinitely variable

I would be most grateful for any suggestions on how to do this

[pbaldy]

My first though is code in the format event of the detail section that compares the project to the previous project, which you'd hold in a report level variable and update each time it changed.

[Micron]

This might be a dumb question (in which case it would not be my first) but why don't you just create a report grouping based on project?
For me, there is some uncertainty as to what is meant by "alternate background" color. There is an alternate row color property which I believe can be set during a report Format Detail event, but can this event also alter the back color of individual controls, especially without affecting every record where that control appears? For that, I wonder if conditional formatting is the way to go. Just askin'.

[pbaldy]

I took it to mean that the first row of a new project would be highlighted. Now that I read it again, I think all of the records of a given project would be the same color, which could still be accomplished with code. I don't see how with CF or the built-in alternate row color. But it wouldn't be the first time I was wrong...not even the first time today.

[June7]

I thought about conditional formatting as well, especially since now possible to have 50 conditions. How many projects are there? 50 might not be enough. And conditional formatting is hard coding the project parameter. However, I am not sure hard-coding the project parameter can be avoided even in VBA, unless the Projects table has a 'color code' field.

[pbaldy]

I read it as alternate background, except on project instead of record.

[orange]

I read it as Paul did, but the sample graphic the OP provided didn't clarify the request.

[tuniwes]

I read it as Paul did, but the sample graphic the OP provided didn't clarify the request.

Sorry guys, been away from the PC a couple days, Thanks for your replies

As I see it the alternate background normally changes every other record in the detail section
I only want it to change when the project changes. I can add a project grouping and use a very thin header to create a line break but this will not as far as I can see help in changing the back ground at detail level.
There could be over 50 projects on the go at any time with new ones constantly being added. Some might have up to 300 lines under one project but on the other hand could only be one or two

I think the only solution is via VBA but I can't figure out how to do it

Thanks

Ian

[pbaldy]

Post 2 has a rough outline. If you want to attach a sample db to play with, I'll code it.

[Micron]

I have no idea what "when the project field changes" means. Any time after the form opens? Only if it is altered from the original value? The former may require code via .OldValue property but the latter is merely conditional formatting IMHO - as long as there is a T/F field to flag a change. Grouping by "changed" was also mentioned. Hard to do without a flag field methinks, unless maybe this has a different meaning from what one would usually think.

[pbaldy]

I think it "changes" when it's different from the record above.

[Micron]

Maybe. Working right to left because the header labels don't line up, I see Project as either TILE300 or 31305 for the rows that have highlighting yet neither Item or Customer information is the same for either record, so what would be the common thing between them? I guess I will leave this to those who seem to be able to decipher the information.

[tuniwes]

Ok, I have lined up the field headers.sorry about that I have tried to upload a cut down version of the database but get an error message "File upload failed"...not clear why this is

So from the top, what I am trying to do is have :

Project Detail Colour

Apollo Park White Background
All King William Street lines Alternate Backround say Light Grey
All SP Arndale lines White Backround

with the background colour alternating on each change of project name

Etc,etc

Thanks

Ian

[pbaldy]

You should be able to attach after doing a compact/repair and then zipping.

[tuniwes]

You should be able to attach after doing a compact/repair and then zipping.

Thanks, I think that worked

example.zip