CODEWITHSUNDEEP
ms-accessvba
evvdogg
evvdogg

Reputation: 37

Load arrays into recordset to loop through to increment value vba access

I'm trying to load values from two tables of an access database (in the same database file as the VBA code) into arrays to loop through and increment the value in EQP_POS_CD field when the PART FIND NO matches the previous PART FIND NO. The query:

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:

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_no() 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 = 0 To rs.RecordCount
    part_find_no() = rs("PART FIND NO")
    eqp_pos_no() = rs("EQP_POS_CD")
    If part_find_no(i) = part_find_no(i - 1) Then
        eqp_pos_no(i) = eqp_pos_no(i) + 1
    End If
    Debug.Print rs.Fields("PART FIND NO") & " " & 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
    db.Close
    On Error GoTo 0
    Exit Function

ErrProc:
    MsgBox Err.Description, vbCritical
    Resume Leave
End Function

I'm not sure what's wrong here. It's expecting arrays, but do I have to initialize those arrays somehow? How would I set those fields to an array from the results generated by the query? I want the arrays to be loaded with the results from that query but just those fields: PART FIND NO and EQP_POS_CD. Then it should loop through the rows in the two fields to increment EQP_POS_CD when the current PART FIND NO is the same as the previous PART FIND NO. Any advice on how to clean this up? Thanks.

Upvotes: 0

Views: 1001

Answers (1)

June7
June7

Reputation: 21370

I don't think array objects are needed. Consider:

Sub SetSeq()

Dim rs As DAO.Recordset, x As Integer, strPart As String, intSeq As Integer
Set rs = CurrentDb.OpenRecordset("SELECT [Part Find No], EQP_POS_CD FROM CTOL ORDER BY [Part Find No], SERIAL_NO;")

If rs.EOF Then GoTo Leave

rs.MoveLast
rs.MoveFirst
strPart = rs![Part Find No]
For x = 1 To rs.RecordCount
    If rs![Part Find No] <> strPart Then
        intSeq = 0
        strPart = rs![Part Find No]
    End If
    intSeq = intSeq + 1
    rs.Edit
    rs!EQP_POS_CD = intSeq
    rs.Update
    rs.MoveNext
Next x

Leave:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    On Error GoTo 0
    Exit Sub

ErrProc:
    MsgBox Err.Description, vbCritical
    Resume Leave
End Sub

The table after running code:

+------------+--------------+-----------+
| EQP_POS_CD | Part Find No | SERIAL_NO |
+------------+--------------+-----------+
|          1 | a            | abc1      |
|          2 | a            | abc2      |
|          3 | a            | abc3      |
|          1 | b            | abc4      |
|          2 | b            | abc5      |
|          1 | c            | abc6      |
|          2 | c            | abc7      |
|          3 | c            | abc8      |
+------------+--------------+-----------+

Upvotes: 1

Related Questions