Tabular Form, Individual Cell Condition per record

[Stpwaddell]

I have been searching all afternoon for a solution for this.

I have a form which displays all Health and Safety training for individuals and have used this code to refresh the cells on each record of a single record form. This code changes the colour of a cell if the Qualification is out of date or under 30 days remaining as an indicator. I have also created a form In tabular format (To represent a summary view) but no matter what conditioning methods or VB code I use I cannot Change individual cell colours. It changes the whole column and I cannot see what I need to do as I am relatively new to this. The code for the single form is below which seems to do the job but not for tabular.

Any help would be much appreciated

Private Sub Form_Current()

Txt_CSCS_Test_Expires.BackColor = vbWhite
Txt_CPCS_Dumper.BackColor = vbWhite
Txt_CPCS_Roller.BackColor = vbWhite
Txt_CPCS_360.BackColor = vbWhite
Txt_CPCS_Forklift.BackColor = vbWhite
Txt_CPCS_Forklift.BackColor = vbWhite
Txt_Forklift_Inhouse.BackColor = vbWhite
Txt_Dumper_Inhouse.BackColor = vbWhite
Txt_Roller_Inhouse.BackColor = vbWhite
Txt_360_Inhouse.BackColor = vbWhite
Txt_SlingSig_Inhouse.BackColor = vbWhite
Txt_Abrasiv_Wheels.BackColor = vbWhite
Txt_Confined_Spaces.BackColor = vbWhite
Txt_Wessex_Water_Card.BackColor = vbWhite
Txt_Quick_Hitch_Test.BackColor = vbWhite
Txt_Respirator_Test.BackColor = vbWhite
Txt_Streetworks.BackColor = vbWhite
Txt_FirstAid_1day.BackColor = vbWhite
Txt_FirstAid_4days.BackColor = vbWhite
Txt_Manual_Handling.BackColor = vbWhite

If Txt_CSCS_Test_Expires <= Date + 30 Then
Txt_CSCS_Test_Expires.BackColor = 16776960
End If
If Txt_CSCS_Test_Expires <= Date Then
Txt_CSCS_Test_Expires.BackColor = 3158271
End If

If Txt_CPCS_Dumper <= Date + 30 Then
Txt_CPCS_Dumper.BackColor = 16776960
End If
If Txt_CPCS_Dumper <= Date Then
Txt_CPCS_Dumper.BackColor = 3158271
End If

If Txt_CPCS_Roller <= Date + 30 Then
Txt_CPCS_Roller.BackColor = 16776960
End If
If Txt_CPCS_Roller <= Date Then
Txt_CPCS_Roller.BackColor = 3158271
End If

If Txt_CPCS_360 <= Date + 30 Then
Txt_CPCS_360.BackColor = 16776960
End If
If Txt_CPCS_360 <= Date Then
Txt_CPCS_360.BackColor = 3158271
End If

If Txt_CPCS_Forklift <= Date + 30 Then
Txt_CPCS_Forklift.BackColor = 16776960
End If
If Txt_CPCS_Forklift <= Date Then
Txt_CPCS_Forklift.BackColor = 3158271
End If

If Txt_CPCS_Forklift <= Date + 30 Then
Txt_CPCS_Forklift.BackColor = 16776960
End If
If Txt_CPCS_Forklift <= Date Then
Txt_CPCS_Forklift.BackColor = 3158271
End If

If Txt_Forklift_Inhouse <= Date + 30 Then
Txt_Forklift_Inhouse.BackColor = 16776960
End If
If Txt_Forklift_Inhouse <= Date Then
Txt_Forklift_Inhouse.BackColor = 3158271
End If

If Txt_Dumper_Inhouse <= Date + 30 Then
Txt_Dumper_Inhouse.BackColor = 16776960
End If
If Txt_Dumper_Inhouse <= Date Then
Txt_Dumper_Inhouse.BackColor = 3158271
End If

If Txt_Roller_Inhouse <= Date + 30 Then
Txt_Roller_Inhouse.BackColor = 16776960
End If
If Txt_Roller_Inhouse <= Date Then
Txt_Roller_Inhouse.BackColor = 3158271
End If

If Txt_360_Inhouse <= Date + 30 Then
Txt_360_Inhouse.BackColor = 16776960
End If
If Txt_360_Inhouse <= Date Then
Txt_360_Inhouse.BackColor = 3158271
End If

If Txt_SlingSig_Inhouse <= Date + 30 Then
Txt_SlingSig_Inhouse.BackColor = 16776960
End If
If Txt_SlingSig_Inhouse <= Date Then
Txt_SlingSig_Inhouse.BackColor = 3158271
End If

If Txt_Abrasiv_Wheels <= Date + 30 Then
Txt_Abrasiv_Wheels.BackColor = 16776960
End If
If Txt_Abrasiv_Wheels <= Date Then
Txt_Abrasiv_Wheels.BackColor = 3158271
End If

If Txt_Confined_Spaces <= Date + 30 Then
Txt_Confined_Spaces.BackColor = 16776960
End If
If Txt_Confined_Spaces <= Date Then
Txt_Confined_Spaces.BackColor = 3158271
End If

If Txt_Wessex_Water_Card <= Date + 30 Then
Txt_Wessex_Water_Card.BackColor = 16776960
End If
If Txt_Wessex_Water_Card <= Date Then
Txt_Wessex_Water_Card.BackColor = 3158271
End If

If Txt_Quick_Hitch_Test <= Date + 30 Then
Txt_Quick_Hitch_Test.BackColor = 16776960
End If
If Txt_Quick_Hitch_Test <= Date Then
Txt_Quick_Hitch_Test.BackColor = 3158271
End If


If Txt_Respirator_Test <= Date + 30 Then
Txt_Respirator_Test.BackColor = 16776960
End If
If Txt_Respirator_Test <= Date Then
Txt_Respirator_Test.BackColor = 3158271
End If

If Txt_Streetworks <= Date + 30 Then
Txt_Streetworks.BackColor = 16776960
End If
If Txt_Streetworks <= Date Then
Txt_Streetworks.BackColor = 3158271
End If

If Txt_FirstAid_1day <= Date + 30 Then
Txt_FirstAid_1day.BackColor = 16776960
End If
If Txt_FirstAid_1day <= Date Then


Txt_FirstAid_1day.BackColor = 3158271
End If

If Txt_FirstAid_4days <= Date + 30 Then
Txt_FirstAid_4days.BackColor = 16776960
End If
If Txt_FirstAid_4days <= Date Then
Txt_FirstAid_4days.BackColor = 3158271
End If

If Txt_Manual_Handling <= Date + 30 Then
Txt_Manual_Handling.BackColor = 16776960
End If
If Txt_Manual_Handling <= Date Then
Txt_Manual_Handling.BackColor = 3158271
End If

End Sub

[weekend00]

you need to set "conditioning format" for each field in form design view.