访问 VBA：如何将“无当前记录”移至“项目”=“项目”的下一条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19772756/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access VBA: How to move past 'No Current Record' to next Record where 'Item' = 'Item'
提问by user2921017
How do I move past 'No Current Record' to next Record where 'Item' = 'Item'
如何从“无当前记录”移到“项目”=“项目”的下一个记录
Loop comes back around and finds a Sales Order record in rsSO but not in rsInv or the Inventory recordset, creating the "Record not found error." The reason is that once the Inventory is depleted by allocating it to open Sales Orders I then delete the Inventory record for that specific item but there still may me open sales order left for that item. How do I move to the next item in the Open Sales Order rsSO recordset once the Inventory is depleted in rsInv?
循环返回并在 rsSO 中找到销售订单记录，但不在 rsInv 或库存记录集中，从而创建“未找到记录错误”。原因是，一旦通过将库存分配给未结销售订单来耗尽库存，我就会删除该特定项目的库存记录，但仍有可能为该项目留下未结销售订单。一旦 rsInv 中的库存耗尽，我如何移动到打开销售订单 rsSO 记录集中的下一个项目？
It is happening in the following section of code inside the first loop:
它发生在第一个循环内的以下代码段中：
Do Until rsInv!Item = rsSO!Item
If rsInv!Item = rsSO!Item Then
Exit Do
Else
rsInv.MoveNext
End If
Loop
Entire code:
完整代码：
Public Function UpdateInventoryIntl()
Dim rsInv As DAO.Recordset, rsSO As DAO.Recordset, db As DAO.Database
Dim qdf As DAO.QueryDef
Dim AllocationQty As Long, SaleOrderRemainder As Long
Set db = CurrentDb
Set rsInv = CurrentDb.OpenRecordset( _
"SELECT * FROM [tbl_InventoryAvailForIntl] ORDER BY [Item] DESC,[QOH_IntlAllocation] DESC", _
dbOpenDynaset)
Set rsSO = CurrentDb.OpenRecordset("SELECT * FROM [tbl_IntlAllocated] ORDER BY [Item] DESC,[Qty_Open] DESC", _
dbOpenDynaset)
Do Until rsSO.RecordCount = 0
Do Until rsInv!Item = rsSO!Item
If rsInv!Item = rsSO!Item Then
Exit Do
Else
rsInv.MoveNext
End If
Loop
AllocationQty = IIf(rsSO!Qty_Open > rsInv!QOH_IntlAllocation, rsInv!QOH_IntlAllocation, rsSO!Qty_Open)
db.Execute ("INSERT INTO tbl_IntlAllocatedResults (Due_Date, Sale_Order_Num, SO_Line, Item, Qty_OpenStart, Location, Lot, QtyAllocated) " & _
"VALUES (#" & rsSO!Due_Date & "#,'" & rsSO!Sale_Order_Num & "'," & rsSO!SO_Line & ",'" & rsSO!Item & "'," & rsSO!Qty_OpenStart & ",'" & rsInv!Location & "','" & rsInv!Lot & "'," & AllocationQty & ");")
rsSO.Edit
rsSO!Qty_Open = rsSO!Qty_Open - AllocationQty
rsSO.Update
If rsSO!Qty_Open = 0 Then
rsSO.Delete
rsSO.MoveNext
End If
rsInv.Edit
rsInv!QOH_IntlAllocation = rsInv!QOH_IntlAllocation - AllocationQty
rsInv.Update
Debug.Print rsInv!QOH_IntlAllocation
If rsInv!QOH_IntlAllocation = 0 Then
rsInv.Delete
rsInv.MoveNext
End If
Loop
rsSO.Close
Set rsSO = Nothing
Set qdf = Nothing
rsInv.Close
Set rsInv = Nothing
End Function
采纳答案by Simon1979
Rather than cycling through the recordset use FindFirst:
而不是循环通过记录集使用 FindFirst：
Dim sCriteria as String
sCriteria = "Item = " & rsSO!Item
rsInv.FindFirst (sCriteria)
If rsInv.NoMatch Then
' Do whatever you need to if there is no inventory
Else
' Carry on with your code
End If
You may get better efficiency picking up the recordset as you need it, depending on the size of recordset.
根据记录集的大小，在需要时拾取记录集可能会获得更高的效率。
Don't set your rsInv initially and then instead of your problematic loop use:
最初不要设置您的 rsInv，然后使用有问题的循环代替：
Set rsInv = Currentdb.OpenRecordset( _
"SELECT * FROM [tbl_InventoryAvailForIntl] _
WHERE [Item] = " & rsSO!Item & " ORDER BY [QOH_IntlAllocation] DESC", _
dbOpenDynaset)
You can then test if there are no records:
然后您可以测试是否没有记录：
If rsInv.EOF and rsInv.BOF Then
' No records, do what is required when no inventory
End If
