add one condition

[travismp]

Private Sub Form_Current()

    CheckColor

End Sub


Public Sub CheckColor2()

    'using a recordset will be faster, but you might not notice
    Dim rs As dao.Recordset
    
    'get the recordset from the subform
    Set rs = Me.frm_TESTVER_TEST_HIST.Form.Recordset.Clone
    
    'search for POSITIVE in the subform's recordset
    rs.FindFirst "[TestResult] = 'POSITIVE'"
    
    'if POSITIVE wasn't found, go grey
    If rs.NoMatch Then
        Me.Detail.BackColor = -2147483633
        
    'otherwise, go red
    Else
        Me.Detail.BackColor = 255
    End If
    
    'close the recordset
    rs.Close
    
    'make sure you clean up your objects
    Set rs = Nothing

End Sub


Public Sub CheckColor()

    'use the built in DLookup function... it's a bit slower
    'if no records exist for this SS with POSITIVE, go grey
    If IsNull(DLookup("TestResult", "tbl_TESTS", "EmpSS = '" & Me.SSN & "' AND TestResult = 'POSITIVE'")) Then
        Me.Detail.BackColor = -2147483633
    'otherwise, go red
    Else
        Me.Detail.BackColor = 255
    End If

End Sub

This is a code that compairs a field in a subform to a field in the current form. It works great now. I need to make one change I am not sure how. Right now if a previous test is "POSITIVE" the current screen will go red. I need to change it to say IF a pervious test is "POSITIVE" OR "REFUSAL" then go red.

It looks like I am going to make 2 changes, but I am not sure how to do it correctly. Thank you all for the advice.

 

[ajetrumpet]

Change this:

    'search for POSITIVE in the subform's recordset
    rs.FindFirst "[TestResult] = 'POSITIVE'"
    
    'if POSITIVE wasn't found, go grey
    If rs.NoMatch Then
        Me.Detail.BackColor = -2147483633
        
    'otherwise, go red
    Else
        Me.Detail.BackColor = 255
    End If

To this:

    'search for POSITIVE in the subform's recordset
    rs.FindFirst "[TestResult] = 'POSITIVE'"
    
    'if POSITIVE wasn't found, [COLOR="Red"]'TRY AGAIN[/COLOR]
    If rs.NoMatch Then
      rs.FindFirst "[TestResult] = 'REFUSAL'"

        If rs.NoMatch Then [COLOR="Silver"][COLOR="Gray"]'GO GREY[/COLOR][/COLOR]
          Me.Detail.BackColor = -2147483633
        End If
        
    'otherwise, go red
    Else
        Me.Detail.BackColor = 255
    End If

This might not be the fastest way, but it's the best.

 

[travismp]

thank you Adam

Thank you for the help so far.

Do I even need this code in my VB

Public Sub CheckColor()

    'use the built in DLookup function... it's a bit slower
    'if no records exist for this SS with POSITIVE, go grey
    If IsNull(DLookup("TestResult", "tbl_TESTS", "EmpSS = '" & Me.SSN & "' AND TestResult = 'POSITIVE'")) Then
        Me.Detail.BackColor = -2147483633
    'otherwise, go red
    Else
        Me.Detail.BackColor = 255
    End If

End Sub

 

[ajetrumpet]

I have no idea Travis, but I can tell you that it doesn't do the same thing. That block searches for a specific record based on the SSN in your form:

If IsNull(DLookup("TestResult", "tbl_TESTS", _
   "EmpSS = [COLOR="Red"]'" & Me.SSN & "'[/COLOR] AND TestResult = 'POSITIVE'")) Then

Whereas the other Sub just searches through all of the records. So NO, the conditions of the two Subs are not the same.

 

[travismp]

OK, so if it is needed what would I have to add to that VB to also look at 'Refusal'

If IsNull(DLookup("TestResult", "tbl_TESTS", _
"EmpSS = '" & Me.SSN & "' AND TestResult = 'POSITIVE' OR 'REFUSAL")) Then


would this work? Sorry I suck at this stuff man.

 

[ajetrumpet]

would this work?

If IsNull(DLookup("TestResult", "tbl_TESTS", _
"EmpSS = '" & Me.SSN & "' AND TestResult = 'POSITIVE' OR 'REFUSAL")) Then

Probably not, use this instead:

If IsNull(DLookup("TestResult", "tbl_TESTS", _
   "EmpSS = '" & Me.SSN & "' AND _
      (TestResult = 'POSITIVE' OR TestResult = 'REFUSAL'"))

 

[travismp]

Adam I have been sick for a few days so I did not have a chance to check this. I will try this script in the morning. THANK YOU VERY MUCH FOR THE HELP.

 

[ajetrumpet]

Adam I have been sick for a few days so I did not have a chance to check this. I will try this script in the morning. THANK YOU VERY MUCH FOR THE HELP.

Not a problem Travis. Let me know how it goes...