Format detail line of report based on control value (1 Viewer)

Big Pat

Registered User.
Local time
Today, 11:26
Joined
Sep 29, 2004
Messages
555
Hi,

I have a report with a control called "Highlight" (which will be hidden eventually). Depending on which value I have selected on a form, one of the lines on my report will have the Highlight value set to "H" and the others will all be Null. I have verified that this is working. The H does appear.

I want to use this value to shade that line of the report yellow i.e. highlighting the line that matches the form selection. But it's not working. I get no error messages, but it just doesn't change the colour.

Is it a problem with the code itself, which is in the OnPrint event, or is there some other setting or property I need in order to activate this somehow?
Code:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

If Me.Highlight.Value = "H" Then
    Me.Detail.BackColor = RGB(255, 255, 204) 'pale yellow
Else
    Me.Detail.BackColor = RGB(255, 255, 255) 'white
End If

End Sub

Please see attached image of report.
 

Attachments

  • Report.PNG
    Report.PNG
    31.6 KB · Views: 110

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:26
Joined
Aug 30, 2003
Messages
36,118
It might be working, though I'd use the format event (unless the report is in Report view). Make sure the back style property of the textboxes is transparent.
 

Big Pat

Registered User.
Local time
Today, 11:26
Joined
Sep 29, 2004
Messages
555
The back style of most of the other controls is transparent. Not the two with the percentages, as I am using conditional formatting for those and the colours wouldn't work unless I made the back styles normal. But every else, apart from the Highlight control is transparent.

There's also plenty of space between the other controls anyway, so even if they were not set to transparent, I would still expect to be able to see yellow between them.

Could you possibly post some code that would work in the On Format event? I tried moving the existing code there but got an error message that said something like "A custom macro failed to run and is preventing your report from rendering", so presumably the code for that event would be different.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:26
Joined
Aug 30, 2003
Messages
36,118
I would expect that code to work. Different events fire based on which view you're using. Can you attach the db here to play with?
 

Big Pat

Registered User.
Local time
Today, 11:26
Joined
Sep 29, 2004
Messages
555
I can, but it will take me some time, as I have links to other dbs and to Excel sheets, so I'll need to make a copy with local tables first. It's after 11pm at this side of the Atlantic so I'll leave it for now and try to make time tomorrow.

Thanks
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:26
Joined
Aug 30, 2003
Messages
36,118
Roger that. You might set a breakpoint and see how the code is treating that line (and let you see the value held by highlight). That will also help make sure the code is even running.
 

Big Pat

Registered User.
Local time
Today, 11:26
Joined
Sep 29, 2004
Messages
555
Wasn't thinking straight. I just needed to make the query a maketable, base the report off the new table and delete everything else, to make a copy you can examine. So I have attached it now.

I'm no coder, just a self-taught (forum-taught!) hack so I don't know exactly what you mean about the breakpoint.

But.... the conditional formatting is making some of the controls on the "H" line bold as expected, so the H is definitely there.

Anyway, I'll check back here tomorrow. Appreciate the help!
 

Attachments

  • Test database.zip
    29.3 KB · Views: 113

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:26
Joined
Aug 30, 2003
Messages
36,118
Curious, I don't see right off why it's not working. I've got to go visit a friend in the hospital, but you can get the same effect by putting a textbox behind all the others and using Conditional Formatting on it. Did a quick test and it worked. I'll poke around further tomorrow.
 

JHB

Have been here a while
Local time
Today, 12:26
Joined
Jun 17, 2012
Messages
7,732
Try the attached:
 

Attachments

  • Test database3.accdb
    476 KB · Views: 108

Big Pat

Registered User.
Local time
Today, 11:26
Joined
Sep 29, 2004
Messages
555
Thanks JHB. That worked, or at least came close enough that I could figure out the rest.

Your solution worked with the H value on the line where I had left it, but it would not work when I moved it to random other rows. I was puzzled as to why you had used alternatebackcolor, but I used the same logic on backcolor and that seems to have done the trick.

I had created the report using the toolbar button and then started reformatting so it had set my report to have alternate row shading. I didn't like that so I had set both values to #FFFFFF and maybe that was preventing my original code from running. Is there a better way to stop reports having alternate row shading?

I'm also unclear about why this needs both an 'On Format' and and 'On Print' events and about why they are slightly different

On Format, if there's a H, make it yellow
On Print, if there is not a H, make it white.

Maybe it would work just as well the same way round on both. I should experiment when I get time.

Anyway...thank you! This really helps,
 

JHB

Have been here a while
Local time
Today, 12:26
Joined
Jun 17, 2012
Messages
7,732
Set the "Alternate Back Color" = "No Color" then your original code will run! :)
 

Big Pat

Registered User.
Local time
Today, 11:26
Joined
Sep 29, 2004
Messages
555
Thank you. The most obvious and the most simple - but I hadn't spotted you could even do that!!
 

Users who are viewing this thread

Top Bottom