Help assigning leads to salespeople in "Round Robin" fashion!

[Bob]

I have an Access application where I need to automatically forward
emails to a group of 12 salespeople as soon as they arrive. The first
email needs to go to the first salesperson on the list, the second
email to the second salesperson on the list until I get to the end of
the list, then start over. I also need to be able to exclude
salespeople on their day off. The email portion of the application
will be using a rule in Outlook 2003 that runs a VBA project.

I was thinking of having a table with the salespersons name, email
address and some kind of flag value that I could have my VBA code look
for.. but thats where my lack of experience and knowledge becomes
apparent. I'm especially not sure of how to reset the process and
start over.

Does anyone have any suggestions or examples that may help?

Thank you in advance for any help you might havr!

Bob

[Tom van Stiphout]

On 5 Dec 2006 03:36:09 -0800, "Bob" <te*******@bellsouth.netwrote:

When I did something like this, I kept the value of the
EmployeeID_LastSent, so that next time I could go to the one after
that. That was done using rs.Move, with special handling for
end-of-recordset which meant going back to the first record.
In your case the recordset would only hold EmployeeIDs of salespeople
that are present in the office today.

-Tom.

>I have an Access application where I need to automatically forward
emails to a group of 12 salespeople as soon as they arrive. The first
email needs to go to the first salesperson on the list, the second
email to the second salesperson on the list until I get to the end of
the list, then start over. I also need to be able to exclude
salespeople on their day off. The email portion of the application
will be using a rule in Outlook 2003 that runs a VBA project.

I was thinking of having a table with the salespersons name, email
address and some kind of flag value that I could have my VBA code look
for.. but thats where my lack of experience and knowledge becomes
apparent. I'm especially not sure of how to reset the process and
start over.

Does anyone have any suggestions or examples that may help?

Thank you in advance for any help you might havr!

Bob

[Bob]

Tom,

Thanks for getting back to me so quickly.

I'm not sure if I can do what you're suggesting unless I store the
employee ID in some kind of temp table.

I'm using a VBA project to look at a table and determine the last
person that got mail, then send to the next one. After all of the
people have gotton mail, start over at the beginning. Can you explain
the special handling at the end of the recordset you're describing? Or
a reference to similiar code sample? That's where I seem to be having
the most trouble. I'm not sure if I should use DAO or ADO for my
connection to that table and if I should use Do until EOF or other
options.

Bob
Tom van Stiphout wrote:

On 5 Dec 2006 03:36:09 -0800, "Bob" <te*******@bellsouth.netwrote:

When I did something like this, I kept the value of the
EmployeeID_LastSent, so that next time I could go to the one after
that. That was done using rs.Move, with special handling for
end-of-recordset which meant going back to the first record.
In your case the recordset would only hold EmployeeIDs of salespeople
that are present in the office today.

-Tom.

I have an Access application where I need to automatically forward
emails to a group of 12 salespeople as soon as they arrive. The first
email needs to go to the first salesperson on the list, the second
email to the second salesperson on the list until I get to the end of
the list, then start over. I also need to be able to exclude
salespeople on their day off. The email portion of the application
will be using a rule in Outlook 2003 that runs a VBA project.

I was thinking of having a table with the salespersons name, email
address and some kind of flag value that I could have my VBA code look
for.. but thats where my lack of experience and knowledge becomes
apparent. I'm especially not sure of how to reset the process and
start over.

Does anyone have any suggestions or examples that may help?

Thank you in advance for any help you might havr!

Bob

[Tom van Stiphout]

On 5 Dec 2006 06:02:59 -0800, "Bob" <te*******@bellsouth.netwrote:

Storing the last EmpID in a "temp" table is fine.
DAO is fine when working with Access (better: Jet) database.

dim rs as Dao.Recordset

'Open Recordset

rs.FindFirst "EmpID=" & EmpID_LastSent
if rs.NoMatch then Msgbox "Aaarrcchhh, LastSent was not found!"
else
rs.MoveNext
if rs.EOF then rs.MoveFirst
Msgbox "Next up is " & rs!EmpID
end if

-Tom.

>Tom,

Thanks for getting back to me so quickly.

I'm not sure if I can do what you're suggesting unless I store the
employee ID in some kind of temp table.

I'm using a VBA project to look at a table and determine the last
person that got mail, then send to the next one. After all of the
people have gotton mail, start over at the beginning. Can you explain
the special handling at the end of the recordset you're describing? Or
a reference to similiar code sample? That's where I seem to be having
the most trouble. I'm not sure if I should use DAO or ADO for my
connection to that table and if I should use Do until EOF or other
options.

Bob
Tom van Stiphout wrote:

>On 5 Dec 2006 03:36:09 -0800, "Bob" <te*******@bellsouth.netwrote:

When I did something like this, I kept the value of the
EmployeeID_LastSent, so that next time I could go to the one after
that. That was done using rs.Move, with special handling for
end-of-recordset which meant going back to the first record.
In your case the recordset would only hold EmployeeIDs of salespeople
that are present in the office today.

-Tom.

>I have an Access application where I need to automatically forward
emails to a group of 12 salespeople as soon as they arrive. The first
email needs to go to the first salesperson on the list, the second
email to the second salesperson on the list until I get to the end of
the list, then start over. I also need to be able to exclude
salespeople on their day off. The email portion of the application
will be using a rule in Outlook 2003 that runs a VBA project.

I was thinking of having a table with the salespersons name, email
address and some kind of flag value that I could have my VBA code look
for.. but thats where my lack of experience and knowledge becomes
apparent. I'm especially not sure of how to reset the process and
start over.

Does anyone have any suggestions or examples that may help?

Thank you in advance for any help you might havr!

Bob

[Bob]

Tom,

Thanks again for your help!

I'll be trying out today and may post more questions later.

Regards,

Bob
Tom van Stiphout wrote:

On 5 Dec 2006 06:02:59 -0800, "Bob" <te*******@bellsouth.netwrote:

Storing the last EmpID in a "temp" table is fine.
DAO is fine when working with Access (better: Jet) database.

dim rs as Dao.Recordset

'Open Recordset

rs.FindFirst "EmpID=" & EmpID_LastSent
if rs.NoMatch then Msgbox "Aaarrcchhh, LastSent was not found!"
else
rs.MoveNext
if rs.EOF then rs.MoveFirst
Msgbox "Next up is " & rs!EmpID
end if

-Tom.

Tom,

Thanks for getting back to me so quickly.

I'm not sure if I can do what you're suggesting unless I store the
employee ID in some kind of temp table.

I'm using a VBA project to look at a table and determine the last
person that got mail, then send to the next one. After all of the
people have gotton mail, start over at the beginning. Can you explain
the special handling at the end of the recordset you're describing? Or
a reference to similiar code sample? That's where I seem to be having
the most trouble. I'm not sure if I should use DAO or ADO for my
connection to that table and if I should use Do until EOF or other
options.

Bob
Tom van Stiphout wrote:

On 5 Dec 2006 03:36:09 -0800, "Bob" <te*******@bellsouth.netwrote:

When I did something like this, I kept the value of the
EmployeeID_LastSent, so that next time I could go to the one after
that. That was done using rs.Move, with special handling for
end-of-recordset which meant going back to the first record.
In your case the recordset would only hold EmployeeIDs of salespeople
that are present in the office today.

-Tom.
I have an Access application where I need to automatically forward
emails to a group of 12 salespeople as soon as they arrive. The first
email needs to go to the first salesperson on the list, the second
email to the second salesperson on the list until I get to the end of
the list, then start over. I also need to be able to exclude
salespeople on their day off. The email portion of the application
will be using a rule in Outlook 2003 that runs a VBA project.

I was thinking of having a table with the salespersons name, email
address and some kind of flag value that I could have my VBA code look
for.. but thats where my lack of experience and knowledge becomes
apparent. I'm especially not sure of how to reset the process and
start over.

Does anyone have any suggestions or examples that may help?

Thank you in advance for any help you might havr!

Bob