Changing Row BackColor on Change in Value

[tjarus]

I know how to use Alternating Row Color (using Access 2016), but I need to be able to change the row color when the value in a field changes. Datasheet view, when the value changes in the SmartKey column I want to change the row backcolor. I've googled but not been able to find anything for Access. Does anyone have any ideas on how to do this? Thanks for your help.

 

[sneuberg]

Depending on what you mean by the value change Conditional Formatting might do what you want.

 

[tjarus]

Thanks for the reply. I've already been to that link and it's not what I'm wanting to do. I want to change the entire row color when the value changes. Let's say I have 8 columns of data, in one column, there are repeating values that are sorted together, such as, a list of people and their ages - everytime the value under the Age column changes, I want the row color to change to highlight the difference. Hope that helps.

 

[missinglinq]

A couple of things, to begin with:

• First off, there is no 'row backcolor' in Access.
  
• Secondly, the only way to format anything, color-wise, in a Datasheet View Form, is through the use of Conditional Formatting.

There are hacks out there, for doing this kind of thing, but they involve

1. Using a Continuous View Form and tweaking it to look like a Datasheet View Form
2. Placing a single Textbox behind all of the other Controls to provide a pseudo-background
3. Using (you guessed it) Conditional Formatting to format the color of this Textbox

Two more things:

• I've seen this kind of thing done...but I've never seen it done where I thought it looked even remotely like a professional app.
• Trying to set Conditional Formatting to work, every time the value under the Age column changes, is going to be a bear, if at all possible.

All in all, I can't imagine it being worth the effort it would involve...but that's just me! Maybe someone else will have an approach for you.

Linq ;0)>

 

[sneuberg]

i've actually done this before and I had to search through my projects to figure out how. In that project I had to creating a temporary table with code so it was no big deal to add a column I named Format and populate it alternately with True and False when the value changes. So using your example with age I ending up with something like:

Age      Format
45         False 
45         False
45         False
46         True
46         True
47         False
48         True
48         True

And so based on this Format column I could use Conditional Formatting to get the desired effect.

If you can add a field to your table like this and give me the specifics of your situation I could give you the code that would populate a column like this Format column. You would have to rerun the code anytime the table changed so this might not work for you, but if you want to give it a try let me know.

Concerning missinglinq's comment about there being no row backcolor this is certainly true, but Conditional Formatting has multiselect so you can change all of the textboxes and comboboxes in the row. I'd think that would be good enough.

 

[tjarus]

So what I gave was a simple example and not at all what my data actually is - but I might be able to do that Format column - I'm guessing you have code that looks at the field to determine True or False?

Rerunning the table wouldn't be out of the question either.

I have about 40,000+ rows of data and at the end of the day, Excel will do what I want and might even turn out to be a better option but I wanted to see if I could keep everything within Access.

Appreciate any suggestions or help and thanks for responding.

 

[Minty]

If you want to set the whole row a colour then don't set the individual textboxes colours using Conditional Formatting, it's slow on multiple items and even worse on large datasets.

Missinglinq's suggestion about using a single unbound textbox is by far the best way to do this. Set all the field textbox controls background to transparent, then place a single unbound textbox behind those controls that is the same height and the width of the whole detail line. As sneuberg then suggested you are better setting up something in your source query that calculates a flag you can use to set the colour, again as a speed thing this works better in the query than a complicated expression on the form.

 

[sneuberg]

I'm guessing you have code that looks at the field to determine True or False?

That's right. Below is the code I used. We needed so show when the UPCs changed. But if you have 40,000 records this is not a good solution.

Private Sub ApplyFormat()
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim bolFormat As Boolean
Dim LastUPC As String
Set db = CurrentDb
strSQL = "SELECT [Repair Table].[UPC Code], [Repair Table].Format FROM [Repair Table] ORDER BY [Repair Table].[UPC Code];"
Set rs = db.OpenRecordset(strSQL)
If (rs.BOF And rs.EOF) Then
    Exit Sub
End If
rs.Edit
bolFormat = True
rs![Format] = True
LastUPC = rs![UPC Code]
rs.Update
rs.MoveNext
Do While Not rs.EOF
    If rs![UPC Code] <> LastUPC Then
        bolFormat = Not bolFormat
    End If
    rs.Edit
    rs![Format] = bolFormat
    rs.Update
    LastUPC = rs![UPC Code]
    Debug.Print rs![UPC Code] & "  "; rs![Format]
    rs.MoveNext
Loop
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
 
End Sub

 

[tjarus]

That is almost exactly what I am doing - I have a list of NDCs similar to UPCs but pharmaceuticals with additional information and I want to see when the NDC changes. I might give this a shot just to see what happens or it might trigger something else. Thanks!