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