Looping

[Guest]

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

 

[Guest]

I have a possibility that would fix all this madness.

Is it possible to "append" a row, possibly 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

.......becomes, after some code....

ID CAT COMMENT CLOSE DATE
2006-001 1 testing.... CLOSED 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

....that would make everything VERY easy.
Better stated, is it possible to add "CLOSED" in the CLOSE field for every
ID, CATEGORY specified?

Thanks again.
-State

 

[Guest]

Forgot to state, can this be done using a SQL procedure?

 

[Guest]

Got it.

No need for all that mumbo jumbo coding.

Cheers
-State

 

[Marshall Barton]

Good work State.

I was trying to figure out what the problem was, but you
were posting updates faster than I could get a grip on the
issue ;-)