Loop to allocate a user to new records

Posted on 2008-11-12
Last Modified: 2013-11-27
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

Question by:MikeyThomas1
    LVL 4

    Accepted Solution

    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.

    Author Comment


    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!


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now