生成记录集时出现"找不到方法或数据成员"错误

Question

我离开Access有一段时间了，在一个稍微简单的概念上遇到了一些麻烦。我想循环浏览子表单的记录，并在销售后调整库存数量。子表单的循环测试很好，但当我试图添加关于调整库存的部分时，我收到了一个Method或Data Member Not Found错误。对于S&G，我将SQL粘贴到一个空查询中，它返回的数据很好。我一定不是在搞砸什么，对吧？当它试图设置rec2=db时，会出现错误。打开记录集。这个错误特别出现在rec2.StockQuantity上，但正如我所说，当我只是将rec2代码粘贴到一个空查询中时，该字段就存在了。

这是完整的代码：

Option Compare Database
Option Explicit
Private Sub bnUpdate_Click()
Dim db As Database
Dim rec As Recordset
Dim rec2 As Recordset
Dim MsgBx1 As String
Dim X As String
Dim Y As String
Me.Refresh
Me.Parent.Refresh
Me.Parent!TotalSale.Value = Me.txtPriceSum.Value
MsgBx1 = MsgBox("Do you want to save the record?", vbYesNo, "Save Sale")
If MsgBx1 = vbYes Then
Set rec = Me.Form.Recordset
Do While rec.EOF = False
X = Me.UPC
Set rec2 = db.OpenRecordset("Select SKU, StockQuantity from tblProduct where SKU = '" & X & "'")
rec2.Edit
Y = rec2.StockQuantity
rec2.StockQuantity = Y - 1
rec2.Update
Set rec2 = Nothing
'MsgBox X
rec.MoveNext
Loop
Else
End If
End Sub

Answer 1

尝试进行大量修改：

Private Sub bnUpdate_Click()
Dim rec As DAO.Recordset
Dim rec2 As DAO.Recordset
Dim MsgBx1 As Long
Dim X As String
Dim Y As Long
Me.Refresh
Me.Parent.Refresh
Me.Parent!TotalSale.Value = Me!txtPriceSum.Value
MsgBx1 = MsgBox("Do you want to save the record?", vbQuestion + vbYesNo, "Save Sale")
If MsgBx1 = vbYes Then

Set rec = Me.RecordsetClone
rec.MoveFirst
Do While rec.EOF = False
X = rec!UPC.Value
Set rec2 = CurrentDb.OpenRecordset("Select SKU, StockQuantity from tblProduct where SKU = '" & X & "'")
Y = rec2!StockQuantity.Value
rec2.Edit
rec2!StockQuantity.Value = Y - 1
rec2.Update
rec2.Close
rec.MoveNext
Loop
rec.Close
End If
End Sub