Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-17-2018, 01:30 PM   #1
Big Pat
Newly Registered User
 
Join Date: Sep 2004
Location: A long way from Tipperary!
Posts: 549
Thanks: 30
Thanked 5 Times in 5 Posts
Big Pat is on a distinguished road
Format detail line of report based on control value

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.
Attached Images
File Type: png Report.PNG (31.6 KB, 17 views)

__________________
I think my keyboard is running low on toner.
Big Pat is offline   Reply With Quote
Old 10-17-2018, 01:38 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,036
Thanks: 10
Thanked 3,862 Times in 3,805 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Format detail line of report based on control value

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-17-2018, 01:51 PM   #3
Big Pat
Newly Registered User
 
Join Date: Sep 2004
Location: A long way from Tipperary!
Posts: 549
Thanks: 30
Thanked 5 Times in 5 Posts
Big Pat is on a distinguished road
Re: Format detail line of report based on control value

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.

__________________
I think my keyboard is running low on toner.
Big Pat is offline   Reply With Quote
Old 10-17-2018, 01:55 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,036
Thanks: 10
Thanked 3,862 Times in 3,805 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Format detail line of report based on control value

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?
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-17-2018, 02:00 PM   #5
Big Pat
Newly Registered User
 
Join Date: Sep 2004
Location: A long way from Tipperary!
Posts: 549
Thanks: 30
Thanked 5 Times in 5 Posts
Big Pat is on a distinguished road
Re: Format detail line of report based on control value

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
__________________
I think my keyboard is running low on toner.
Big Pat is offline   Reply With Quote
Old 10-17-2018, 02:05 PM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,036
Thanks: 10
Thanked 3,862 Times in 3,805 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Format detail line of report based on control value

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-17-2018, 02:22 PM   #7
Big Pat
Newly Registered User
 
Join Date: Sep 2004
Location: A long way from Tipperary!
Posts: 549
Thanks: 30
Thanked 5 Times in 5 Posts
Big Pat is on a distinguished road
Re: Format detail line of report based on control value

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!
Attached Files
File Type: zip Test database.zip (29.3 KB, 6 views)

__________________
I think my keyboard is running low on toner.
Big Pat is offline   Reply With Quote
Old 10-17-2018, 02:53 PM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,036
Thanks: 10
Thanked 3,862 Times in 3,805 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Format detail line of report based on control value

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 10-17-2018, 08:28 PM   #9
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,427
Thanks: 2
Thanked 1,993 Times in 1,949 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Format detail line of report based on control value

Try the attached:
Attached Files
File Type: accdb Test database3.accdb (476.0 KB, 7 views)
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
Big Pat (10-18-2018)
Old 10-18-2018, 07:04 AM   #10
Big Pat
Newly Registered User
 
Join Date: Sep 2004
Location: A long way from Tipperary!
Posts: 549
Thanks: 30
Thanked 5 Times in 5 Posts
Big Pat is on a distinguished road
Re: Format detail line of report based on control value

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,
__________________
I think my keyboard is running low on toner.
Big Pat is offline   Reply With Quote
Old 10-18-2018, 01:42 PM   #11
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,427
Thanks: 2
Thanked 1,993 Times in 1,949 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Format detail line of report based on control value

Set the "Alternate Back Color" = "No Color" then your original code will run!
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 10-18-2018, 11:16 PM   #12
Big Pat
Newly Registered User
 
Join Date: Sep 2004
Location: A long way from Tipperary!
Posts: 549
Thanks: 30
Thanked 5 Times in 5 Posts
Big Pat is on a distinguished road
Re: Format detail line of report based on control value

Thank you. The most obvious and the most simple - but I hadn't spotted you could even do that!!
__________________
I think my keyboard is running low on toner.
Big Pat is offline   Reply With Quote
Old 10-18-2018, 11:25 PM   #13
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,427
Thanks: 2
Thanked 1,993 Times in 1,949 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Format detail line of report based on control value

Quote:
Originally Posted by Big Pat View Post
- but I hadn't spotted you could even do that!!
And I have forgotten it, until I took a closer look, then I remembered it!

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Reply

Tags
background color , report

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access Report Format Detail kannang77 Reports 2 10-12-2016 06:34 PM
Using VBA to change detail in report to percentage format fortwrestler Reports 2 08-09-2016 07:36 AM
Format a line of a Report based on criteria in unrelated popup johnkrytus Modules & VBA 0 10-13-2014 02:10 PM
Fixed Format In The Report's Detail mykil Reports 46 09-27-2011 04:07 PM
hide detail line on report Tara Reports 3 04-01-2005 07:46 AM




All times are GMT -8. The time now is 05:52 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World