S
State Troopers
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
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