Conditional Formating in Access97

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the following code for conditional formating in forms

Private Sub Comp1_AfterUpdate()
If Me!Comp1 > Me!Comp_Log_Isuue_Date Then
Me!Comp1.BackColor = 65280
ElseIf Me!Comp1 = Me!Comp_Log_Isuue_Date Then
Me!Comp1.BackColor = 12632256
Else: Me!Comp1.BackColor = 16777215

End If
End Sub

Private Sub Comp2_AfterUpdate()
If Me!Comp2 > Me!Comp_Log_Isuue_Date Then
Me!Comp2.BackColor = 65280
ElseIf Me!Comp2 = Me!Comp_Log_Isuue_Date Then
Me!Comp2.BackColor = 12632256
Else: Me!Comp2.BackColor = 16777215

End If
End Sub

The code works fine however I have 45 fields on each of four forms. Each of
the forms have the same field names (Comp1 Comp2 etc), how can I make a
common code that can be called from each of the forms bearing in mind I use
the code for Form_Current() and _AfterUpdate().
 
Keith_R said:
I am using the following code for conditional formating in forms

Private Sub Comp1_AfterUpdate()
If Me!Comp1 > Me!Comp_Log_Isuue_Date Then
Me!Comp1.BackColor = 65280
ElseIf Me!Comp1 = Me!Comp_Log_Isuue_Date Then
Me!Comp1.BackColor = 12632256
Else: Me!Comp1.BackColor = 16777215

End If
End Sub

Private Sub Comp2_AfterUpdate()
If Me!Comp2 > Me!Comp_Log_Isuue_Date Then
Me!Comp2.BackColor = 65280
ElseIf Me!Comp2 = Me!Comp_Log_Isuue_Date Then
Me!Comp2.BackColor = 12632256
Else: Me!Comp2.BackColor = 16777215

End If
End Sub

The code works fine however I have 45 fields on each of four forms. Each of
the forms have the same field names (Comp1 Comp2 etc), how can I make a
common code that can be called from each of the forms bearing in mind I use
the code for Form_Current() and _AfterUpdate().
Click to expand...


This kind of situation is probably symptomatic of a poorly
designed table/form structure. Please reconsider your
design to see if you can figure out how to base your
application on a normalized database instead of a
spreadsheet style.

If you must create these kind of forms, then create a Public
function in a standard module:

Public Sub SetCompColor(frm As Form, _
strCtl As String)
If frm(strCtl) > frm!Comp_Log_Isuue_Date Then
frm(strCtl).BackColor = 65280
ElseIf frm(strCtl) = frm!Comp_Log_Isuue_Date Then
frm(strCtl).BackColor = 12632256
Else
frm(strCtl).BackColor = 16777215
End If
End Sub

With that in place, you can call that procedure from
anywhere in the form's module/events this way:

SetCompColor Me, "Comp2"
 
Thanks for the help. I inherited this database, I will certainly have a look
at redesigning, but this is only the third MS Access DB I have worked on so
you may expect more posts. Again Thanks
 
Well, I wish you good luck. Picking up someone else's old
code is tough enough for an experienced software developer,
but as only your third experience in Access, that's a
serious challenge.
 
Back
Top