Update user field

[kaldag]

I have two tables. The User table has 10 user id's which are their three initals to their name. There is a second table called Contact Log with 3000 records with a blank user field. How can I assign a user from the User table to the Contact log table so each of the 3000 records has a user id? I assume I would use a query but have not gotton it to work.

Ken

 

[SgtJarrow]

Are you trying to set all the blank userfields in the Contact Log table to the same userid? If so, create an update query against the Contact Log table and just define the userid to go into the userfield:

UPDATE [Contact Log ]SET [user] = "rlj";

More importantly, it sounds as if you are trying to assign a particular contact log record to a user and haven't set up your relationship yet. If you enter the relationships window and add the two tables, make sure a line is draw from the user field in each of the tables. You may also want to right-click this relationship line and check your cascade effects, so that if you update a userid in the Users table, it will update all the records in the Contact Log table as well.

You will not be able to create this join in the relationship window until you have completed the update query above. This will assign ALL the Contact Log records to that one user, but you can then go in and update them as necessary. May be a pain, but unless you have some other criteria somewhere to determine who "owns" each record, this is your best bet. But the relationship setup will prevent this going forward. This may also require modifying the entry forms a bit, depending on how they are currently setup.

Good luck and let us know if we can help further


=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[kaldag]

I would like to take the 10 users from the User table and just add them to the user field in the Conctact log (all 3000). There is no rhyme or reason to how the users are assigned to the Contact log. Just need to populate the user field in the Contact log from the User table. Hope this makes sense.

Thanks

Ken

 

[SgtJarrow]

Okay....I couldn't find a simple way to do this through a query, but here is a sub that will do just that. Just make the appropriate name changes for tables and fields and run this and it will what I think you are looking for

Public Sub UpdateUsers()

    Dim cl As ADODB.Recordset
    Dim users As ADODB.Recordset
    Dim i As Integer
    
    Set cl = New ADODB.Recordset
    Set users = New ADODB.Recordset
    
    users.Open "Table2", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
    
    With cl
        .Open "Table1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
        .MoveFirst
        Do Until .EOF
            Randomize
            i = Int(users.RecordCount * Rnd)
            users.MoveFirst
            users.Move (i)
            .Fields("user") = users.Fields("user")
            .MoveNext
        Loop
        .Close
    End With
    
    users.Close

End Sub

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[kaldag]

Thank you. Will work with this.

Ken