none
Try to loop or wend to records and only the record selected is update RRS feed

  • Question

  • Hi Everyone

    I have a form named "frmProduits PP" which is based on a table name "Produits PP", according to the fields that has data in, I want the field "SEQUENCE" and "VERIFICATION" to be updated for all records. At the moment and with the code below, only the record that appears on the form is changed.

    Form:

    the next record is not updated see below

    so it should has been because the sequence is the same then the previous product (#53)

    Here is the code on the VERIFICATION button:

        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Set dbs = CurrentDb
        strSQL = "SELECT * FROM [Produits PP]"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
        Do While Not rst.EOF
            With rst
                If Not IsNull(Forms![frmProduits PP].[VENDOR (1)]) And Not IsNull(Forms![frmProduits PP].[STYLES (2)]) And Not IsNull(Forms![frmProduits PP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduits PP].[GAUGES (4)]) And Not IsNull(Forms![frmProduits PP].[SIZES-IN (5)]) And Not IsNull(Forms![frmProduits PP].[SIZES-MM (6)]) And Not IsNull(Forms![frmProduits PP].[TYPES (7)]) And Not IsNull(Forms![frmProduits PP].[COLORS (8)]) Then
                Me.SEQUENCE = "12345678"
                Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)] & "-" & [SIZES-MM (6)] & "-" & [TYPES (7)] & "-" & [COLORS (8)]
                End If
                If Not IsNull(Forms![frmProduits PP].[VENDOR (1)]) And Not IsNull(Forms![frmProduits PP].[STYLES (2)]) And Not IsNull(Forms![frmProduits PP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduits PP].[GAUGES (4)]) And Not IsNull(Forms![frmProduits PP].[SIZES-IN (5)]) And Not IsNull(Forms![frmProduits PP].[SIZES-MM (6)]) And Not IsNull(Forms![frmProduits PP].[TYPES (7)]) And IsNull(Forms![frmProduits PP].[COLORS (8)]) Then
                Me.SEQUENCE = "1234567"
                Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)] & "-" & [SIZES-MM (6)] & "-" & [TYPES (7)]
                End If
                If Not IsNull(Forms![frmProduits PP].[VENDOR (1)]) And Not IsNull(Forms![frmProduits PP].[STYLES (2)]) And Not IsNull(Forms![frmProduits PP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduits PP].[GAUGES (4)]) And Not IsNull(Forms![frmProduits PP].[SIZES-IN (5)]) And Not IsNull(Forms![frmProduits PP].[SIZES-MM (6)]) And IsNull(Forms![frmProduits PP].[TYPES (7)]) And IsNull(Forms![frmProduits PP].[COLORS (8)]) Then
                Me.SEQUENCE = "123456"
                Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)] & "-" & [SIZES-MM (6)]
                End If
                If Not IsNull(Forms![frmProduits PP].[VENDOR (1)]) And Not IsNull(Forms![frmProduits PP].[STYLES (2)]) And Not IsNull(Forms![frmProduits PP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduits PP].[GAUGES (4)]) And Not IsNull(Forms![frmProduits PP].[SIZES-IN (5)]) And IsNull(Forms![frmProduits PP].[SIZES-MM (6)]) And IsNull(Forms![frmProduits PP].[TYPES (7)]) And IsNull(Forms![frmProduits PP].[COLORS (8)]) Then
                Forms![frmProduits PP]![SEQUENCE] = "12345"
                Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)]
                End If
                If Not IsNull(Forms![frmProduits PP].[VENDOR (1)]) And Not IsNull(Forms![frmProduits PP].[STYLES (2)]) And Not IsNull(Forms![frmProduits PP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduits PP].[GAUGES (4)]) And IsNull(Forms![frmProduits PP].[SIZES-IN (5)]) And IsNull(Forms![frmProduits PP].[SIZES-MM (6)]) And IsNull(Forms![frmProduits PP].[TYPES (7)]) And IsNull(Forms![frmProduits PP].[COLORS (8)]) Then
                Me.SEQUENCE = "1234"
                Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)]
                End If
            End With
            rst.MoveNext
        Loop

    Thank you all for your help

    Claude from Quebec, Canada

    Tuesday, February 12, 2019 5:12 PM

Answers

  • I did try an rst.edit and rst.update, but as soon as a criteria is met, then, all the records are updated with the same data :(

    Hi Claude,

    Did you update the fields of the recordset using the data in the other fields of the recordset, and not the data fro the form in the (static) displayed record?

    Further you could simplify your code quite a lot, like:

       With rst
        Do Until .EOF
    
          If (Not IsNull(rst![Vendor (1)])) Then
            tmp_sequence = "1"
            tmp_verification = rst![Vendor (1)]
          End If
          If (Not IsNull(rst![Styles (2)])) Then
            tmp_sequence = tmp_sequence & "2"
            tmp_verification = tmp_verification & "-" & rst![Styles (2)]
          End If
          If (Not IsNull(rst![Materials (3)])) Then
            tmp_sequence = tmp_sequence & "3"
            tmp_verification = tmp_verification & "-" & rst![Materials (3)]
          End If
    
          ......
    
          rst.Edit
          rst!Sequence = tmp_sequence
          rst!Verification = tmp_verification
          rst.Update
    
          rst.MoveNext
         Loop
    
    End With
    

    Imb.

    Wednesday, February 13, 2019 9:25 AM
  • Hi Lawrence,

    thanks for your time, it is still works with only the current record, but it's OK because I use it when someone create a new PP Product.

    Thanks a lot

    Wednesday, February 13, 2019 11:34 AM

All replies

  • Hi Claude,

    You loop through the RecordSet rst that is completely independant of the form (though the same), and you are updating the current record in the form.

    Instead, you can better update the fields in rst, and these updates will then be displayed in the form.

    Imb.


    • Edited by Imb-hb Tuesday, February 12, 2019 5:42 PM
    • Proposed as answer by Peter N Roth Tuesday, February 12, 2019 8:29 PM
    Tuesday, February 12, 2019 5:41 PM
  • Try the code below.

    I reversed the With and the Do loop and it will loop until the End Of File. When it gets to the last record however, an error message might appear stating there is "no currrent record" so I introduced an Error handler. I don't know what your Public Function name is so lets say the Function name is 'UpdateRecords'. So: 

    Public Function UpdateRecords()
    On Error GoTo UpdateRecords_Error

        Dim dbs As DAO.Database
       
    Dim rst As DAO.Recordset
       
    Dim strSQL As String
       
    Set dbs = CurrentDb
        strSQL
    = "SELECT * FROM [Produits PP]"
       
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

       With rst
        Do Until .EOF

    If Not IsNull(Forms![frmProduits PP].[VENDOR (1)]) And Not IsNull(Forms![frmProduits PP].[STYLES (2)]) And Not IsNull(Forms!  [frmProduits PP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduits PP].[GAUGES (4)]) And Not IsNull(Forms![frmProduits PP].[SIZES-IN (5)]) And Not IsNull(Forms![frmProduits PP].[SIZES-MM (6)]) And Not IsNull(Forms![frmProduits PP].[TYPES (7)]) And Not IsNull(Forms![frmProduits PP].[COLORS (8)]) Then
               
    Me.SEQUENCE = "12345678"
               
    Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)] & "-" & [SIZES-MM (6)] & "-" & [TYPES (7)] & "-" & [COLORS (8)]
               
    End If
               
    If Not IsNull(Forms![frmProduits PP].[VENDOR (1)]) And Not IsNull(Forms![frmProduits PP].[STYLES (2)]) And Not IsNull(Forms![frmProduits PP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduits PP].[GAUGES (4)]) And Not IsNull(Forms![frmProduits PP].[SIZES-IN (5)]) And Not IsNull(Forms![frmProduits PP].[SIZES-MM (6)]) And Not IsNull(Forms![frmProduits PP].[TYPES (7)]) And IsNull(Forms![frmProduits PP].[COLORS (8)]) Then
               
    Me.SEQUENCE = "1234567"
               
    Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)] & "-" & [SIZES-MM (6)] & "-" & [TYPES (7)]
               
    End If
               
    If Not IsNull(Forms![frmProduits PP].[VENDOR (1)]) And Not IsNull(Forms![frmProduits PP].[STYLES (2)]) And Not IsNull(Forms![frmProduits PP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduits PP].[GAUGES (4)]) And Not IsNull(Forms![frmProduits PP].[SIZES-IN (5)]) And Not IsNull(Forms![frmProduits PP].[SIZES-MM (6)]) And IsNull(Forms![frmProduits PP].[TYPES (7)]) And IsNull(Forms![frmProduits PP].[COLORS (8)]) Then
               
    Me.SEQUENCE = "123456"
               
    Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)] & "-" & [SIZES-MM (6)]
               
    End If
               
    If Not IsNull(Forms![frmProduits PP].[VENDOR (1)]) And Not IsNull(Forms![frmProduits PP].[STYLES (2)]) And Not IsNull(Forms![frmProduits PP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduits PP].[GAUGES (4)]) And Not IsNull(Forms![frmProduits PP].[SIZES-IN (5)]) And IsNull(Forms![frmProduits PP].[SIZES-MM (6)]) And IsNull(Forms![frmProduits PP].[TYPES (7)]) And IsNull(Forms![frmProduits PP].[COLORS (8)]) Then
               
    Forms![frmProduits PP]![SEQUENCE] = "12345"
               
    Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)]
               
    End If
               
    If Not IsNull(Forms![frmProduits PP].[VENDOR (1)]) And Not IsNull(Forms![frmProduits PP].[STYLES (2)]) And Not IsNull(Forms![frmProduits PP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduits PP].[GAUGES (4)]) And IsNull(Forms![frmProduits PP].[SIZES-IN (5)]) And IsNull(Forms![frmProduits PP].[SIZES-MM (6)]) And IsNull(Forms![frmProduits PP].[TYPES (7)]) And IsNull(Forms![frmProduits PP].[COLORS (8)]) Then
               
    Me.SEQUENCE = "1234"
               
    Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)]
               
    End If
            rst
    .MoveNext
       
    Loop

    End With

    UpdateRecords_Error:

    DoCmd.CancelEvent

    Resume Next

    Exit Function

    End Function


    Tuesday, February 12, 2019 8:24 PM
  • Thanks Lawrence for your reply, however, it does only change the current record, here is the code, a small mistake was in yours (an unwanted space) :)

    For testing this is what I do, I remove the text in the sequence field for ID 2 and for ID 4, there are sequence 12345 and the verification is remove as well.

    If I am on another record then the 2 and 4 and click on my verification button, nothing happens, however, if I am on the record # 2 and click the button, then both field are updated same thing happens when I am on record # 4 and click on the button.

    NOTE: I but a me.requery at the end of the function, because nothing change without that, unless I am closing and reopening the form

    thanks again

    Private Sub cmdVérification_Click()
    UpdatePPRecords
    End Sub
    Public Function UpdatePPRecords()
     On Error GoTo UpdatePPRecords_Error
    
        Dim dbs As DAO.Database
         Dim rst As DAO.Recordset
         Dim strSQL As String
         Set dbs = CurrentDb
         strSQL = "SELECT * FROM [ProduitsPP]"
         Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    
    
       With rst
        Do Until .EOF
    
                 If Not IsNull(Forms![frmProduitsPP].[VENDOR (1)]) And Not IsNull(Forms![frmProduitsPP].[STYLES (2)]) And Not IsNull(Forms![frmProduitsPP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduitsPP].[GAUGES (4)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-IN (5)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-MM (6)]) And Not IsNull(Forms![frmProduitsPP].[TYPES (7)]) And Not IsNull(Forms![frmProduitsPP].[COLORS (8)]) Then
                 Me.SEQUENCE = "12345678"
                 Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)] & "-" & [SIZES-MM (6)] & "-" & [TYPES (7)] & "-" & [COLORS (8)]
                 End If
                 If Not IsNull(Forms![frmProduitsPP].[VENDOR (1)]) And Not IsNull(Forms![frmProduitsPP].[STYLES (2)]) And Not IsNull(Forms![frmProduitsPP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduitsPP].[GAUGES (4)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-IN (5)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-MM (6)]) And Not IsNull(Forms![frmProduitsPP].[TYPES (7)]) And IsNull(Forms![frmProduitsPP].[COLORS (8)]) Then
                 Me.SEQUENCE = "1234567"
                 Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)] & "-" & [SIZES-MM (6)] & "-" & [TYPES (7)]
                 End If
                 If Not IsNull(Forms![frmProduitsPP].[VENDOR (1)]) And Not IsNull(Forms![frmProduitsPP].[STYLES (2)]) And Not IsNull(Forms![frmProduitsPP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduitsPP].[GAUGES (4)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-IN (5)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-MM (6)]) And IsNull(Forms![frmProduitsPP].[TYPES (7)]) And IsNull(Forms![frmProduitsPP].[COLORS (8)]) Then
                 Me.SEQUENCE = "123456"
                 Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)] & "-" & [SIZES-MM (6)]
                 End If
                 If Not IsNull(Forms![frmProduitsPP].[VENDOR (1)]) And Not IsNull(Forms![frmProduitsPP].[STYLES (2)]) And Not IsNull(Forms![frmProduitsPP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduitsPP].[GAUGES (4)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-IN (5)]) And IsNull(Forms![frmProduitsPP].[SIZES-MM (6)]) And IsNull(Forms![frmProduitsPP].[TYPES (7)]) And IsNull(Forms![frmProduitsPP].[COLORS (8)]) Then
                 Forms![frmProduitsPP]![SEQUENCE] = "12345"
                 Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)]
                 End If
                 If Not IsNull(Forms![frmProduitsPP].[VENDOR (1)]) And Not IsNull(Forms![frmProduitsPP].[STYLES (2)]) And Not IsNull(Forms![frmProduitsPP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduitsPP].[GAUGES (4)]) And IsNull(Forms![frmProduitsPP].[SIZES-IN (5)]) And IsNull(Forms![frmProduitsPP].[SIZES-MM (6)]) And IsNull(Forms![frmProduitsPP].[TYPES (7)]) And IsNull(Forms![frmProduitsPP].[COLORS (8)]) Then
                 Me.SEQUENCE = "1234"
                 Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)]
                 End If
             rst.MoveNext
         Loop
    
    End With
    Me.Requery
    UpdatePPRecords_Error:
    
    DoCmd.CancelEvent
    
    Resume Next
    
    Exit Function
    
    End Function


    Tuesday, February 12, 2019 9:29 PM
  • Thanks IMB, long time without asking a question, I did try an rst.edit and rst.update, but as soon as a criteria is met, then, all the records are updated with the same data :(

    Tuesday, February 12, 2019 9:32 PM
  • You will need an .Edit command before doing any updating and .Update command after all the If statements so any record that meets your criteria is updated. I also changed the strSQL and added a .MoveLast and .MoveFirst command. It's just a good idea when opening a recordset. So if this doesn't loop through your recordset and update each record that meets the criteria, then there is something wrong in your If statements. That is something you will have to test yourself.

    Public Function UpdatePPRecords()
    On Error GoTo UpdatePPRecords_Error
        
    Dim dbs As DAO.Database
        
    Dim rst As DAO.Recordset
        
    Dim strSQL As String
        
    Set dbs = CurrentDb
         strSQL
    = "SELECT [Produits PP].* FROM [Produits PP]"
        
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
      
    With rst
       
    Do Until .EOF
        .Edit

        .MoveLast

        .MoveFirst
    If Not IsNull(Forms![frmProduitsPP].[VENDOR (1)]) And Not IsNull(Forms![frmProduitsPP].[STYLES (2)]) And Not IsNull(Forms![frmProduitsPP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduitsPP].[GAUGES (4)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-IN (5)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-MM (6)]) And Not IsNull(Forms![frmProduitsPP].[TYPES (7)]) And Not IsNull(Forms![frmProduitsPP].[COLORS (8)]) Then
                
    Me.SEQUENCE = "12345678"
                
    Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)] & "-" & [SIZES-MM (6)] & "-" & [TYPES (7)] & "-" & [COLORS (8)]
                
    End If
    If Not IsNull(Forms![frmProduitsPP].[VENDOR (1)]) And Not IsNull(Forms![frmProduitsPP].[STYLES (2)]) And Not IsNull(Forms![frmProduitsPP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduitsPP].[GAUGES (4)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-IN (5)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-MM (6)]) And Not IsNull(Forms![frmProduitsPP].[TYPES (7)]) And IsNull(Forms![frmProduitsPP].[COLORS (8)]) Then
                
    Me.SEQUENCE = "1234567"
                
    Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)] & "-" & [SIZES-MM (6)] & "-" & [TYPES (7)]
                
    End If
    If Not IsNull(Forms![frmProduitsPP].[VENDOR (1)]) And Not IsNull(Forms![frmProduitsPP].[STYLES (2)]) And Not IsNull(Forms![frmProduitsPP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduitsPP].[GAUGES (4)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-IN (5)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-MM (6)]) And IsNull(Forms![frmProduitsPP].[TYPES (7)]) And IsNull(Forms![frmProduitsPP].[COLORS (8)]) Then
                
    Me.SEQUENCE = "123456"
                
    Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)] & "-" & [SIZES-MM (6)]
                
    End If
    If Not IsNull(Forms![frmProduitsPP].[VENDOR (1)]) And Not IsNull(Forms![frmProduitsPP].[STYLES (2)]) And Not IsNull(Forms![frmProduitsPP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduitsPP].[GAUGES (4)]) And Not IsNull(Forms![frmProduitsPP].[SIZES-IN (5)]) And IsNull(Forms![frmProduitsPP].[SIZES-MM (6)]) And IsNull(Forms![frmProduitsPP].[TYPES (7)]) And IsNull(Forms![frmProduitsPP].[COLORS (8)]) Then
                
    Forms![frmProduitsPP]![SEQUENCE] = "12345"
                
    Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)] & "-" & [SIZES-IN (5)]
                
    End If
    If Not IsNull(Forms![frmProduitsPP].[VENDOR (1)]) And Not IsNull(Forms![frmProduitsPP].[STYLES (2)]) And Not IsNull(Forms![frmProduitsPP].[MATERIALS (3)]) And Not IsNull(Forms![frmProduitsPP].[GAUGES (4)]) And IsNull(Forms![frmProduitsPP].[SIZES-IN (5)]) And IsNull(Forms![frmProduitsPP].[SIZES-MM (6)]) And IsNull(Forms![frmProduitsPP].[TYPES (7)]) And IsNull(Forms![frmProduitsPP].[COLORS (8)]) Then
                
    Me.SEQUENCE = "1234"
                
    Me.VÉRIFICATION = [VENDOR (1)] & "-" & [STYLES (2)] & "-" & [MATERIALS (3)] & "-" & [GAUGES (4)]
                
    End If

                 .Update         rst.MoveNext
        
    Loop
    End With
    UpdatePPRecords_Error:
    DoCmd.CancelEvent
    Resume Next
    Exit Function
    End Function


    Tuesday, February 12, 2019 11:57 PM
  • I did try an rst.edit and rst.update, but as soon as a criteria is met, then, all the records are updated with the same data :(

    Hi Claude,

    Did you update the fields of the recordset using the data in the other fields of the recordset, and not the data fro the form in the (static) displayed record?

    Further you could simplify your code quite a lot, like:

       With rst
        Do Until .EOF
    
          If (Not IsNull(rst![Vendor (1)])) Then
            tmp_sequence = "1"
            tmp_verification = rst![Vendor (1)]
          End If
          If (Not IsNull(rst![Styles (2)])) Then
            tmp_sequence = tmp_sequence & "2"
            tmp_verification = tmp_verification & "-" & rst![Styles (2)]
          End If
          If (Not IsNull(rst![Materials (3)])) Then
            tmp_sequence = tmp_sequence & "3"
            tmp_verification = tmp_verification & "-" & rst![Materials (3)]
          End If
    
          ......
    
          rst.Edit
          rst!Sequence = tmp_sequence
          rst!Verification = tmp_verification
          rst.Update
    
          rst.MoveNext
         Loop
    
    End With
    

    Imb.

    Wednesday, February 13, 2019 9:25 AM
  • Thanks IMB that did the job when I want to update all fields, it is a lot more useful.

    Claude

    Wednesday, February 13, 2019 11:33 AM
  • Hi Lawrence,

    thanks for your time, it is still works with only the current record, but it's OK because I use it when someone create a new PP Product.

    Thanks a lot

    Wednesday, February 13, 2019 11:34 AM