Conditional Row Shading on a Continuous Form

Row Shading has come a long way since Access 2003 where you had to use code to get it to happen. Remember this…

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If bluebar Then
Detail.BackColor = 16777215
Else
Detail.BackColor = 13888992
End If

bluebar = Not (bluebar)

End Sub

Then came Access 2007 and we got Alternate Back Color…

Property Sheet Alternate Back Color

While that was way cool, what was still missing was a way to conditionally shade a row. Here’s a way to do just that with no VBA…

Step 1

Note: In this example the Control names are chkInStock, a Check Box and txtInStock, an unbound Text Box.

Identify a Control (*trigger*) that will let Access know you want to highlight that row. For this example we’re using the Check Box, chkInStock.  Then in your unbound Text Box and type…

=IIf([chkInStock]=True,"████████████████████████████████████████████████████████████","")

Stretch the Control to the full length of the Row, set the Font Size to insure it goes the Height of the Row and select the color of the Font for your Control (this is what determines the color of the highlighting).  In this example I used #F6FBDD.

Row Shading (Design Mode)

Side note: For Bar (Full *Black* Block) use *Arial U + 2588 Full Block*.  To insert the Full Block…

a.  Open your Windows Character Map, found under Accessories > System Tools and select the Arial Font from the Font drop down.

Character Map

b. Once selected click Copy and then navigate back to Access

Character Map (Copy)

c. In Access, paste the Full Block within your Statement on the Control Source line…

Property Sheet Control Source

Step 2

You can set your Controls to anything you like, however, I would recommend you give your Controls some sort of Border.  This will not only help ditinguish Columns but also Rows.  In this example all the Controls in the Row are set to…

Back Style:  Transparent
Back Color:  No Color
Border Width:  Hairline
Border Color:  #C0C0C0
Special Effect:  Etched

Step 3 (Optional)

In this example the Detail section of the Form has been set to alternate colors, you can choose whatever option suits your database.  To set the Rows in the Detail section to alternate colors, in the Properties window for the Detail section set the…

Back Color:  Background 1 (or White)
Alternate Back Color:  #F5F4F1

Step 4

Save and you’re all done.  Now, when you select/deselect the In Stock Check Box the row will toggle according. Enjoy!

Download the sample file here.

Row Shading

 3,025 total views,  1 views today