Recordset field not readable after update (1 Viewer)

amorosik

Active member
Local time
Today, 13:48
Joined
Apr 18, 2020
Messages
683
Why the row InserisciRecordPrincipale = rs("ID_FERICEVUTE") not recognize rs("ID_FERICEVUTE")?
When RS.UPDATE is invoked, rs("ID_FERICEVUTE") lost his value
Going step by step, you can see that the rs("id_fericevute") is correctly readable up to the rs.update line
Without Close, or Move, why the field is not readable?


Code:
Private Function InserisciRecordPrincipale(db As DAO.Database, NOME_FILE As String, hash As String, b64 As Boolean, nom_fil_ser_ftp As String) As Long
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("FERICEVUTE", dbOpenDynaset)
   
    rs.AddNew
    rs("ID_FERICEVUTE") = NUOVO_VALORE_DA_GENERATORE_GLOBALE()
    Call dati_testata_record(rs, "INSERIMENTO")
   
    rs("ID_DOCUMENTITESTATE") = 0
    rs("ID_MOVIMENTICONTABILITESTATE") = 0
    rs("NOME_FILE_COMPLETO") = NOME_FILE
    rs("NOME_FILE") = solo_nome_file(NOME_FILE)
    rs("HASH_MD5_DEL_FILE") = FileToMD5(NOME_FILE, b64)
    rs("HASH_SHA256_DEL_FILE") = hash
    rs("HASH_SHA512_DEL_FILE") = FileToSHA512(NOME_FILE, b64)
    rs("DATA_RICEZIONE") = data_ora_file(NOME_FILE, "CREAZIONE")
    rs("DATA_IMPORTAZIONE") = Format$(Now, "dd/mm/yyyy hh:mm:ss")
    rs("FLAG_SELEZIONA") = 0
    rs("FLAG_CARICATO") = 0
    rs("FLAG_CONTABILIZZATO") = 0
    rs("NOME_FILE_SU_FTP_SERVER") = nom_fil_ser_ftp
    rs("FLAG_VENDITA") = IIf(vendita_acquisto = "VENDITA", -1, 0)
    rs("NUMERO_FILE_ALLEGATI") = 0
    rs("ANNOTAZIONI") = ""
    rs.Update
   
    InserisciRecordPrincipale = rs("ID_FERICEVUTE")
    rs.Close
End Function
 
When you issue rs.update the recordset doesn't stay on that record.

Assuming your NUOVO_VALORE_DA_GENERATORE_GLOBALE() function retrieves the next ID simply store that in a variable at the beginning of the process.
 
By debugging step by step, up to the line rs("ANNOTATIONS")="" the value of rs("ID_FERICEVUTE") is valid
After running rs.update, the value of rs("ID_FERICEVUTE") is no longer valid
If it were something dependent on the previous routines, you would have seen it before row rs("ANNOTATIONS")=""
 
By debugging step by step, up to the line rs("ANNOTATIONS")="" the value of rs("ID_FERICEVUTE") is valid
After running rs.update, the value of rs("ID_FERICEVUTE") is no longer valid
If it were something dependent on the previous routines, you would have seen it before row rs("ANNOTATIONS")=""

I think you misunderstood me. I know it will be valid until you issue .Update
Store the value at the start of the insert:
Code:
Dim NewID as Long

  rs.AddNew

    NewID = NUOVO_VALORE_DA_GENERATORE_GLOBALE()    ''' This will store the new value

    rs("ID_FERICEVUTE") = NewID
    Call dati_testata_record(rs, "INSERIMENTO")
    ....
    rs.Update
  
    InserisciRecordPrincipale = NewID
 rs.Close
 
Yes, of course, if I move the

InsertPrincipalRecord = NewID

line before the rs.UPDATE, everything works fine
But I was curious to understand why the value of rs("ID_FERICEVUTE") is no longer available immediately after the update.
 
Yes, of course, if I move the

InsertPrincipalRecord = NewID

line before the rs.UPDATE, everything works fine
But I was curious to understand why the value of rs("ID_FERICEVUTE") is no longer available immediately after the update.

When you call .Update on a DAO Recordset, the current record’s buffer is written to the database and may be repositioned or refreshed — depending on the recordset type and underlying query.

This means that the record you were editing might no longer be current, or DAO needs to requery to see the updated data.

Example:
rs.Edit
rs!Field1 = "New Value"
rs.Update
rs.Bookmark = rs.LastModified ' Reposition to the updated record
Debug.Print rs!Field1
 
concur, you need to go to the Bookmark (Lastmodified record).

Code:
...
...
    rs.Update
    rs,BookMark = rs.LastModified 
    InserisciRecordPrincipale = rs("ID_FERICEVUTE")
 
When adding a row to recordset, grab the new ID after an edit, but before the update...
Code:
    Dim newRowID As Long
    With CurrentDb.OpenRecordset("SELECT RowID, SomeField FROM Table1")
        .AddNew
        !SomeField = "Updated"
        newRowID = !RowID
        .Update
    End With
 
When adding a row to recordset, grab the new ID after an edit, but before the update...
Code:
    Dim newRowID As Long
    With CurrentDb.OpenRecordset("SELECT RowID, SomeField FROM Table1")
        .AddNew
        !SomeField = "Updated"
        newRowID = !RowID
        .Update
    End With

Just as a warning: That only works with an Access back end. The ID isn't committed with a SQL Server BE until the update is carried out.
 

Users who are viewing this thread

Back
Top Bottom