Assign Records in One Table Based on User List in Another 1

[firebirdta84]

Hi All,

Running Access 2007 and need a bit of assistance.

I have a table called "tbl_Master" where the first field is called "AssignedTo" and the other fields have data that is loaded from an outside source.

I also have a table named "tbl_Users" with consists of two fields - "UserName" and "LastDateAssigned". This table shows all the names of the people who need to be assigned to records in the other table and the last time each one was assigned a record.

Here's where I need help. At the press of a button, I need to assign each record in "tbl_Master" where "AssignedTo" is null. I need for Access to first order the "tbl_Users" by earliest "LastDateAssigned" and then start assigning the users in that order - one record for the first user, one for the second, etc...and when it reaches the bottom of the user list, it needs to loop back to the start and continue until ALL records in "tbl_Master" have something in the "AssignedTo" field.

Any help that can be provided is very much appreciated!

Joe

 

[MazeWorX]

Simple enough with a little vba

the query simply gets the records to be assigned using Is Null Or "" as criteria

The User Table sort order is set to [blue]tbl_Users.LastDateAssigned[/blue] this will sort ascending if you want descending then its [blue]tbl_Users.LastDateAssigned DESC[/blue]

Private Sub cmdAssignTasks_Click()

    Dim db As DAO.Database
    Dim rsUser As DAO.Recordset
    Dim rs As DAO.Recordset

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qryMaster")
    Set rsUser = db.OpenRecordset("tbl_Users")

    rsUser.MoveLast
    rsUser.MoveFirst
    rs.MoveLast
    rs.MoveFirst

    Do Until rs.EOF
        With rs
            .Edit
            .Fields("AssignedTo") = rsUser!UserName
            .Update
            With rsUser
                .Edit
                .Fields("LastDateAssigned") = Now()
                .Update
            End With
        End With
        If rsUser.EOF Then
            rsUser.MoveLast
            rsUser.MoveFirst
        Else
            rsUser.MoveNext
        End If
        rs.MoveNext
    Loop
End Sub

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>