Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MSACCESS: Change BackColor of textbox on report with VBA

Posted on 2010-08-27
4
Medium Priority
?
1,103 Views
Last Modified: 2013-12-20
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
Comment
Question by:Ubertam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 5

Author Comment

by:Ubertam
ID: 33543417
It works in Print Preview, not Report View
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 33543687
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 33544338
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33545057
Thanks

Jeff
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question