Access 2007 Continuous form vba code

[gthomas via AccessMonster.com]

I have a continuous form that display a number of fields from a table. For
some fields I need to highlight the background with a different color
depending on other field values with in the same record when the form opens.

sample code which does not work the way i thought it would.

Private Sub Form_Current()

Dim lngGreen As Long, lngRed As Long, lngOrange As Long, lngBlue As Long,
lngYellow As Long, lngWhite, lngBlack As Long

lngGreen = RGB(34, 139, 34)
lngYellow = RGB(255, 255, 0)
lngOrange = RGB(255, 97, 3)
lngBlue = RGB(0, 0, 255)
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngWhite = RGB(255, 255, 255)

If Me.PPGCAutoReorder = "N" Then Me![PPGCNeed].BackColor = lngGreen
If Me.WGCAutoReorder = "N" Then Me![WGCNeed].BackColor = lngGreen
If Me.AmcalAutoReorder = "N" Then Me![AmcalNeed].BackColor = lngGreen
If Me.WKalAutoReorder = "N" Then Me![WKalNeed].BackColor = lngGreen
If Me.PPKAutoReorder = "N" Then Me![PPKNeed].BackColor = lngGreen
If Me.WKelmAutoReorder = "N" Then Me![WKelmNeed].BackColor = lngGreen

... there are more comples conditions that foloow this.

 

[Marshall Barton]

I have a continuous form that display a number of fields from a table. For
some fields I need to highlight the background with a different color
depending on other field values with in the same record when the form opens.

sample code which does not work the way i thought it would.

Private Sub Form_Current()

Dim lngGreen As Long, lngRed As Long, lngOrange As Long, lngBlue As Long,
lngYellow As Long, lngWhite, lngBlack As Long

lngGreen = RGB(34, 139, 34)
lngYellow = RGB(255, 255, 0)
lngOrange = RGB(255, 97, 3)
lngBlue = RGB(0, 0, 255)
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngWhite = RGB(255, 255, 255)

If Me.PPGCAutoReorder = "N" Then Me![PPGCNeed].BackColor = lngGreen
If Me.WGCAutoReorder = "N" Then Me![WGCNeed].BackColor = lngGreen
If Me.AmcalAutoReorder = "N" Then Me![AmcalNeed].BackColor = lngGreen
If Me.WKalAutoReorder = "N" Then Me![WKalNeed].BackColor = lngGreen
If Me.PPKAutoReorder = "N" Then Me![PPKNeed].BackColor = lngGreen
If Me.WKelmAutoReorder = "N" Then Me![WKelmNeed].BackColor = lngGreen

.. there are more comples conditions that foloow this.

Your code would work fine for a form in single view, but for
continuous forms, you need to use the Format - Conditional
Formatting menu item on each of the text boxes. To format a
text box based on a value in another bound control, use the
Expression is option with something like:
=[PPGCAutoReorder] = "N"
and choose the color from the BackColor color picker.

Note that the [ ] are required in this situation.