Assign task to next person in sequence

[indykimo]

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.

[zmbd]

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]

[TheSmileyCoder]

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.

[TheSmileyCoder]

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.