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