dynamically apply colors to entries in a subform?

[planetjeff]

Hello all,
I have three tables: Events (with pk EventID), Adjudicators (with pk AdjudicatorID, and Assignments (with pk EventID,AdjudicatorID) which tracks a many-to-many relationship. Events can have many judges (adjudicators), and judges can judge many events.

In my form for assigning events to adjudicators, I have two subforms. One is for displaying currently assigned events, and the other is for displaying all events. What I'd like to do is iterate through the all events subform and color-code each event base on if a) event is already assigned, b) event overlaps datewise an already assigned event, or c) event not assigned and doesn't overlap currently assigned event. The Events table also has a StartDate and EndDate fields to do these comparisons.

Usually in such situations, this would call for a nested for loop construct: for each entry in all events, traverse through each entry in a judge's current assigned list and make the above checks, then color the displayed record accordingly.

Can this be done in Access? If so, can it be done with a nested DAO requests, or is there some other way? Any input would be greatly appreciated. Thanks,

jeff

 

[willir]

Jeff

Use of colours in single form is fairly easy.

If Condition1 Then
Me.YourField.BackColor = vbRed
Else
Me.YourField.BackColor = vbBlue
End If


Or instead of using If ... Then, you can use a Select CASE / End Select.


...With a continuous form, using colours is not easily done. When you set a colour for one record, all displayed records will display the same attribute. This is because a continuous form is the same form displayed over and over.

However, you can use Condifitional formatting to have some control. In design mode, select the control (text, combo or list box), and select "Fromat" -> "Conidtional formatting".

Conditional formatting uses test conidtions. If value > x, value = x or value < x. This makes it a little tough for mor ecomplex conditions. A work-around is sometimes possible by using a function.

For example...

Create an unbound text box, and for the ControlSource..
=TestDate(YourPrimaryKey)

Then create a TestDate function..

Function TestDate(MyPrimaryKey as Variant) as String

'Perform a test on the record as determined by the
'primary key, and then take appropriate action

Select Case YourTestCondition

Case a
TestDate = "Late"
Case b
TestDate = "Due"
Case c
TestDate = "NotDue"
End Select

End Function


Then for the unbound field, use conditional formatting for Late, Due and NotDue.

Richard