MS Access VBA 中多个客户 ID 的 For Each 循环

For Each loop for multiple customer id's within MS Access VBA

提问by Anton Hughes

I have an existing table which hold 1000's of records. I need to update each record depending on the customer id and a date field associated with it.

我有一个现有的表，其中包含 1000 条记录。我需要根据客户 ID 和与之关联的日期字段更新每条记录。

Basically so i can put an ordered number beside each date depending on the order of the dates.

基本上，我可以根据日期的顺序在每个日期旁边放置一个有序编号。

I think I need to use two 'for each' loops to get this done. I.E.

我想我需要使用两个 'for each' 循环来完成这项工作。IE

For Each Customer ID in tblCustomers

'gather all records for that customer and get all dates in order from each record via recordset?

    For Each Date 
    newfield = newfield+ 1
    end loop

end loop

Could anyone point me in the right direction to figure this out?

谁能指出我正确的方向来解决这个问题？

Thanks

谢谢

回答by RichardC

Something like the following:

类似于以下内容：

Dim rstCustomers As DAO.Recordset
Set rstCustomers = CurrentDb.OpenRecordset("SELECT CustomerID FROM tblCustomers GROUP BY CustomerID")
If rstCustomers.RecordCount > 0 Then
    rstCustomers.MoveFirst
    Do Until rstCustomers.EOF
        Dim rstRecords As DAO.Recordset
        Set rstRecords = CurrentDb.OpenRecordset("SELECT RecordDate, OrderField FROM tblRecords WHERE CustomerID = " & rstCustomers!CustomerID & " ORDER BY RecordDate")
        If rstRecords.RecordCount > 0 Then
            Dim iCount as Integer
            iCount = 1
            rstRecords.MoveFirst
            Do Until rstRecords.EOF
                rstRecords.Edit
                   rstRecords!OrderField = iCount
                rstRecords.Update

                iCount = iCount + 1
                rstRecords.MoveNext
            Loop
        End If
        rstRecords.Close
        Set rstRecords = Nothing

        rstCustomers.MoveNext
    Loop
End If
rstCustomers.Close
Set rstCustomers = Nothing

回答by Andrey Gordeev

Loop records with using Recordset

使用Recordset循环记录