Access 2003: Problem with DMAX() and conditional formatting

[scarface85]

Problem is I am trying to apply conditional formatting in a main form based
on the values in a form at a lower level. I am using the DMAX function along
with defined expressions to try and get this to work. I was able to get
these methods to work at a different level, but for some reason the following
just wont work for me.
Can anyone see the problem with this. Any help is much appreciated.
Cheers

Details:
There are three states that im am comparing with DMAX..each identified by 1,
2, 3. What should happen is that if the max state is 1 then the box should
be hilighted green...if 2 then yellow..3 = red. At the moment the only value
that comes up is 3 (red) when it should be either 1 or 2 and also the DMAX
equals the same value for all the records on the form. You will see in the
code that I refer to txtSubSystem which is a textbox that is used to show the
ID for each individual record.

I was able to get the following test query to succesfully get the MAX value
but the DMAX and expressions wont work at all.

QUERY:
SELECT Max([Sub-Assembly].StateofSubAssembly) AS MAXSTATE
FROM [Sub-System] INNER JOIN (Equipment INNER JOIN [Sub-Assembly] ON
Equipment.EquipmentID=[Sub-Assembly].EquipmentID) ON [Sub-System].
SubSystemID=Equipment.SubSystemID;

Expressions:
Private Sub Form_Current()
Dim ctl As Control, n As Integer
Dim lngRed As Long
Dim lngGreen As Long
Dim lngYellow As Long
Dim SubSysID As String

lngRed = RGB(255, 0, 0)
lngGreen = RGB(0, 255, 0)
lngYellow = RGB(255, 255, 0)

Me!txtDetails.FormatConditions.Delete

Dim exp1, exp2, exp3 As String

SubSysID = Me.txtSubSystem

exp1 = "DMAX(""[Sub-Assembly.StateofSubAssembly]"", ""Sub-Assembly"", ""
[Equipment.EquipmentID] = [Sub-Assembly.EquipmentID]"" AND ""[Equipment.
SubSystemID] = [Sub-System.SubSystemID]"" AND ""[Sub-System.SubSystemID] =
txtSubSystem)" & "=""1"""
exp2 = "DMAX(""[Sub-Assembly.StateofSubAssembly]"", ""Sub-Assembly"", ""
[Equipment.EquipmentID] = [Sub-Assembly.EquipmentID]"" AND ""[Equipment.
SubSystemID] = [Sub-System.SubSystemID]"" AND ""[Sub-System.SubSystemID] =
txtSubSystem)" & "=""2"""
exp3 = "DMAX(""[Sub-Assembly.StateofSubAssembly]"", ""Sub-Assembly"", ""
[Equipment.EquipmentID] = [Sub-Assembly.EquipmentID]"" AND ""[Equipment.
SubSystemID] = [Sub-System.SubSystemID]"" AND ""[Sub-System.SubSystemID] =
txtSubSystem)" & "=""3"""

Dim fmc As FormatCondition
Set fmc = Me!txtDetails.FormatConditions.Add(acExpression, , exp1)
Set fmc = Me!txtDetails.FormatConditions.Add(acExpression, , exp2)
Set fmc = Me!txtDetails.FormatConditions.Add(acExpression, , exp3)

With Me!txtDetails.FormatConditions(0)
.BackColor = lngGreen
End With
With Me!txtDetails.FormatConditions(1)
.BackColor = lngYellow
End With
With Me!txtDetails.FormatConditions(2)
.BackColor = lngRed
End With

Debug.Print Expression()

' If the number of records in the subform
' is greater than 9, display the
' vertical scrollbar.
If Me.RecordsetClone.RecordCount > 9 Then
Me.ScrollBars = 2
Else
Me.ScrollBars = 0
End If
End Sub