Delete Records in Linked Tables
Introduction
If you want to delete a record in a form bound to a single table that is not linked to another table, you can just select the record and press the Delete key, or use two simple commands in VBA code. However, if the form’s source table is linked one-to-one or one-to-many to another table, this simple technique is not adequate, since you need to delete one or more matching records in the other table, or possibly several other tables. This article describes how to delete matching records for one-to-one and one-to-many relationships.
One-to-One Relationship
The form for the one-to-one relationship is the same one used in a previous Access Archon article (Copy a Record with CalcID); the form is bound to tblEmployeesCalcID, and it has a subform bound to tblConfidentialDataCalcID.
Figure A. The Employees form
These tables are in a one-to-one relationship. Ths form’s Delete Record button deletes the matching records in both tables:
VBA Code
Private Sub cmdDeleteRecord_Click()
On Error GoTo ErrorHandler
Dim intReturn As Integer
Dim lngID As Long
Dim lngCount As Long
Dim rst As DAO.Recordset
Dim strEmployeeName As String
Dim strPrompt As String
Dim strSearch As String
Dim strTitle As String
lngID = Nz(Me![EmployeeID])
strEmployeeName = Me![FirstName] & " " & Me![LastName]
Debug.Print "Employee name: " & strEmployeeName
strTitle = "Question"
strPrompt = "Delete " & strEmployeeName _
& " (Employee ID " & lngID & ")?"
intReturn = MsgBox(prompt:=strPrompt, _
Buttons:=vbQuestion + vbYesNo, _
Title:=strTitle)
If intReturn = vbYes Then
'Delete same record in two tables linked one-to-one
strSearch = "[EmployeeID] = " & lngID
Debug.Print "Search string: " & strSearch
Set rst = CurrentDb.OpenRecordset("tblConfidentalDataCalcID", _
dbOpenDynaset)
rst.FindFirst strSearch
If rst.NoMatch = False Then
rst.Delete
End If
Set rst = CurrentDb.OpenRecordset("tblEmployeesCalcID", _
dbOpenDynaset)
rst.FindFirst strSearch
If rst.NoMatch = False Then
rst.Delete
End If
strTitle = "Done"
strPrompt = strEmployeeName & "'s record deleted"
MsgBox prompt:=strPrompt, _
Buttons:=vbInformation + vbOKOnly, _
Title:=strTitle
Me.Requery
Else
GoTo ErrorHandlerExit
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
One-to-Many Relationship
tblParents and tblChildren are in a one-to-many relationship:
Figure B. A One-to-many relationship
The Parents form lists Parent data on its first tab:
Figure C. The Parent Data tab
And Children data on its second tab:
Figure D. The Children Data tab
The Parents form’s Delete Record code is listed below; it first deletes any Childen records there may be for the Parent, and then the Parent record itself:
VBA Code
Private Sub cmdDeleteRecord_Click()
On Error GoTo ErrorHandler
Dim intReturn As Integer
Dim lngID As Long
Dim lngCount As Long
Dim rst As DAO.Recordset
Dim strParentName As String
Dim strPrompt As String
Dim strSearch As String
Dim strSQL As String
Dim strTitle As String
lngID = Nz(Me![ParentID])
strParentName = Me![FirstName] & " " & Me![LastName]
Debug.Print "Parent name: " & strParentName
strTitle = "Question"
strPrompt = "Delete records of " & strParentName _
& " (Parent ID " & lngID & ") and their children?"
intReturn = MsgBox(prompt:=strPrompt, _
Buttons:=vbQuestion + vbYesNo, _
Title:=strTitle)
If intReturn = vbYes Then
'Delete Child records for this Parent record
strSQL = "DELETE *, ParentID " _
& "FROM tblChildren " _
& "WHERE ParentID = " & lngID & ";"
Debug.Print "SQL String: " & strSQL
CurrentDb.Execute strSQL
'Delete Parent record
Set rst = CurrentDb.OpenRecordset("tblParents", _
dbOpenDynaset)
strSearch = "[ParentID] = " & lngID
Debug.Print "Search string: " & strSearch
rst.FindFirst strSearch
If rst.NoMatch = False Then
rst.Delete
Me.Requery
End If
strTitle = "Done"
strPrompt = strParentName _
& "'s record and children records deleted"
MsgBox prompt:=strPrompt, _
Buttons:=vbInformation + vbOKOnly, _
Title:=strTitle
Else
GoTo ErrorHandlerExit
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
The technique used to delete records from tables in a one-to-many relationship can also be used to delete records in a many-to-many relationship, since such a relationship is just a set of one-to-many relationships, with a junction table in the middle:
Figure E. A many-to-many relationship
To delete records in a many-to-many relationship, first delete the records in the junction table, then the main table record.
References
The code in the sample database needs the following references (in addition to the default references):
Microsoft Word 14.0 Object Library (for SortDeclarations procedure only)
Microsoft Forms 2.0 Object Library (for SortDeclarations procedure only)
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 2007-2010 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site, as accarch251.zip, which is the last entry in the table of Access Archon columns for Access Watch.
| Document Name | Document Type | Place in |
| Deleting Records in Linked Tables.accdb | Access 2007-2010 database (can also be used in higher versions of Access) | Wherever you want |