Conditional formatting with non-standard background color

[fredalina]

i am recreating an existing Excel spreadsheet report into Access. Everything is perfect except for one thing. i have 3 places that are colored white, bluish gray, and a darker bluish gray (neither of them are standard pull-down colors). i need these three areas to be formatted like the rest, which includes red text when the number is negative.

The rest of the report (all white background) accomplishes the red text through conditional formatting (if there's a way to set it as "Accounting" in some other way, i don't know how as that doesn't seem to be an available setting for the fields).

So far i can only figure out how to make the totals section background color correctly but with black text even with negative values, or make the background white and the text colored red for negatives and black for positives. i was thinking of VBA on Form_Load() but I'm at a loss for code. Any suggestions?

 

[Trevor G]

If this is to work in a report it wouldn't be a form load for starters.

You would use RGB within an if statement within the report detail section properties and possibly on Print

If fieldname>=50 then
me.fieldname.forecolor=rgb(250,250,250)
else
me.fielname.forecolor=vbBlack
end if


You would have to play around with the RGB (which you can do a search on) and also work on the backcolor (Note you will have to use the american spelling for Color).

Also you could use a Select Statement

See how you get on.

 

[fredalina]

Yes, of course it's not Form_Load(). Silly me!

I'm having trouble accessing the value of the text box. Me.textbox.Value doesn't work (error is "You entered an expression that has no value"). Me.textbox.Text doesn't work (error is "You can't access the property or method for a control unless the control has focus"... and subsequently Me.textbox.SetFocus doesn't work (error is "Microsoft Office Access doesn't allow you to use this method in the current view"). This is a problem whether I use If/Then or Select Case.

I thought about having duplicate text boxes, one with black font and one with red font, and setting the .Visible property to each based on the value. This actually works well, but the problem is that there is a grouping of "Type" (i.e. Apples, Oranges, and Bananas). If i were only reporting Apples, this would work perfectly, but i don't know how to get this to work with groupings. Although this is probably just the long way around figuring out the right syntax for accessing the value of the text box. There are definitely values in the table in each grouping.

Thanks!

Edited to add that the report is based on a table, and the total fields are calculated (TotalThisMonth/SellingDaysThisMonth - TotalLastMonth/SellingDaysLastMonth as a percentage). i've tried pointing at either the text box itself or the fields it is based from.

 

[fredalina]

For future searchers: I found it. I needed to be using the Procedure

Private Sub Totals_Format(Cancel As Integer, FormatCount As Integer)

rather than anything to do with Report_Load(), etc. The .Value Property worked and all formatting worked as well.

Thanks!

 

[Trevor G]

Im pleased you worked it out,

I never mentioned anything about the report on load, but the detail section.

Pleased though for you