Dlookup to populate a hidden field with value from a different record

[TenOc]

Access 2019 I hope this is posted in the correct forum. Maybe it should be in the programing forum????

I have a database which list about 6 generations of my family members. In the main form (frmMain) there are 3 fields of interest for this posting. They are family member (FName a key field), generation number (GenNumber), and the family member parent’s name (PName).

I have been able use a query to generate a report which list each parent and their children. This report runs very well with no problem.



I now want to generate a report which list the generation number of each parent and their children. I am able to show in this report the generation number of the child, but I am unable after many different attempts, to show the generation number of the parent (Which is on a different record from the child's record). In the report I could simply subtract 1 from the child’s generation number but this would defeat the purpose of verifying the integrity of the database. In addition, ideally, I would like to have the generation number entered automatically when a new child is entered into the database by adding 1 to the parents’ generation.

I have attempted to use dlookup to populate a hidden field for the parents’ generation number in the child’s record. I have been unsuccessful with this approach.

Any help would be greatly appreciated.

[Minty]

Forgive me for complicating the question, but surely a generation number is a calculation based on a variable starting point?
E.g. from my parents I am the 2nd generation.
But from their parents I am the 3rd generation?

[TenOc]

Forgive me for complicating the question, but surely a generation number is a calculation based on a variable starting point?
E.g. from my parents I am the 2nd generation.
But from their parents I am the 3rd generation?

The Generation number currently is MANUAL entered with my grandmother generation 0 and my mother generation 1 and me generation 2. So the generation number is the number of generations from my grandmother. It would be very nice if I could have Access calculate the generation number so there would not be so many errors in the manual input of the data.

I THINK the problem I am having with DLookup is the GenNumber is on the same table not a different table.

[Minty]

Given a starting point access can calculate that for you.
You would need to provide a starting parent, and then loop down through the available children counting as you go.

(Actually you would do this backwards, start at the child and go up as it would be complicated otherwise to make sure you headed through the records in the right direction.)
That would give you the count on each node.

[TenOc]

(Actually you would do this backwards, start at the child and go up as it would be complicated otherwise to make sure you headed through the records in the right direction.)
That would give you the count on each node.

I am a newbie, can you give more information how to do this? I do not see how I can count up through 100+ family name and always select only the correct parent and grandparent and grandgrandparent ect.

It appear simpler to simply add 1 to the parent generation number, IF (and that is the problem) I could only reference the correct parent's GenNumber.