Genealogy - Microsoft Access Genealogy Seminar


	Back to Access Genealogy Seminar Comments List

Upload Images Link Email

Genealogy

Ina van Rooyen

7 months ago

I'm using the genealogy structure to create pedigree papers for my Dog Breeding business.
I want to show the Pedigree Name and the Colour of the dog. The PedigreeName is a field in the Dog Table which is associated with the DogID. The Colour is in a different table and each colour has a ColourID. The ColourID is a field in the Dog Table which is also associated with the DogID.
I tried a few different scenarios to link the Colour with the MotherID and FatherID with no success. Can anyone shed some light on how to do this. I will also need to link the Colour for the Mother and Father of the Mother and of the Father.

Details

Path = "z:\entity\"
    
        With Forms!frmAncestor
        !DogID = DogID
        !CallName = CallName
        !DogPicture = MyPicture
        !PedName = PedName
        !Colour = "Border Collie - " & Nz(DLookup("Colour", "tblColours", "ColourID=" & cboColour), "")
        If MyPicture <> "" And Not IsNull(MyPicture) Then
            MyPath = FilePath & !DogID & "\" & MyPicture
            !DogPicture.Picture = MyPath
        End If
        ' Mother = Dog's Mother
        If cboMother <> 0 Then
            !MotherID = cboMother
            
            !MotherName = Nz(DLookup("Callname", "tblDogs", "DogID=" & cboMother), "")
            !MotherPedName = Nz(DLookup("PedName", "tblDogs", "DogID=" & cboMother), "")
            !Mothercolour = Nz(DLookup("Colour", "tblColours", "ColourID=" & cboColour), "")
            LookupPic = Nz(DLookup("MyPicture", "tblDogs", "DogID=" & cboMother), "")
            If LookupPic <> "" Then
                MyPath = FilePath & !MotherID & "\" & LookupPic
                !MotherPicture.Picture = MyPath
            End If
        End If
        ' Father = Dog's Father
        If cboFather <> 0 Then
            !FatherID = cboFather
            !FatherName = Nz(DLookup("Callname", "tblDogs", "DogID=" & cboFather), "")
            !FatherPedName = Nz(DLookup("PedName", "tblDogs", "DogID=" & cboFather), "")
            !Fathercolour = Nz(DLookup("Colour", "tblColours", "ColourID=" & cboColour), "")
            LookupPic = Nz(DLookup("MyPicture", "tblDogs", "DogID=" & cboFather), "")
            If LookupPic <> "" Then
                MyPath = FilePath & !FatherID & "\" & LookupPic
                !FatherPicture.Picture = MyPath
            End If
        End If
        ' MM = Mother's Mother
        ID = Nz(DLookup("MotherID", "tblDogs", "DogID=" & !MotherID), 0)
        If ID > 0 Then
            !MMID = ID
            !MMName = Nz(DLookup("Callname", "tblDogs", "DogID=" & ID), "")
            LookupPic = Nz(DLookup("MyPicture", "tblDogs", "DogID=" & ID), "")
            If LookupPic <> "" Then
                MyPath = FilePath & ID & "\" & LookupPic
                !MMPicture.Picture = MyPath
            End If
        End If
        ' MF = Mother's Father
        ID = Nz(DLookup("FatherID", "tblDogs", "DogID=" & !MotherID), 0)
        If ID > 0 Then
            !MFID = ID
            !MFName = Nz(DLookup("Callname", "tblDogs", "DogID=" & ID), "")
            LookupPic = Nz(DLookup("MyPicture", "tblDogs", "DogID=" & ID), "")
            If LookupPic <> "" Then
                MyPath = FilePath & ID & "\" & LookupPic
                !MFPicture.Picture = MyPath
            End If
        End If
 
    End With
End Sub

	Ina van Rooyen OP 7 months ago
Can someone help me with this, please?

	Alex Hedley 6 months ago
Have you tried creating a Query that joins the two tables?

	Alex Hedley 6 months ago
Is `ColourID` a number or a Text Field? `Nz(DLookup("Colour", "tblColours", "ColourID=" & cboColour), "")`

Ina van Rooyen OP

6 months ago

Hi Alex, thank you for your reply. ColourID is a number.
I have activated the debugger and I can see that the MotherID is correct for the mother and subsequently for the rest of the ancestors. However, when it comes to the colour it selects the colour of the dog. That tells me that it does not pick the colourid associated with the motherid. All the dogs, parents and offspring, are listed in the Dog Table with a unique DogID. I have two fields in the Dog Table, MotherId and FatherId, which shows the DogID of the parents of each dog.
Hope this makes sense.

If cboMother <> 0 Then
            !MotherID = cboMother

            !MotherName = Nz(DLookup("Callname", "tblDogs", "DogID=" & cboMother), "")
            !MotherPedName = Nz(DLookup("PedName", "tblDogs", "DogID=" & cboMother), "")
            !Mothercolour = Nz(DLookup("Colour", "tblColours", "ColourID=" & cboColour), "")

	Alex Hedley 6 months ago
Is the control on the form actually called `cboColour`?

	Kevin Yip 6 months ago
To get the colourid associated with the motherid, you need an extra DLookup() to get it, and that's what your code seems to be missing. You said tblDogs has the ColourID field. Look up the mother's ColourID based on the mother's DogID in tblDogs. Then use that as a criteria to look up the mother's color name in tblColours.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Genealogy Seminar.