Results 1 to 5 of 5
  1. #1
    domivax is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2014
    Posts
    7

    Update a field of a table from another table with in common Id

    Hi Everybody,

    I have:

    - a table [tbl_Client] with the fields [Id] - [Client Name] - [Color Id]
    - a query [sql_Color] with the fields [Id] - [Client Id] - [Color Id]

    I would like to update the [tbl_Client]![Color Id] picked up from [sql_Color]![Color Id] for each [tbl_Client]![Id] matching with [sql_Color]![Client Id]

    I think for that I need to do a Loop but I am not very confortable with Loops. I tried and of course this does not work.


    Private Sub UpDate_Click()
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim myCriteria As String
    Dim strSQL As String

    Set db = CurrentDb
    Set rs1 = CurrentDb.OpenRecordset("SELECT * " & "FROM tbl_Client")
    Set rs2 = CurrentDb.OpenRecordset("SELECT * " & "FROM sql_Color")

    rs1.MoveFirst

    Do While Not rs1.EOF()

    myCriteria = rs2![Color Id]

    If rs1![Id] = rs2![Client Id] Then
    strSQL = "UPDATE tbl_Client " & _
    "SET [Color Id] =""" & myCriteria & """ " & _
    "WHERE Id=" & rs1![Id]
    CurrentDb.Execute strSQL, dbFailOnError


    End If

    rs1.MoveNext

    Loop

    rs1.Close: Set rs1 = Nothing
    rs2.Close: Set rs2 = Nothing
    db.Close: Set db = Nothing
    End Sub


    but only the first record in [tbl_Client] is updated

    Your precious help are welcomed.
    Thank you very much.
    Domivax

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you don't use any code.
    Make a query, join the 2 tables on clientID,
    set the update color field.
    run the query.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If you like code, try something like this. It's a loop within a loop.

    Code:
    Private Sub UpDate_Click()
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim myCriteria As String
    
    
    Set db = CurrentDb
    Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM tbl_Client ORDER BY ID;")
    Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM sql_Color ORDER BY Client_ID;")
    rs1.movelast
    rs1.MoveFirst
    rs2.movelast
    Do While Not rs1.EOF
        rs2.movefirst    
        Do while not rs2.eof
             if rs1!ID = rs2![Client ID]
                 rs1.edit
                 rs1![Color ID] = rs2![Color ID]
                 rs1.update
                 Exit DO
             endif
             rs2.movenext 
         Loop 
        rs1.movenext
    Loop
    
    
    rs1.Close: Set rs1 = Nothing
    rs2.Close: Set rs2 = Nothing
    db.Close: Set db = Nothing
    End Sub

  4. #4
    domivax is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2014
    Posts
    7
    Thank you for the reply but I don't want to join table for circular queries reasons.

  5. #5
    domivax is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2014
    Posts
    7
    Hi Davegri,

    This works perfectly. Thank you so much. I did not know that we could include a loop inside another.

    Thank you so much again.
    Best Regards,
    Xavier

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-15-2016, 06:02 PM
  2. Replies: 1
    Last Post: 06-18-2016, 10:51 AM
  3. Replies: 5
    Last Post: 11-01-2012, 09:26 AM
  4. Replies: 3
    Last Post: 01-17-2011, 01:48 AM
  5. Replies: 4
    Last Post: 09-03-2009, 02:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums