Conditional Formatting

RXTypeID is on the many side of a one-to-many relationship; it can hold many values. Two compulsory values are RXTypeID = 2 and RXTypeID = 3. If one or both are not present, control must change color.

so, you might have several rows, and you have (at least) 2 rows, with the values 2 and 3, then put the control in green, otherwise in red?

based on which event do you want to check for the color? on form-load? on button-click?

is there some other value/control on the form, based on which you have to filter the rows to check for?

That's correct, the form control must color on form-load.
The form lists all the table records, unfiltered, in a read-only datasheet (there are other fields too, in addition to RxTypeID). Both RxTypeID = 2 and  RxTypeID = 2 must be present in each record, otherwise control must change color.

Sorry  I have given you wrong information; please leave it for now. I will repost later. Sorry about this.

This is how it should work; control must change color if both ISFALSE(RxTypeID = 2) and ISFALSE(RxTypeID = 3).

If only one of the above is false, then that's ok because I have another condition set up to catch for one missing value.

In the conditional format tool I initially coded IsNull([RxTypeID])=True but this only partially works because if, in one record,  RxTypeID = 1 and nothing else, the control does not color because now  (IsNull([RxTypeID])=False.  

Think I have described it correctly now
Thanks

in vba:

Dim c2 as Integer
Dim c3 as Integer
c2 = DSUM("1", "yourtable", "RxTypeID = 2")  
c3 = DSUM("1", "yourtable", "RxTypeID = 3")

if (c2 > 0 and c2> 3) then
  'both row types exist
else
  'at least one of the 2 values is missing
end if

OK, dont understand the Dsum("1",..)
Is this saying if RxTypeID = 2, then return a 1.
Would it also work if I used the record key in the expression; then for two values (2 and 3) it would sum 2 (1+1).

I will work on it tonight and let you know
Thanks very much

Have not been able to figure it out; will look again this weekend
Thanks

Should it be
if (c2 > 0 and c3>0) then
  'both row types exist

Have tried this, but getting "wrong number of arguments or invalid property assignment error" in the IsNull expression. Did the IsNull test because was getting a null error.
Am I on the right track?
Thanks

Private Sub Form_Current()

Dim c2 As Integer
Dim c3 As Integer

c2 = IIf(IsNull(Me.Requirement, 0, DSum("1", "Requirements", "RxTypeID = 2")))
c3 = IIf(IsNull(Me.Requirement, 0, DSum("1", "Requirements", "RxTypeID = 3")))

If (c2 > 0 And c3 > 0) Then Me.Requirement.BackColor = White      'both of the values exist
ElseIf (c2 > 0 Or c3 > 0) Then Me.Requirement.BackColor = White   'at least one of the 2 values exists
Else: Me.Requirement.BackColor = Beige    'both of the values are missing
End If

End Sub

OK got the code running but not returning the correct colors yet; will continue working on it

Private Sub Form_Current()

Dim c2 As Integer
Dim c3 As Integer

c2 = IIf(IsNull(Me.Requirement), 0, DSum("1", "Requirements", "RxTypeID = 2"))
c3 = IIf(IsNull(Me.Requirement), 0, DSum("1", "Requirements", "RxTypeID = 3"))
'c2 = DSum("1", "Requirements", "RxTypeID = 2")
'c3 = DSum("1", "Requirements", "RxTypeID = 3")

If (c2 > 0 And c3 > 0) Then
    Me.Requirement.BackColor = White    'both values present
ElseIf (c2 > 0 Or c3 > 0) Then
    Me.Requirement.BackColor = White    'one value present
Else: Me.Requirement.BackColor = Beige  'no values present
End If

End Sub

Actually, above did not work, was still getting null errors under some conditions. Have reworked it and now seems to be working for all value combinations. Except cell is not changing color. Have checked the code and c2 is being fed a 1 or  0 under the right conditions. But when it gets a 1, cell color does not change to beige. Any ideas.
Thanks

Private Sub Form_Current()
Dim c2 As Integer

c2 = IIf(IsNull(Me.Requirement) Or ((RxTypeID) <> "3" And (RxTypeID) <> "4"), 1, 0)

If (c2 > 0) Then
    Me.Requirement.BackColor = Beige    
Else: Me.Requirement.BackColor = White  
End If

End Sub

Hi
Any feedback on this one; I can step througth the following code but when c2 holds a value of 1, it does not change "requirement.backcolor" to vbBrown. vbBrown has value Empty. Have not been able to figure why it does not work.
Thanks.

Private Sub Form_Load()
Dim c2 As Integer

c2 = IIf(IsNull(Me.Requirement) Or ((RxTypeID) <> "3" And (RxTypeID) <> "4"), 1, 0)

If c2 > 0 Then
    Me.Requirement.BackColor = vbBrown
Else
    Me.Requirement.BackColor = vbWhite

End If
End Sub

Requirement backstyle is normal, not transparent

Getting closer; have changed color to Access codes and in step-thru "Me.Requirement.Backcolor" now changes; but the control still does not change ???

Private Sub Form_Load()
Dim c2 As Integer

c2 = IIf(IsNull(Me.Requirement) Or ((RxTypeID) <> "3" And (RxTypeID) <> "4"), 1, 0)

If c2 > 0 Then
    Me.Requirement.BackColor = 14480885
Else
    Me.Requirement.BackColor = 14150650

End If
End Sub

OK I've got it working, but only in single form, not datasheet; need it to work in datasheet.
Endless struggle, just as well this is not my job ....