vba - 在 Access VBA 中将第二个嵌套子表单复选框设置为 true
问题描述
我有一个包含工单的表格,PK=OrderID。具有 OrderDetails 的子表单,PK=OrderDetailsID。该子窗体上的一个子窗体具有 OrderDetailAccessories,PK=OrderAccID。
当我将主窗体上的复选框设置为 True 时,我希望子窗体 OrderDetails 的所有复选框都更改为 True 并将 CompDate 设置为今天的日期,并将其子窗体 OrderDetailsAccessories 的所有复选框更改为 True 并设置CompDate 到今天的日期。
在我的代码中,记录集 rs 返回预期的记录,但我得到 rs2 的空记录集。我单步执行了代码并且 orddetid 返回了正确的值。
Private Sub IsComplete_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strItemComp As String
Dim strAccComp As String
Dim ordid As Long
Dim orddetid As Long
ordid = Me.txtOrdID
strItemComp = "SELECT OrderDetailID, IsComplete, CompDate FROM tblOrderDetails WHERE OrderID = " & ordid
strAccComp = "SELECT OrderAccID, IsComplete, CompDate FROM tblOrderAcc WHERE OrderDetailID = " & orddetid
Set db = CurrentDb
Set rs = db.OpenRecordset(strItemComp)
If Me.IsComplete = True Then
If MsgBox("Marking main order complete will mark ALL items and accessories for this Order as complete!", vbYesNo, "Are you sure?") = vbYes Then
Me!txtCompletionDate = Date
rs.MoveFirst
Do Until rs.EOF
If rs!IsComplete = False Then
rs.Edit
rs!IsComplete = True
rs!CompDate = Date
rs.Update
End If
orddetid = rs.Fields("OrderDetailID").Value
Debug.Print orddetid
Set rs2 = db.OpenRecordset(strAccComp)
If rs2.RecordCount > 0 Then
rs2.MoveFirst
Do Until rs2.EOF
If rs2!IsComplete = False Then
rs2.Edit
rs2!IsComplete = True
rs2!CompDate = Date
rs2.Update
End If
rs2.MoveNext
Loop
End If
rs.MoveNext
Loop
Me.Dirty = False
Exit Sub
Else
Me.Undo
End If
Else
Me.txtCompletionDate = Null
Exit Sub
End If
Me.Dirty = False
End Sub
解决方案
您必须更新strAccComp
每条记录,例如:
If Me.IsComplete = True Then
If MsgBox("Marking main order complete will mark ALL items and accessories for this Order as complete!", vbYesNo, "Are you sure?") = vbYes Then
Me!txtCompletionDate = Date
Me.Dirty = False
Set rs = Me!NameOfSubformControl.Form.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
If rs!IsComplete = False Then
rs.Edit
rs!IsComplete = True
rs!CompDate = Date
rs.Update
End If
strAccComp = "SELECT OrderAccID, IsComplete, CompDate FROM tblOrderAcc WHERE OrderDetailID = " & rs!orddetid.Value
Set rs2 = CurrentDb.OpenRecordset(strAccComp)
If rs2.RecordCount > 0 Then
rs2.MoveFirst
Do Until rs2.EOF
If rs2!IsComplete = False Then
rs2.Edit
rs2!IsComplete = True
rs2!CompDate = Date
rs2.Update
End If
rs2.MoveNext
Loop
End If
rs2.Close
rs.MoveNext
Loop
rs.Close
End If
End If
推荐阅读
- android - 如何突出显示 RecyclerView 中的第一个项目并仅保持选中的 itemView 被选中?
- reactjs - 将 prop 值传递给所有嵌套的子组件
- javascript - 有什么方法可以简化这个 if/else 块?
- sockets - 在不同的线程中使用 cin 和 cout。为什么 cin 缓冲区会删除第一个字符?
- java - EditView 只显示文本文件的最后一行
- mysql - SQL 多个之间由 or 连接
- blockchain - C 二进制代码(编译后)到 EVM 字节码的转换
- java - 如何创建一个使用另一个类的变量的 java 对象?
- redirect - Google 上的操作:隐式 Oauth 流,重定向失败
- javascript - 网络应用可以访问 Oculus 接近传感器吗?