遍历记录并在vba访问中增加值 [英] Loop through records and increment value in vba access

问题描述

我正在尝试遍历表中具有部件号和匹配部件号的记录，请在pos字段中增加一个值。我之前问过一个问题（对象变量或带块变量请勿将Access vba 设置为具有相同的项目，但要设置为该过程的不同步骤（运行查询）。我尚未在Access中找到解决此问题的问题，但我从该问题中获取了一些提示：用于遍历MS Access中所有记录的代码。现在，我正在尝试编写代码以遍历记录并增加pos字段中的值。

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(Object variable or With block variable not set Access vba) 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: Code to loop through all records in MS Access . Now, I'm trying to write the code to loop through the records and increment the value in the pos field.

将运行的查询：

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];

到目前为止的代码（向Kostas K致谢，以帮助我解决另一个问题）：

Code so far (credit to Kostas K for helping me on the other question):

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") & " " & 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

<我想循环通过的两个字段是CTOL表中的PART FIND NO和CTOL表中的EQP_POS_CD。如果最后一个PART FIND NO与当前编号相同，则EQP_POS_CD中的值应增加1。结果集也应包含在查询中选择的其他字段。还有什么我需要添加才能输出的吗？现在，无论是窗口还是数据表格式，我都不太担心它的输出方式（不过，如果有人知道的话，我会很高兴看到它的指针）。关于如何实现这一目标的任何指示？另外，如果我的问题中缺少任何重要的东西，或者在发布问题时我可以做得更好，我愿意讨论。我也愿意讨论如何解决该问题。谢谢！

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!

推荐答案

可能：

SELECT CTOL.*, [CTOL_Asbuilt].[PART-SN], [CTOL_Asbuilt].[PART-ATA-NO], [CTOL_Asbuilt].[PW-PART-NO],
DCount("*", "CTOL", "[Part Find No]=" & [Part Find No] & " AND ID<" & [ID])+1 AS Seq
FROM CTOL LEFT JOIN [CTOL_Asbuilt] ON CTOL.[PART FIND NO] = [CTOL_Asbuilt].[PART-ATA-NO];

这篇关于遍历记录并在vba访问中增加值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！