Relation Object, Relations Collection Example

This example shows how an existing Relation object can control data entry. The procedure attempts to add a record with a deliberately incorrect CategoryID; this triggers the error-handling routine.

Sub RelationX()

   Dim dbsNorthwind As Database
   Dim rstProducts As Recordset
   Dim prpLoop As Property
   Dim fldLoop As Field
   Dim errLoop As Error

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set rstProducts = dbsNorthwind.OpenRecordset("Products")

   ' Print a report showing all the different parts of
   ' the relation and where each part is stored.
   With dbsNorthwind.Relations!CategoriesProducts
      Debug.Print "Properties of " & .Name & " Relation"
      Debug.Print "  Table = " & .Table
      Debug.Print "  ForeignTable = " & .ForeignTable
      Debug.Print "Fields of " & .Name & " Relation"
      With .Fields!CategoryID
         Debug.Print "  " & .Name
         Debug.Print "    Name = " & .Name
         Debug.Print "    ForeignName = " & .ForeignName
      End With
   End With

   ' Attempt to add a record that violates the relation.
   With rstProducts
      .AddNew
      !ProductName = "Trygve's Lutefisk"
      !CategoryID = 10
      On Error GoTo Err_Relation
      .Update
      On Error GoTo 0
      .Close
   End With

   dbsNorthwind.Close
   
   Exit Sub
   
Err_Relation:

   ' Notify user of any errors that result from
   ' the invalid data.
   If DBEngine.Errors.Count > 0 Then
      For Each errLoop In DBEngine.Errors
         MsgBox "Error number: " & errLoop.Number & _
            vbCr & errLoop.Description
      Next errLoop
   End If
   
   Resume Next

End Sub