Updating a field in Table1 with the data from Table2 ... but wait, there's more!

[Sankofa]

I need to assign a user from the list of users in the User Table to an existing record in the "Assigned To" field of the Main table.

I know that I may have to implement a vba module to loop through the list of users to update the field in the main table - or - there may be a simpler way, but I'm an access newbie, and I have no idea how to go about this. I've been reading forums and textbooks since the start of business yesterday, and I haven't found a response that I think is on point.

If there is already a thread that explains how to do this I'd appreciate it if someone could point it out, because I couldn't fine it (and I really did look!)

Thanks in advance for your help,
Desperate

[Sankofa]

Oh, I forgot to tell you ... There will only be 4 users in the User table, and I need for any fields in the Main table to be assigned a User (equally) ... so if there are 12 records in Main where the Assigned To field is empty, Each of the 4 users in the User table will be assigned to 3 records in the Main table.

Sorry I didn't get this into the first post, I'm frustrated!

[Kirsti]

How are the Users in the User Table assigned to the Main Table? What is the relationship between the tables?

[Sankofa]

The Main table has a field called "UserID" which corresponds to the "ID" field in the User table

[Kirsti]

So can't you just create an IIf statement?

IIf([tblUser.ID]Is Null, [tblMain.UserID],[tblUser.ID])

Or am I missing something?

[Sankofa]

This database is serving to assign each user in the User table to a record in the Main table until all of the Main records have been assigned and there are no remaining null Assigned To fields. If there are 40 records in the Main table where the Assigned To field is null, and there are 4 users in the User table, each user would be assigned to 10 records in the Main table.

[Kirsti]

Ok, but that goes back to what I was asking earlier. How do you know which 10 records in the Main table each of the 4 users are assigned to? What criteria determines this?

[Sankofa]

Sorry ... this must seem like pulling teeth, and I apologize. The records will be assigned to the Main table only when the Assigned To field is null.

[Kirsti]

Ok, so why can't you put the IIf statement I posted above in the Assigned To field?

This doesn't seem very complicated to me, so I assume that means that I'm missing something. Please post more information.