How to display linked records before deleting them.
Access Archon #147 —
The VBA code for the command buttons is listed below:
VBA Code
frmOrderCleanup
Private Sub cmdDeleteOrder_Click()
On Error GoTo ErrorHandler
Dim strPrompt As String
Dim strTitle As String
Dim intReturn As Integer
Dim strQuery As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim strTable As String
Dim lngCount As Long
Dim frm As Access.Form
Dim lngID As Long
‘Attempt to get OrderID from selected record on subform
If Nz(Me![subOrders]![OrderID]) = 0 Then
GoTo ErrorHandlerExit
Else
lngID = Me![subOrders]![OrderID]
End If
‘Check whether order was shipped and not returned, and
‘confirm deletion in that case
If IsDate(Me![subOrders]![ShippedDate]) = True And _
Me![subOrders]![Returned] = False Then
strPrompt = “The order was shipped and not returned;” _
& ” do you still want to delete it?”
strTitle = “Confirm order deletion”
intReturn = MsgBox(strPrompt, vbYesNo + vbQuestion, strTitle)
If intReturn = vbNo Then
GoTo ErrorHandlerExit
Else
GoTo CheckLinkedRecords
End If
End If
CheckLinkedRecords:
‘Check whether there are any linked records before deleting record
strQuery = “qryTemp”
Set dbs = CurrentDb
strTable = “tblOrderDetails”
strSQL = “SELECT * FROM ” & strTable & ” WHERE ” _
& “[OrderID] = ” & lngID & “”
Debug.Print “SQL for ” & strQuery & “: ” & strSQL
lngCount = CreateAndTestQuery(strQuery, strSQL)
If lngCount = 0 Then
strPrompt = “No linked records found; delete this order?”
strTitle = “Confirmation”
intReturn = MsgBox(strPrompt, vbYesNo + vbQuestion, strTitle)
If intReturn = vbYes Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
ElseIf intReturn = vbNo Then
GoTo ErrorHandlerExit
End If
Else
DoCmd.OpenForm FormName:=”fdlgLinkedRecords”, _
wherecondition:=”[OrderID] = ” & lngID
Set frm = Forms![fdlgLinkedRecords]
frm.Requery
frm.Caption = “Linked records for Order ID ” & lngID
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
fdlgLinkedRecords
Private Sub cmdDelete_Click()
On Error GoTo ErrorHandler
Dim lngID As Long
Dim strSQL As String
Dim frm As Access.Form
Dim prj As Object
If Nz(Me![OrderID]) = 0 Then
GoTo ErrorHandlerExit
Else
lngID = Me![OrderID]
End If
DoCmd.SetWarnings False
strSQL = “DELETE tblOrderDetails.OrderID ” _
& “FROM tblOrderDetails ” _
& “WHERE OrderID = ” & lngID
Debug.Print “SQL string: ” & strSQL
DoCmd.RunSQL strSQL
strSQL = “DELETE tblOrders.OrderID ” _
& “FROM tblOrders ” _
& “WHERE OrderID = ” & lngID
Debug.Print “SQL string: ” & strSQL
DoCmd.RunSQL strSQL
Set prj = Application.CurrentProject
If prj.AllForms(“frmOrderCleanup”).IsLoaded Then
Forms![frmOrderCleanup].Requery
End If
DoCmd.Close acForm, Me.Name
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
References
The code in the sample database needs the following references (in addition to the default references):
Microsoft DAO 3.6 Object Library
Microsoft Scripting Runtime
If you import code or objects into a database of your own, you may need to set one or more of these references. The version number may differ, depending on your Office version; check the version you have. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch147.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Confirm Deletion (AA 147).mdb |
Access 2000 database (can also be used in higher versions of Access) |
Wherever you want |
