How to programmatically color an object on a condition

MS Office Access


  • 1. cumulative sums of the deviations
    I need to use access to do the following: HOW??? I retrieve the values A and B from a table, then calculate A-B, then I need to look at the previous records' (A-B) value and add it to the current records' (A-B), hence the current value of (A-B) is a cumulative sum, i dont want a total, as i want the individual records the the cumulative sum todate to be presented in a query. pat
  • 2. Sub Report With No Links to Parent Not Visible in preview/print mo
    I've not attempted this before but I now have a need to display an unconnected subreport with data on a report where there is no linking criteria. The subreport is actually an index for the main report but since it is effectively a summary shares no common linkable field with the main report. I want to drop it onto the report footer. Any ideas?
  • 3. Subreport with where clause in query
    Dear Group, I'm having a problem with designing queries for subreports. I'm selected a group of records for the subreport where I want to limit the set accoring to a field specified in the main report. It looks somewhat like the following: SELECT Part, Revision, RevDate, Designer FROM tblRevision WHERE [Part] = [reports]![MAIN_REPORT].[Part] The subreport uses this either as an SQL query or a stored query. Works great. I'd like to be able to use this subreport & query in other forms so I need to do some sort of relative addressing like: SELECT Part, Revision, RevDate, Designer FROM tblRevision WHERE [Part] = Parent.[Part] But I can't get this to work. What is the correct syntax for specifing the parent report of a sub? Is there any clean way to do this? Thanks, Max
  • 4. runtime error 2106
    Hi there. I'm having trouble with a report. I inserted an object as a link - a word-document. When closing the report afterwards Access told me that thing was too big for the report but closed the report - so i had no chance to change it. Now, when trying to open the report - nevermind whether in design or preview view - i keep getting the following error message: "1 errors occured when you loaded the form or report - You loaded a form or report that has controls or properties that Microsoft Office Acces doesn't recognize and will ignore". Even when trying to access the report over the immediate window ( [CTRL] + G ) i get the error message "Run-time error '2106': 1 errors occured when you loaded the form or report". So, unfortunately i don't even get access to the controls on the report over VBA - the error keeps coming up all the time. As the report is rather big and has many conditional elements which took quite some while to build, i'd really like to get access to it other than starting from scratch. Does anyone have an idea ? Thanx a lot in advance.. Greets Fabian.
  • 5. Output Access record to Word Template
    I have a template that is dictated by our company for standard reports. rather than re-create the template now (and next time they revise it) as an access report, I want to put a button in my access forms that allows users to create a word document, populated by the data in that record, using the standard word template. Is mail merge the right thing for this? It didn't seem to be taking me down the right road when I first tried it. What I really want is a word document based on a single record of access data on an ad hoc basis. Can I just create a few MSWord fields in the template to be placeholders for the access fields, and then issue a VB command to dump from Access to Word? Thanks,

How to programmatically color an object on a condition

Postby Zm94eWRldjc3 » Thu, 11 Jan 2007 05:44:02 GMT

I need to change colors as scores change.  The conditional format only works 
on textbox.  I would like to change the label as well.

Re: How to programmatically color an object on a condition

Postby fredg » Thu, 11 Jan 2007 06:32:53 GMT

Use code in the Detail Format event to change both controls at the
same time:
If [SomeField] = 1
	[SomeField].BackColor = vbBlue
	[SomeLabel].BackColor = vbBlue
Elseif [Somefield] = 2 Then
	[SomrLabel].BackColor = vbRed
	[SomeField].BackColor = vbRed
ElseIf [SomeField] = 3 Then
	[SomeLabel].BackColor = vbWhite
	[SomeField].BackColor = vbWhite
End If
Please respond only to this newsgroup.
I do not reply to personal e-mail

RE: How to programmatically color an object on a condition

Postby RHVhbmUgSG9va29t » Thu, 11 Jan 2007 06:34:02 GMT

You could write some code in the On Format event of the report section 
containing the controls.

Select Case Me.txtScore
   Case 0
       Me.lblScore.BackGround = vbRed
   Case ....
End Select
Duane Hookom
Microsoft Access MVP

RE: How to programmatically color an object on a condition

Postby RHVhbmUgSG9va29t » Thu, 11 Jan 2007 06:39:02 GMT

Actually the property is BackColor and some sample code would be similar to:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Select Case Me.txtScore
        Case Is < 25
            Me.Label6.BackColor = vbYellow
        Case Is < 50
            Me.Label6.BackColor = vbGreen
        Case Else
            Me.Label6.BackColor = vbWhite
    End Select
End Sub
Duane Hookom
Microsoft Access MVP

Similar Threads:

1.Programmatically detach object in unbound object frame to a bound object frame


On one of my form, I have a hidden unbound object frame with a Word document
template in it. I want users to press a button and then it will detach the
Word template from the unbound object frame and insert it into a bound
object frame.

Thanks in advance.


2.Loop through objects changing background color of certain kinds of objects

I think that sums it up.  If someone can help me, TIA.

Just to clarify-  I have several forms- and I want to run a program
that will look at each one, and on each form, if the type of object is
a Header, Form, Footer, or Label, change it's background to a given

Something like For each object in objects...?  The syntax excapes me
at the moment.


3.Word 2007: programmatically determine current color scheme

4.Change the backgroud color according to condition

Hi all,

I woulid like to enhance my form by changing the backgroud color of my
text box when the firstname change.
I would like to have something like that:


First name
Alan     xxxxxxx     00000000         (blue)
Alan     rrrrrrrrrrr      11111111         (blue)
Alan     zzzzzz       22222222         (blue)
Peter    aaaaaaa     11111111         (red)
Peter    xxxxxxx     00000000         (red)
Zita       eeeeeee     22222222         (blue)

Is it possible and who has any idea ?

5.Background color based on condition

I have a continuous form and want to change the backcolor
of a field in one column based on a condition in another
column. My problem is that when the field is clicked the
backcolor that is appropiate is applied to the whole column
not just that one record.  Here is my code

Private Sub Form_Current()
    Dim lngYellow As Long, lngWhite As Long
    lngYellow = RGB(255, 255, 0)
    lngWhite = RGB(255, 255, 255)
    If Me.ChATimeIn = 0 Or IsNull(ChATimeIn) Then
        Me!ATimeIn.BackColor = lngWhite
        Me!ATimeIn.BackColor = lngYellow
    End If



6. Change textbox color based on a condition

7. Highlight/color Whole Record When Condition is Met

8. Change color if condition is met.

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 87 guest