Hi there.

this is going to be a rather lenthy post. But I will try and explain my
problem as clearly as possible.

Firstly, I have a table(tblActions). In this table, I have several fields.
they are: ID, CATEGORY, COMMENT, CLOSED, and DATE

Secondly, the process: The Form is divided into 5 sections, each one of
these sections represents a CATEGORY number. The user enters comments into
these fields. The ID is also stored from user input(the ID is in this format
- 2006-001). The date is automatticaly saved upon submitting the form. Once
an actions(CATEGORY) is completed, it is closed.

The problem:
The user has 30 days to complete the action(single CATEGORY..there are 5
actions that need completion, but all can be different time points)
On the 30th day after an action has been entered an email is sent
automatically to the user, telling them which CATEGORY needs to be completed,
and from which ID.
The problem here is that when the user enters data it looks like this:

ID              CAT     COMMENT    CLOSE       DATE
2006-001      1       testing....                       4/25/2006
2006-001      1       CLOSED        CLOSED     4/27/2006
2006-001      2       test..1..2                        4/23/2006
2006-001      2       more to do.                    4/25/2006

As you can see, the first time the user enters the information there is no
CLOSED field tagged with it.

I have the email part working, with everything else. The part I am having
trouble with is trying to code in that when it reads that 4/25/2006 is 30
days that it much keep checking for CLOSED. And then if CLOSED doesnt exist,
send the email telling them which ID and CATEGORY needs to be completed.

Heres the working code:

       If strComment = "" Then
   
       Else

           If stDiff30 = 0 Then
           
                   If stDiff48 = 0 Then
                   
                           If stDiff60 = 0 Then
                                       
                               If strDbLine = strLn Then
                               
                                   rsMyRS.MoveNext
                               Else
                               'go
                               message = message & vbNewLine & vbNewLine &
"DIR: " & strDir & vbNewLine & "-Corrective Action Number " & id & " has not
been closed. It has been 30 Days since it was opened."
                               rsMyRS.MoveNext
                               End If
               
                           Else
                       
                           'Debug.Print "60"
                           rsMyRS.MoveNext
                           End If
           
                   
                           If strDbLine = strLn Then
                           
                               rsMyRS.MoveNext
                               
                           Else
                           'go
                           message = message & vbNewLine & vbNewLine &
"DIR: " & strDir & vbNewLine & "-Corrective Action Number " & id & " has not
been closed. It has been 30 Days since it was opened."
                           rsMyRS.MoveNext
                           End If
                   
               
                   Else
               
                   'Debug.Print "48"
                   rsMyRS.MoveNext
                   End If
               
               If strDbLine = strLn Then 'if CLOSED, move on

                   rsMyRS.MoveNext
                   
               Else
               'go        
               rsMyRS.MoveNext                  
               End If            
           Else        
           'Debug.Print "-->30"
           rsMyRS.MoveNext            
           End If
       
       End If

And this is me just think aloud, trying to find a way around this:

strClosed(0)    = 001
strCA(0)    = 1

message30(001)= C.A 1 still open
message48(001)= C.A 2 still open
message60(001)= C.A 3 still open

---------------------------------

strNewDir = Right(strDir, 3)

for x=lbound(strClosed) to ubound(rsMyRS.recordCount) step 1

        if strBoolean(2006-001) = strCA(x) then 'if boolean = strCA
       
               

        else

        message30(001) = vbNewLine & vbNewLine & "DIR: " & strDir & "   (Opened by
" & strUser & ")" & vbNewLine & "-Corrective Action " & id & " has not been
closed. It has been 30 days since it was opened."

        end if

next x

finally, what i need: say the first record it checks is not CLOSED, it
should keep checking the same ID AND CATEGORY fields, looking for CLOSED.
If no CLOSED field is found, then add the EMAIL message to a string, which
is then sent when it has checked EVERY record.

So instead of emailing after every ID check and sending 10 emails(if 10
CATEGORIES have yet to be completed) it will send 1 email - with 10
instructions as to which ID/CATEGORY needs to be completed.

I know it's long. But I tried to explain everything as simple as possible.

Thanks.
-State