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
' move on to next user and repeat
Set rstask = Nothing
Set rsUsers = Nothing
MsgBox ("The new records have been assigned!")