Automatically distribute new records to different users? 1

[mikeythomas]

Afternoon

This is my first post here so i appologise if i am on the wrong thread.

What I trying to do is import a spreadsheet of new records and then give 20% to user1, then 20% to user2 and then 30% to user3 etc. Then append these records to the main table so that the users can then get to work.

I just need to find out how i can split the records and attach the usernames to them.

If you need more info then thats no prob.

Any ideas where i can start?

Your help will be greatly appreciated.

Cheers

Mike

 

[JBinQLD]

G'day fella,

a) Use docmd.transferspreadsheet to a table

b) Count records in table and number of users. Divide to work out number of records each - Assign to variable

c) Create a recordset of imported data, move first, start a loop to eof

d) set urrent record.assigned user to current user

e) increment counter and loop until intNumberOfRecordsEach reached

f) move to next user

Sorry i didn't have time to code, gotta go but hope it helps,

JB

 

[mikeythomas]

Howdy,

Thanks for your help, I have learnt a great deal!

I have a problem though, I have managed to alot of the above with the aid of a book however I am struggling with part d. I cant grasp how to to apply the user to the current record?

Would you be able to supply a little code when you get a spare minute.

I am pretty handy at access however this is my first venture in VBA but I think I will get there!

Thanks again.

Cheers

Mike

 

[JBinQLD]

OK, let's assume we have tblUsers and tblTasks and you've a variable intTasksEach which contains the number of tasks each user should get....

dim db as database
dim rsUsers as recordset
dim rsTasks as recordset
dim intLooper as integer
dim intCurrentUser as integer

set db=currentdb
set rsUsers=db.openrecordset("tblUsers",dbopendynaset)
set rsTasks=db.openrecordset("tblTasks",dbopendynaset)

rsUsers.movefirst
rsTasks.Movefirst

do until rsUsers.eof
   intCurrentUser=rsUsers!UserID

   'Assign x tasks to that user
   for intLooper=1 to intTasksEach
      rsTasks.edit
         rsTasks!AssignUser=intCurrentUser
      rstasks.update
      rstasks.movenext
   next intLooper

   'move on to next user and repeat...
   rsUsers.Movenext
loop   

'Almost there, but imagine we had 20 tasks and 3 users.
'That means tasks 1 - 6 are user 1, 7 - 12 are user 2
'13-18 are user 3.  What about tasks 19 and 20?
'well, lucky user 3 can have them....

'Just loop through remainder of rsTasks and dump in intcurrentUser

do until rsTasks.eof
   rstasks.edit
      rsTasks.AssignedUser=intCurrentUSer
   rstasks.update
   rstasks.movenext
loop

rstasks.close
rsusers.close

set rstask=nothing
set rsusers=nothing

msgbox("Tasks Assigned")

Above code is untested but it should work / give you a pointer. Good luck fella!

JB

 

[mikeythomas]

Morning

That works perectly! thanks alot!

I almost did that myself so i am getting there, but your code is much more stremlined! I have now added a few things to it and has helped me with a few other things i was trying to do.

Thanks again.

Cheers

Mike

 

[JBinQLD]

Too easy mate, no drama.

Keep rockin, good luck with rest of project

JB