Select Case for a Recordset(Please Help)

[Guest]

I am building an application for a performance assessment program. I am
trying to build the functionality for it to automatically send an email to
the next person in the review chain when a function "ContinueRouting" is
called.

I have created a query that represents the current snapshot of the routing
chain.
For each person that must recieve the email, there are 2 lcheckboxes. For
instance for the second person in the chain: "Routee2Next" represent that the
first person has completed their review and "Routee2Notified" indicates that
an email has been sent to the second reviewer. Once the second reviewer
completes his review, "Routee3Next" will be "True" and "Routee2Next" will be
"False". This is already programmed into application which creates a
cascading identifier per record of who the next person for notification is.

Problem: I want to perform the continued routing process with out a
form(code Only). I wrote the Select Case Statements to Loop through records
and send email to next reviewer based on status of "RouteenNext" identifier.
It appears that you can not use this function on a recordset or am I doing it
wrong?

Any suggestions to make this functionality work are greatly appreciated

 

[David C. Holley]

Are your fields named Routee2Next, Route2Notified, Routee3Next,
Route3Notified etc.?

If so, I would HIGHLY recommend altering your db structure to spin out
the fields to another table named perhouse tblEmailRouting

The reason for this is that you'll find doing so a bit easier in terms
development and a bit more efficient. You'll avoid design issues later
on in the event that things change. What if in some situations you need
only 1 person to review an item, but in others as many as 10 people? For
each record, you have unneccessary fields. You also gain the advantage
of being able to capture more specific data AND being able to alter a
person's email from 1 table and have the changes cascade throughout the
records (assuming that you have a table that contains all of the
reviewers' information).

tblEmailRouting
lngRecordId Autonumber
[foreignKeyField] FK tieing back to the primary table
intReviewOrder Order in which the person fails when reviewing
the item (1, 2, 3, etc. corresponding to the
first person to review, second person, etc.)
lngReceipiantId FK tieing to a table containing the person's
name, email, phone, etc. The field is here to
further normalize the db.
dteEmailSentDateTime Date & Time the email was sent
dteRecordReviewed Date & Time the item was reviewed
txtStatus Field to capture the response of the person
ACCEPTED, REJECTED, etc. not certain if you'll
make use of this or not, but seemed to be
appropriate

As to the problem - using the db schema above, your code would update
the record in tblEmailRouting (dteRecordReviewed = Now, txtStatus =
Accepted, etc.), grab the review order (intReviewOrder) and then check
to see if there are any additional records (DMax() on intReviewOrder
WHERE [foreignKeyField] = [recordNumber]) and if so, grab the next
person in line. (DLookup (lngRecordId] WHERE [foreignKeyField] =
[recordNumber] AND intReviewOrder = [prior reviewOrder + 1])

As to your current problem, please post the code. It sounds though that
you won't be able to run the code without using a FORM because in either
instance, there's data that you have to collect in order to identify to
whom the next email should be sent.

David H

 

[Guest]

This will be a little confusing.I deleted most of it when I found it wasnt
going to work.

Herer is what is left:
Sub ContinueRouting()
Dim cnn As ADODB.Connection
Dim rstContinueRouting As ADODB.Recordset
Dim RouteTo As String
Dim WhoisNext As Integer
Dim CurrentRecord As Variant

Set cnn = New ADODB.Connection
cnn.Open CurrentProject.BaseConnectionString
rstContinueRouting.Open "qryContinueRouting", cnn, adOpenKeyset,
adLockPessimistic, 2
Stop
rstContinueRouting.MoveFirst

For Each CurrentRecord In rstContinueRouting
Select Case (Routee2Next = True Or Routee3Next = True Or Routee4Next = True
Or Routee5Next = True Or Routee6Next = True)
Case Routee2Next = True
WhoisNext = 2
NotifiedRoutee2 = True
DoCmd.SendObject acSendNoObject, , , Email, , , RouteMessage, True
Case Routee3Next = True
WhoisNext = 3
NotifiedRoutee3 = True
DoCmd.SendObject acSendNoObject, , , Email, , , RouteMessage, True
Case Routee4Next = True
WhoisNext = 4
NotifiedRoutee4 = True
DoCmd.SendObject acSendNoObject, , , Email, , , RouteMessage, True
Case Routee5Next = True
WhoisNext = 5
NotifiedRoutee5 = True
DoCmd.SendObject acSendNoObject, , , Email, , , RouteMessage, True
Case Routee6Next = True
WhoisNext = 6
NotifiedRoutee6 = True
DoCmd.SendObject acSendNoObject, , , Email, , , RouteMessage, True
End Select
Next CurrentRecord
End Sub

The Email field is currently wrong but I am fixing it now. It has to be a
Dlookup().

 

[David C. Holley]

Can't help you when it comes to ADO, but in DAO it would be

Set db = CurrentDB
Set rs = rs.OpenRecordset ("qryContinueRouting")

if rs.EOF = True then
MsgBox ("No records returned for processing")
else
While NOT rs.EOF
[CODE HERE]
rs.MoveNext
Wend
end if

I *still* recommend altering your database schema as I outlined before
as it *will* save you alot of headaches in the future. Using your
current design you'll have to keep expanding the SELECT CASE as the
number of persons reviewing the items increases. With the alternate, its
a design-once approach.

David H

 

[Guest]

Thanks David, I am gonna try your suggestion and hopefully learn a little
more to. I may be back for more help.

Can't help you when it comes to ADO, but in DAO it would be

Set db = CurrentDB
Set rs = rs.OpenRecordset ("qryContinueRouting")

if rs.EOF = True then
MsgBox ("No records returned for processing")
else
While NOT rs.EOF
[CODE HERE]
rs.MoveNext
Wend
end if

I *still* recommend altering your database schema as I outlined before
as it *will* save you alot of headaches in the future. Using your
current design you'll have to keep expanding the SELECT CASE as the
number of persons reviewing the items increases. With the alternate, its
a design-once approach.

David H

This will be a little confusing.I deleted most of it when I found it wasnt
going to work.

Herer is what is left:
Sub ContinueRouting()
Dim cnn As ADODB.Connection
Dim rstContinueRouting As ADODB.Recordset
Dim RouteTo As String
Dim WhoisNext As Integer
Dim CurrentRecord As Variant

Set cnn = New ADODB.Connection
cnn.Open CurrentProject.BaseConnectionString
rstContinueRouting.Open "qryContinueRouting", cnn, adOpenKeyset,
adLockPessimistic, 2
Stop
rstContinueRouting.MoveFirst

For Each CurrentRecord In rstContinueRouting
Select Case (Routee2Next = True Or Routee3Next = True Or Routee4Next = True
Or Routee5Next = True Or Routee6Next = True)
Case Routee2Next = True
WhoisNext = 2
NotifiedRoutee2 = True
DoCmd.SendObject acSendNoObject, , , Email, , , RouteMessage, True
Case Routee3Next = True
WhoisNext = 3
NotifiedRoutee3 = True
DoCmd.SendObject acSendNoObject, , , Email, , , RouteMessage, True
Case Routee4Next = True
WhoisNext = 4
NotifiedRoutee4 = True
DoCmd.SendObject acSendNoObject, , , Email, , , RouteMessage, True
Case Routee5Next = True
WhoisNext = 5
NotifiedRoutee5 = True
DoCmd.SendObject acSendNoObject, , , Email, , , RouteMessage, True
Case Routee6Next = True
WhoisNext = 6
NotifiedRoutee6 = True
DoCmd.SendObject acSendNoObject, , , Email, , , RouteMessage, True
End Select
Next CurrentRecord
End Sub

The Email field is currently wrong but I am fixing it now. It has to be a
Dlookup().
: