Nested loop to update recordset not working Access-CodePudding

I am planning to search and update records which matches my criteria in my table through a form. I want my code to search for OrderNo and OrderNoItem ( For each orderno I have multiple OrderNoItems like 10,20,30... in my table) when there is a match I want to update the customer name(Text18.Value) from my Form.

I have the following code. For some reason it is just updating only the first record. For example when I input Text25.Value = 12345, Text27.Value = 20 and Text49.Value = 40, it is updating the customer name only for the rows with 12345 and 20. Can any one help??

Set logDB1 = CurrentDb()
Set logRS1 = logDB1.OpenRecordset("Log")   

For i = Text27.Value To Text49.Value Step 10
    Do Until logRS1.EOF
         If (logRS1![OrderNo] = Text25.Value And logRS1![OrderNoItem] = Text27.Value) Then
    
            logRS1.Edit
            logRS1![DTN-#] = Text18.Value
            logRS1.Update
          
         End If
    logRS1.MoveNext
    Loop
        
    Next

CodePudding user response：

Because code is set to do that. That's exactly what the If condition requires. What you should do is open the recordset filtered by OrderNo and the OrderNoItem range then just loop those records.

Set logDB1 = CurrentDb()
Set logRS1 = logDB1.OpenRecordset("SELECT [DTN-#] FROM Log WHERE OrderNo='" & _
                Me.Text25 & "' AND Val(OrderNoItem) BETWEEN " & Me.Text27 & " AND " & Me.Text49)   

    Do Until logRS1.EOF
        logRS1.Edit
        logRS1![DTN-#] = Me.Text18
        logRS1.Update
        logRS1.MoveNext
    Loop

Or instead of opening and looping recordset, Execute an UPDATE action SQL:

CurrentDb.Execute "UPDATE Log SET [DTN-#]='" & Me.Text18 & _
     "' WHERE OrderNo = '" & Me.Text25 & "' AND Val(OrderNoItem) BETWEEN " & Me.Text27 & " AND " & Me.Text49

If OrderNo is a number field, remove the apostrophe delimiters.

Strongly advise not to use punctuation/special characters (underscore is only exception) in naming convention. Better would be DTN_Num.