By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,551 Members | 2,741 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

Assign task to next person in sequence

P: 1
I have a form for data entry, and when I create a new record I want the "assigned to" field to auto-populate by choosing the next record in a sequence from a list of people.

For example, every time a certain meeting is set up one representative from my group needs to attend, but it should not always be the same person. When I add a new record for the next meeting, I want the "assign to" field to look at my list of seven possible employees to decide whose turn it is to attend that meeting, and autopopulate the "assign to" field. Kind of like assigning sales leads equally, but I haven't been able to find an example of that either.
Sep 26 '12 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 5K+
P: 5,397
You really haven't given us enough detail about the database.

However, let's say you have the following:

table name: tbl_employees
[employee_pk] autonumber primarykey
[employee_fname] text(25)
[employee_lname] text(25)

table name: tbl_meetings
[meetings_pk] autonumber primary key
[meetings_date] date
[FK_employee_assigned] numeric long, 1:M with tbl_employees

So the basic logic:

In VBA, find the last record in tbl_meetings and fetch the value from [FK_employee_assigned]. You can do this a number of ways. Open a record set and move last is one such, you can use a query to return the max date or the max [meetings_pk] etc...

Take the found value for [FK_employee_assigned] and find the matching record in tbl_employees, determine if that is the last record in the table, if so then start at the top of the table else do a move next and pull the [employee_pk] value for entry into the new record in tbl_meetings [FK_employee_assigned]
Sep 26 '12 #2

Expert Mod 100+
P: 2,321
Well there are so many ways to approach this.

One way could be to simply store the last used representative ID in a table, and based on that choose the next in line (Could use a Dao recordset and the FindNext method for instance). You could tie it into the Before_Insert event, or load it as a default value when the form is loaded.

A more refined method would involved checking to see who is available at the desired time, and then choose the one with the least workload, but that is a way more refined solution.
Sep 26 '12 #3

Expert Mod 100+
P: 2,321
I see zmbd beat me to this one. He has a very good point though, no need to store the last used representative, since he is allready stored in whatever meeting table you have, and you should off course just pull it out of that table.
Sep 26 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.