Loop to allocate a user to new records

Afternoon All,

I have a problem regarding a Loop.

I first made this loop (with some help off here as i am a newbie) to meet a specific requirement but as i was able to do that they have asked to make it much more complicated, along with alot of other stuff!  I now have alot more sypathy for developers who i have asked to change requirements in the middle of a project!  Now i am not sure if this is to big a ask to put on here but as i am rubbish at loops and still learning (alot!) VBA , but  i will give it a shot.

Basically the attached code allocates a user to a new record that has been entered automatically from another database.  (This database is the product of various feeds from a load of DB2 tables, its just one table containing updated records and new records).  As you can see it loops through tblNewTasks and depending on tblUsers.Percent it allocates records accordingly.

So for example there are 4 users, a,b,c and d on 20,20,20,40 percent allocation and they currently have 20,20,20,40 records each.  If user a closes all of their records on day 1 then he has no more records and the others still have their set amount Live cases.  Day 2 and 100 more records come in, user a gets 20 giving them 20 total, user b gets 20 giving them 40 overall, user c gets 20 giving them 40 overall and user d gets 40 giving them 80 overall.  Also on Day 2, 30 records come in that have previously been on the database, these are automatically given back to the user who originally dealt with them.  So user b recieves 10 more and user d recieves 20 more.  so a=20, b=50,c=40 d=100.  This is what currently happens and obviously its not that fair!  

What i want to do is take into account total cases the user currently has and then base the allocation of new cases on that.

So what i want to happen is this.

Day 1 users a,b,c and d have 20,20,20 and 40 records respectively, they have the same percentages as above.  User a closes 20 records.  
Day 2 100 new records come in and 30 previous records come in.  So the 30 previous are given out first, so b=30 and d=60, a=0 and c=20.  Now the allocation takes into account their current workloads so total live records = 210.
a = 210*20% = 42
b = 210*20% = 42
c = 210*20% = 42
d = 210*40% = 84

So what i want to happen is if the users current percentage allocation  is >= to the SUM of Current live records + New records then they do not recieve any more cases.  But if the the above is < then they recieve enough to take their allocation to the correct percentage.  

If in example 2 only 10 new records came in i would expect those 10 records to go to user a, the other users would only recieve ones previously assigned to them.  Previously assigned records are already in the database, what happens is their status is set to live as opposed to closed.  This is done automatically and happens before the allocation.

Oh and records can either be LIVE or CLOSED.  Only those options.

I think thats about it, I am pretty sure i have missed some info that could be helpful but just ask and i will reply!

Any help or guidence on this would be great!



Private Sub btnStart_Click()
    Dim db As Database
    Dim rsUsers As DAO.Recordset
    Dim rsTasks As DAO.Recordset
    Dim intLooper As Integer
    Dim intCurrentUser As Integer
    Dim intTasksEach As Integer
    Dim intTasks As Integer
    Dim intUsers As Integer
    Set db = CurrentDb
    Set rsUsers = db.OpenRecordset("tblUsers", dbOpenDynaset)
    Set rsTasks = db.OpenRecordset("tblNewTasks", dbOpenDynaset)
    intTasks = DCount("*", "tblNewTasks ")
    Do Until rsUsers.EOF
    intCurrentUser = rsUsers!ID
    '       Assign x tasks to that user
            intTasksEach = intTasks * rsUsers!Percent
            For intLooper = 1 To intTasksEach
                If rsTasks.EOF Then Exit For
                rsTasks!AssignUser = intCurrentUser
            Next intLooper
    '       move on to next user and repeat
    Set rstask = Nothing
    Set rsUsers = Nothing
    MsgBox ("The new records have been assigned!")
End Sub

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

A basic approach to this is to determine the number of live, existing tasks for each user within the User For/Each loop and subtract this number from intTasksEach.  If greater than 0, this number can be assigned in addition to the current load.  If less than 0, assign zero.  Then process your allocations for this user  and loop to the next.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeyThomas1Author Commented:

As that really did sort me out on the write track i am giving you the points! things are alot more obvious sometimes than i make them!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.