Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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

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
sarahellis
Asked:
sarahellis
1 Solution
 
peter57rCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
sarahellisAuthor Commented:
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
 
Dale FyeCommented:
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
 
Dale FyeCommented:
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
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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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