| Christaaay 2005-12-19, 8:26 pm |
| I am attempting to use the code below on a data access page to assign a
number to each record entered on the dap. the queries called specify the
numbers to use and the code is set up to make sure that only the specified
numbers are used and cycled through. Once all numbers have been used the
cycle should start over again. This is used to route records to 'work lists'
or 'queues' evenly to be worked. Any help you can give me would be great!
Function FetchQueueValue()
blnQueueNotInLastCyc
le = False
strCycledQueues = "SELECT * FROM tbl_AssocTbl " & _
"WHERE (((tbl_AssocTbl.cycled_ind)=True) AND
((tbl_AssocTbl.Queue) Is Not Null));"
strLastQueueCycleSeq
uence = "SELECT * FROM qryGetQueueRoutingCy
cle"
Set rstQueuesToCycle = CreateObject("ADODB.Recordset")
Set rstLastQueueCycleSeq
uence = CreateObject("ADODB.Recordset")
rstQueuesToCycle.Open strCycledQueues, MSODSC.Connection
rstLastQueueCycleSeq
uence.Open strLastQueueCycleSeq
uence, MSODSC.Connection
rstQueuesToCycle.MoveLast
If rstQueuesToCycle.RecordCount <> 0 Then 'if records exist
rstQueuesToCycle.MoveFirst 'go to first record
Do Until rstQueuesToCycle.EOF Or blnQueueNotInLastCyc
le = True 'loop
through cycled Queues
rstLastQueueCycleSeq
uence.MoveFirst
rstLastQueueCycleSeq
uence.Find "Queue_Key='" &
rstQueuesToCycle.Fields.item("queue").value & "'" 'check if a Queue was in
the last cycle
If rstLastQueueCycleSeq
uence.EOF Then 'if queue was not in last cycle
blnQueueNotInLastCyc
le = True 'then set indicator to true and
exit the loop
Exit Do
End If
rstQueuesToCycle.MoveNext 'if queue was in last cycle then check the
next queue
Loop
If blnQueueNotInLastCyc
le = True Then 'sets the routing to either the
queue not already in the cycle
strReceivingQueue = rstQueuesToCycle.Fields.item("queue").value
Else
rstLastQueueCycleSeq
uence.MoveFirst 'or the queue at the beginning
of the last cycle
strReceivingQueue =
rstLastQueueCycleSeq
uence.Fields.item("queue_key").value
End If
FetchQueueValue = strReceivingQueue
End If
rstLastQueueCycleSeq
uence.Close
rstQueuesToCycle.Close
'Set rstQueueSequence = Nothing
Set rstQueuesToCycle = Nothing
End Function
|