title: Loop through records and increment value in vba access
body: <p>I'm trying to loop through records in a table with part no's and with matching part no's, increment a value in the pos field. I asked a question earlier(<a href="https://stackoverflow.com/questions/44439264/object-variable-or-with-block-variable-not-set-access-vba">Object variable or With block variable not set Access vba</a>) with the same project but for a different step of the process (running a query). I haven't found a question that addresses this problem in Access yet, but I have taken some pointers from this question: <a href="https://stackoverflow.com/questions/5864160/code-to-loop-through-all-records-in-ms-access">Code to loop through all records in MS Access</a> . Now, I'm trying to write the code to loop through the records and increment the value in the pos field. </p>
  
  <p>The query that will run:</p>
  
  <pre><code>SELECT CTOL.ID, CTOL.BOM_PART_NAME, CTOL.CII, CTOL.[PART FIND NO], CTOL.CSN, CTOL.AFS, CTOL.EQP_POS_CD, CTOL.LCN, CTOL.POS_CT, CTOL.SERIAL_NO, CTOL.PART_NO_LLP, [CTOL_Asbuilt].[PART-SN], [CTOL_Asbuilt].[PART-ATA-NO], [CTOL_Asbuilt].[PW-PART-NO]
  FROM CTOL LEFT JOIN [CTOL_Asbuilt] ON CTOL.[PART FIND NO] = [CTOL_Asbuilt].[PART-ATA-NO];
  </code></pre>
  
  <p>Code so far (credit to Kostas K for helping me on the other question):</p>
  
  <pre><code>Option Compare Database
  Option Explicit
  
  'Const adOpenStatic = 3
  'Const adLockOptimistic = 3
  
  Function queryDatabase()
  
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim qdf As QueryDef
  'Dim rsQuery As DAO.Recordset
  Dim rows As Variant
  
  
  Dim part_find_no() As String
  Dim eqp_pos() As Integer
  'Dim strSQL As String
  
  Dim i As Integer
  Dim j As Integer
  'Set objConnection = CurrentDb.OpenRecordset("CTOL")
  
  Set db = CurrentDb
  
  Set qdf = db.QueryDefs("SicrProcess")
  
  Set rs = qdf.OpenRecordset(dbOpenDynaset)
  
  If rs.EOF Then GoTo Leave
  rs.MoveLast
  rs.MoveFirst
  
  
  For i = 1 To rs.RecordCount
      Debug.Print rs.Fields("PART FIND NO") &amp; " " &amp; rs.Fields("EQP_POS_CD")
      rs.MoveNext
  Next i
  
  Leave:
      On Error Resume Next
      rs.Close
      Set rs = Nothing
      qdf.Close
      Set qdf = Nothing
      Set db = Nothing
      On Error GoTo 0
      Exit Function
  
  ErrProc:
      MsgBox Err.Description, vbCritical
      Resume Leave
  End Function
  </code></pre>
  
  <p>The two fields I want to loop through are PART FIND NO in the CTOL table and EQP_POS_CD in the CTOL table. The value in EQP_POS_CD should be incremented by 1 if the last PART FIND NO is identical to the current no. The result set should contain the other fields selected in the query as well. Is there anything else I need to add to get it output? Right now, I'm not too concerned as to how it's output, whether in a window or in datasheet format (though I would appreciate pointers on this if anyone knows). Any pointers on how to accomplish this? Also, if there's anything I'm missing in my question that's important, or if there's something I could do better when posting a question, I'm open to discussing it. I'm also open to discussion of how to approach the problem. Thanks!</p>