?
Solved

How can I change a color using Conditional formatting or VBA, when the value of a record changes?

Posted on 2010-09-01
5
Medium Priority
?
299 Views
Last Modified: 2012-05-10
I have a question about a continuous form I'm working on.  The record source will have 2 or 3 records with the same "transID,"  then changes to another.  I'd like to visually group these on the form, so the user easily identifies them as a group.  The form has to stay a form.  Since forms don't allow grouping, I thought conditional formatting might work.  Any ideas?  Thanks in advance!
0
Comment
Question by:sarahellis
[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
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 33576438
Unless you are using A2010 which has 50 CF rules, rather than the 3 in earlier versions, then I don't believe this is possible.  But wait for other opinions.

(I'm not saying it would be easy in A2010, but since it is probably unlikely that you have more than 50 different transID values on the screen at the same time  it is at least theoretically viable.)
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 33576463
You have a limit of 4 "conditions" for Conditional Formatting (i.e. the Default condition + 3 that you can detail). You can modify this to some degree with VBA, but it's tricky at best.

You might use a Temporary table to "format" your records using blank spaces to delineate your differing groups. In other words, if GroupA has 8 record, and GroupB has 4 and GroupC has 6, then move those records to a Temporary table and add the first 8 records, then a blank line, then the next 4 records and a blank line, and finally the last 6 records. The user would see a blank line and would be visually notified that a new "group" had started. You could also insert text in the first "cell" of that blank line to indicate the TransID, and then insert a value in the second cell to use with Conditional Formating (i.e insert a value of "True" in the second "cell", and then use CF to Bold/Make Red the value of the First "cell" based on the value of True in the second).

If you need advanced functionality like this, you often must move to a 3rd party grid control (I've used GridEX from www.janusys.com, but I'm not sure how well it will work in 07). CodeJock has a ReportControl which may work in 2007 (www.codejock.com), and Bennet-Tec has a control which might suit your needs (www.bennettec.com).
0
 

Author Closing Comment

by:sarahellis
ID: 33576529
Oooh, good idea!  I think I'll try a spin on that idea.  Since this is a stagnant table (which I didn't mention), I'll add a field and populate it, with a code for every other group.  Then I can hide that field but use it for conditional formatting.  Thanks for the inspiration!
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 33576577
In 2007, you also have the Detail sections "Paint" event.  Similar to a Reports Format event, the Paint event allows you to do formatting when the record is displayed.  You could use code similar to the following to cycle through a series of background colors, using changes in [transID] to initiate the change in background color.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 33576631
Oops, forgot the code:

Private Sub Detail_Paint()

    Static lngTransID as long
    Static intCounter as int
    Dim lngBackColor as long

    if me.txtTransID <> lngTransID then
        lngTransID = me.txtTransID
        intCounter = (intCounter + 1) Mod 3
        lngBackColor = Choose(intCounter + 1, RGB(255, 0, 0), RGB(0, 255, 0), RGB(0, 0, 255))
    end if
    Me.txtTransID.BackColor = lngBackColor
       
End Sub
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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

762 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