Access开发：业务与成本数据关联记录集的取值及筛选问题

我一眼就看出问题所在了——你当前的代码有两个关键bug：

• 用DLookup的时候，因为查询条件只限定了Origin Port，它只会返回匹配的第一条记录，所以循环5次都是同一个Charge Type；
• 你打开rstCost时只选择了[Origin Port]字段，记录集里根本没有Currency、Charge Type这些字段，自然会报"item not found in collection"错误。

核心修复思路 #

1. 修改Cost记录集的SQL查询：把需要的所有字段（Origin Port、Currency、Charge Type、Unit Of Measure、Charge）都包含进来，这样就能直接从记录集读取每一行的成本数据。
2. 替换DLookup为记录集字段读取：循环rstCost的时候，直接用rstCost![Currency]这种方式取当前行的字段值，避免DLookup只取首行的问题。
3. 优化汇率查询：不用每次循环都打开汇率记录集，直接用DLookup一次性获取，提升代码效率。

修正后的完整代码 #

Sub CYCost1()
    Dim db As DAO.Database
    Dim rstCost As DAO.Recordset
    Dim rst As DAO.Recordset
    Dim rstOutput As DAO.Recordset
    Dim ContCount As Integer
    Dim TotalCost As Double
    Dim ConsolPOL As String, ContType As String, ShipMode As String
    Dim ConsolWeek As Integer
    Dim CostCurrency As String, CostType As String, CostUOM As String
    Dim CostCharge As Double, CostX As Double, USDCost As Double
    Dim xConversion As Double

    DoCmd.SetWarnings False
    Set db = CurrentDb
    ' 打开业务数据表
    Set rst = db.OpenRecordset("5 - Scenarios 2 - Optimisation - 2 Op Options")
    
    Do Until rst.EOF
        TotalCost = 0
        ' 读取当前业务记录的参数
        ConsolPOL = rst!POL
        ContType = rst![Container Type]
        ShipMode = rst![Shipment Mode]
        ConsolWeek = rst![Consol Week]
        ContCount = rst![Container Count]
        
        If ShipMode = "CFSCY" Then
            ' 关键修改：查询包含所有需要的成本字段，并且筛选指定Origin Port
            Set rstCost = db.OpenRecordset("SELECT [Origin Port], [Currency], [Charge Type], [Unit Of Measure], [Charge] " & _
                                          "FROM [2 - Rates 1 Origin - 1 Factory Loads - Tariff] " & _
                                          "WHERE [Origin Port] = '" & ConsolPOL & "';")
            
            Do Until rstCost.EOF
                ' 直接从rstCost记录集读取当前行的成本数据，替换DLookup
                CostCurrency = rstCost![Currency]
                CostType = rstCost![Charge Type]
                CostUOM = rstCost![Unit Of Measure]
                CostCharge = rstCost![Charge]
                
                ' 获取汇率，直接用DLookup，不用打开整个记录集
                xConversion = DLookup("[Conversion]", "2 - Rates 5 Exchange - Report", "[From] = '" & CostCurrency & "'")
                
                ' 计算成本
                CostX = ContCount * CostCharge
                USDCost = CostX * xConversion
                
                ' 写入输出表
                Set rstOutput = db.OpenRecordset("5 - Scenarios 2 - Optimisation - 3 Cost 1 Origin CY")
                rstOutput.AddNew
                rstOutput![Consol Week] = ConsolWeek
                rstOutput![POL] = ConsolPOL
                rstOutput![Container Type] = ContType
                rstOutput![Shipment Mode] = ShipMode
                rstOutput![Container Count] = ContCount
                rstOutput![Charge Type] = CostType
                rstOutput![Unit Of Measure] = CostUOM
                rstOutput![CostLocal] = CostX
                rstOutput![Cost USD] = USDCost
                rstOutput.Update
                rstOutput.Close ' 记得关闭输出记录集，避免资源泄漏
                
                rstCost.MoveNext
            Loop
            rstCost.Close ' 关闭成本记录集
        End If
        rst.MoveNext
    Loop
    
    ' 清理资源
    rst.Close
    Set rst = Nothing
    Set rstCost = Nothing
    Set rstOutput = Nothing
    Set db = Nothing
    DoCmd.SetWarnings True ' 最后恢复警告
End Sub

关键修改说明 #

• Cost记录集查询：现在SQL语句包含了所有需要的字段，并且保留了WHERE子句筛选指定的Origin Port，这样只会返回对应港口的5条成本记录；
• 移除DLookup：直接从rstCost读取当前行的字段值，循环的时候每一行都会拿到对应的Charge Type和金额，解决了重复问题；
• 资源清理：新增了rstOutput.Close和rstCost.Close，避免打开过多记录集导致的资源问题；最后恢复了DoCmd.SetWarnings True，避免后续操作看不到警告信息；
• 变量声明：把所有变量都提前声明，提升代码可读性和稳定性。

内容的提问来源于stack exchange，提问作者Willis24