• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1242
  • Last Modified:

MSACCESS: Change BackColor of textbox on report with VBA

I am trying to do something that should be way easier than it is (and probably is easier).  I want to change the background color on a textbox based on its value.  I don't want to use Conditional Formatting because I need to learn the VBA to do it.  I've used conditional formatting before and that's great.  I'm working with more advanced formulas to determine color, and want to do it in VBA.

Amount is of type Currency, if that matters.  Code is below.

The textbox format default back color is not transparent; it's set to Normal.

It simply doesn't work.  All my values are positive dollar amounts.  Once I get this to work, then I'll play more.  What I'd love to see is all of the Amount fields to be red.  Now, they're all white.

Thanks!
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Amount > 0 Then
Me.Amount.BackColor = RGB(240, 0, 0)
End If
End Sub

Open in new window

0
Ubertam
Asked:
Ubertam
  • 2
1 Solution
 
UbertamSenior TechnicianAuthor Commented:
It works in Print Preview, not Report View
0
 
puppydogbuddyCommented:
try it this way:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim x as Integer                 'loop counter
For x = 1 to FormatCount
     If Me.Amount > 0 Then
            Me.Amount.BackColor = vbRed
    End If
    x = x + 1
Next
End Sub
0
 
Jeffrey CoachmanMIS LiasonCommented:
You cannot access certain values via VBA in "Report View"
This is just the way it is.

There are certain things you can do in Report View, like setting values on the Open event.
But to read values on the Detail Format event requires you to be in Print Preview.
See here, under:  What's mixed (good and bad)
http://allenbrowne.com/access2007.html

This is how it's been since Acc2007 was introduced.

So it looks like it is Print Preview or Conditional formatting for this...

;-)

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
Thanks

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now