none
Update the field “customer_id” in a table based on an ID field from another table RRS feed

  • Question

  • Hi, I would like to update the field “customer_id” (not primary key) in a table named “tblInternetDépôtsCSV” based on an ID field from another table named “Clientèles”, here is the function I have for the moment:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Set dbs = CurrentDb
    
    Dim strSQL As String
    strSQL = "SELECT ID, ClasserSousAdministrateur FROM Clientèles"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    
    Dim strSQL1 As String
    strSQL1 = "SELECT customer_id, ClasserSousAdministrateur FROM tblInternetDépôtsCSV WHERE [ClasserSousAdministrateur] = rst.[ClasserSousAdministrateur]"
    Set rst2 = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)
    
    With rst2
        While Not .EOF
                .Edit
                ![customer_id] = rst![ID]
                .Update
            .MoveNext
        Wend
    End With
    End Function 

    So I did create a test query with the rst of the function:

    The table I want to update “tblInternetDépôtsCSV” (field: customer_id) so I did create a test query as well to see if the record will be created:

    I would like the customer_id field to get the value of 2 because the ID of the rst for that client is 2,

    When I execute the function:

    Hi, want to update the field “customer_id” (not primary key) in a table named “tblInternetDépôtsCSV” based on an ID field from another table, both field are numeric field

    Thank you in advance...

    Claude from Quebec, Canada


    Claude Larocque



    Sunday, August 7, 2016 5:55 AM

Answers

  • Hi, I would like to update the field “customer_id” (not primary key) in a table named “tblInternetDépôtsCSV” based on an ID field from another table named “Clientèles”

    Don't you merely need to execute a simple UPDATE statement?

    UPDATE tblInternetDépôtsCSV
    INNER JOIN Clientèles
    ON tblInternetDépôtsCSV.ClasserSousAdministrateur =
    Clientèles.ClasserSousAdministrateur
    SET tblInternetDépôtsCSV.customer_id = Clientèles.ID;

    I assume the reason for this is to enable the tables to be related on the keys rather than, as appears to currently be the case on ClasserSousAdministrateur, which will then become redundant in tblInternetDépôtsCSV?


    Ken Sheridan, Stafford, England

    Sunday, August 7, 2016 8:28 PM

All replies

  • strSQL1 = "SELECT customer_id, ClasserSousAdministrateur FROM tblInternetDépôtsCSV WHERE [ClasserSousAdministrateur] = rst.[ClasserSousAdministrateur]"
    

    Hi Claude,

    I think you have to change to above line to:

    strSQL1 = "SELECT customer_id, ClasserSousAdministrateur FROM tblInternetDépôtsCSV WHERE [ClasserSousAdministrateur] = " & rst.[ClasserSousAdministrateur]
    

    If you want to have the value of the variable rst.[ClasserSousAdministrateur], it must be outside the quotes, else the literal value of the variable is taken.

    Imb.

    Sunday, August 7, 2016 7:02 AM
  • Thanks IMB but now I have this error:

    I did remove from bracket because even with them I have the same error:

    Public Function MAJAdministrateurID()
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    
    
    Set dbs = CurrentDb
    
    Dim strSql As String
    strSql = "SELECT Clientèles.ID, Clientèles.ClasserSousAdministrateur FROM Clientèles RIGHT JOIN tblInternetDépôtsCSV ON Clientèles.ClasserSousAdministrateur = tblInternetDépôtsCSV.ClasserSousAdministrateur"
    Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
    
    Dim strSQL1 As String
    strSQL1 = "SELECT customer_id, ClasserSousAdministrateur FROM tblInternetDépôtsCSV WHERE tblInternetDépôtsCSV.ClasserSousAdministrateur = " & rst.ClasserSousAdministrateur
    Set rst2 = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)
    
    With rst2
        While Not .EOF
                .Edit
                ![Customer_ID] = rst![ID]
                .Update
            .MoveNext
        Wend
    End With
    
    End Function
    


    Thanks for your time


    Claude Larocque



    Sunday, August 7, 2016 4:49 PM
  • Imb, I have solve the precedent problem by entering rst! instead of rst. in the function, however, now I have this error:

    So I told to myself that if I remove the comma in the calculated fields it should be ok, so I just put a space in these fields and now I have this:

    Public Function MAJAdministrateurID()
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    DoCmd.SetWarnings False
    
    Set dbs = CurrentDb
    
    Dim strSql As String
    strSql = "SELECT Clientèles.ID, Clientèles.ClasserSousAdministrateur FROM Clientèles RIGHT JOIN tblInternetDépôtsCSV ON Clientèles.ClasserSousAdministrateur = tblInternetDépôtsCSV.ClasserSousAdministrateur"
    Set rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
    
    Dim strSQL1 As String
    strSQL1 = "SELECT customer_id, ClasserSousAdministrateur FROM tblInternetDépôtsCSV WHERE tblInternetDépôtsCSV.ClasserSousAdministrateur = " & rst!ClasserSousAdministrateur
    Set rst2 = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)
    
    With rst2
        While Not .EOF
                .Edit
                ![Customer_ID] = rst![ID]
                .Update
            .MoveNext
        Wend
    End With
    DoCmd.SetWarnings False
    End Function


    Claude Larocque


    Sunday, August 7, 2016 5:10 PM
  • So I told to myself that if I remove the comma in the calculated fields it should be ok, so I just put a space in these fields and now I have this:

    Hi Claude,

    If the control you want to update is a calculated field, you can not update it in the form itself, but you can update it in the source of the calculated field.

    Imb.

    Sunday, August 7, 2016 6:09 PM
  • Hi, I would like to update the field “customer_id” (not primary key) in a table named “tblInternetDépôtsCSV” based on an ID field from another table named “Clientèles”

    Don't you merely need to execute a simple UPDATE statement?

    UPDATE tblInternetDépôtsCSV
    INNER JOIN Clientèles
    ON tblInternetDépôtsCSV.ClasserSousAdministrateur =
    Clientèles.ClasserSousAdministrateur
    SET tblInternetDépôtsCSV.customer_id = Clientèles.ID;

    I assume the reason for this is to enable the tables to be related on the keys rather than, as appears to currently be the case on ClasserSousAdministrateur, which will then become redundant in tblInternetDépôtsCSV?


    Ken Sheridan, Stafford, England

    Sunday, August 7, 2016 8:28 PM
  • Hi Imb, the control I want to update is not a calculated field, only the ClasserSousAdministrateur is calculated...

    Thanks

    Claude


    Claude Larocque

    Monday, August 8, 2016 8:41 AM
  • Hi Ken and Imb...

    Ken, you were exactly right, it works at first try, the tblInternetDépôtsCSV is a temporary table that received the data first, then, with some inhouse code, it distribute the data in other tables and after the distribution, I empty that table, so it will not become redundant.

    But the goal is achieve,

    Thank you so much

    Claude

    Ibm, thanks for your help in this, as always, I learn a lot because I did use your code in another task...

    :)


    Claude Larocque

    Monday, August 8, 2016 8:49 AM