• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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!

Thanks

Mike

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 ")
    
    rsUsers.MoveFirst
    rsTasks.MoveFirst
    
    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.Edit
                rsTasks!AssignUser = intCurrentUser
                rsTasks.Update
                rsTasks.MoveNext
            Next intLooper
 
    '       move on to next user and repeat
            rsUsers.MoveNext
            Loop
        
    rsTasks.Close
    rsUsers.Close
 
    Set rstask = Nothing
    Set rsUsers = Nothing
 
    MsgBox ("The new records have been assigned!")
 
End Sub

Open in new window

0
MikeyThomas1
Asked:
MikeyThomas1
1 Solution
 
OdeMonkeyCommented:
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.
0
 
MikeyThomas1Author Commented:
Howdy,

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!

Cheers
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now