我在我的主表单上附带了以下'取消'按钮的点击事件上的VBA代码。目的是删除" entities_subform"中的所有记录,然后以主形式删除相关记录。
然而,尽管子图中的实体已成功删除,但由于运行时间错误3021(没有当前记录),因此未删除主表单记录。
我需要做些什么才能有效地重置对主要形式的重点以进行这项工作?在我添加代码以从实体子形式中删除记录之前,命令acCmdDeleteRecord
在主表单上工作正常。我已经尝试在下面的acCmdDeleteRecord
之前插入Me.SetFocus
行,但这没有任何区别。
Private Sub Cancel_New_Record_Click()
If MsgBox("Are you sure you want to delete this record?", vbYesNo) = vbYes Then
'first we need to delete all the entities in the subform, to prevent orphans being left behind
entities_subform.SetFocus
Dim entityRecSet As Recordset
Set entityRecSet = entities_subform.Form.Recordset.Clone()
entityRecSet.Delete
entityRecSet.Close
Set entityRecSet = Nothing
'now we can delete the check record
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acForm, "checks"
DoCmd.OpenForm "menu"
End If
End Sub
编辑:目前,我已经能够通过使用以下内容来实现我正在寻找的功能:
Private Sub Cancel_New_Record_Click()
'--------------------------------------------------
'deletes the newly created record from the database
'--------------------------------------------------
If MsgBox("Are you sure you want to delete this record?", vbYesNo) = vbYes Then
'grab the id of the current check record for later
Dim checkID As Integer
checkID = Me.Check_ID
'delete the current check record
DoCmd.RunCommand acCmdDeleteRecord
'now delete any orphan entities from the entity table
CurrentDb.Execute "DELETE * FROM entities WHERE entities.[Check ID] = " & checkID & ";"
'close the form and return to the menu
DoCmd.Close acForm, "checks"
DoCmd.OpenForm "menu"
End If
End Sub
首先,这只是创建您的记录的克隆:
Set entityRecSet = entities_subform.Form.Recordset.Clone()
然后,这只是删除克隆的第一个记录并关闭克隆:
entityRecSet.Delete
entityRecSet.Close
因此,您的记录完好无损。
您可以和应该做的是在两个表之间设置参考完整性。然后,删除主记录时,所有子记录将自动删除。