Having a brain cramp

[The_Doc_Man]

I'm having a problem and either my brain is fried (not impossible) or I'm missing something (also not impossible). The setup for this is complex, so bear with me. I am working on a complicated part of my Genealogy program, something to help me analyze structural errors in the family tree.

The specific problem I am addressing is the assignment of a person to a generation. It might be a bit pretentious but my generation is numbered 0. My parents are generation 1, grandparents generation 2, my step-kids are generation -1, and so on. This generation number is assigned by a really ugly stretch of code in which I look at the relationship between two people. The relationships are always either sibling, parent, spouse, or child. So if I find someone for whom I know their generation and have a relationship link to someone whose generation I DON'T know (yet), I can assign as either known person's generation +1 if relationship is parent, no change if spouse or sibling, or -1 if child. Simple enough. I've checked and it NEVER assigns a difference other than 1, 0, or -1 - so the problem isn't mechanical. The code that assigns this repeats sweeps through all combinations of <person 1, relation, person 2> - at least 28K combinations in total - until all generations are assigned or until anomalies show up.

As the analyzer proceeds, it finds LOTS of cases where generations on both sides are known, so it compares those cases. If it knows the relationship and the generations of both parties, it can test whether the generations are consistent. A huge percentage of the time, like 98%+, no problem. But sometimes I find a relationship between two people and their generation numbers not consistent with the relationship. This is an anomaly that means a parentage link is incorrect. It happens that sometimes the contributing sub-genealogies you get back can be faulty.

To identify the problematic generation assignment, I determine the path by which the generations were assigned. This is possible because each generational assignment gets logged as it is made. I can identify the step in which the generation was assigned for each person. To complete the analysis, I want to find an anomaly (i.e. two people with a relationship inconsistent with their generation) and step back through the generation logs for both sides of the relationship. This is where I have the problem.

I find the anomaly, then look at the two people and start tracing back the left-side partner (copying the relevant assignments along the way to the table that will drive the report) and then I want to go back to the same anomaly entry but start tracing the right-side partner's assignments. To achieve that, I have code that - before I start tracing EITHER side - I take a recordset clone from Me.Recordset into a separate DAO recordset object variable and I assure that the form's current bookmark goes into that recordset object. I start AND FINISH the left-side traceback, which DOES involve form navigation. In theory, all such traceback paths have to lead back to my record because it is the first record in the database.

At the point where I want to go back to the original anomaly, I copy the bookmark from the separate recordset back to the form's Me.Recordset.Bookmark - but it says "No current record."

...
    Set rsCurAnomaly = Me.Recordset.Clone           'remember where we are
    rsCurAnomaly.Bookmark = Me.Recordset.Bookmark   'save exact pointer
...
{code that steps through the relationships to find the given assignments}
...
    Me.Recordset.Bookmark = rsCurAnomaly.Bookmark   'find original marker

No records ever get deleted by this analyzer but there are some updates to records to mark "USED" so that if there is ever a double-reference, I'll catch it. (So far, hasn't happened.) What is wrong with this approach to return the analyzer form back to the anomaly? At the moment I don't have record autonumbers because I wanted to avoid that particular bit of manipulation. If I have to do that, I will, but I was hoping to just bookmark where I wanted to return for subsequent analysis.

 

[MarkK]

I don't fully understand your problem, but looking at the code, it might be useful to know that you can just assign a bookmark to a string. It looks like you have a separate recordset variable rsCurAnomaly, and it looks like all it does is support a bookmark. Instead, you should be able to do this...

    Dim bmk As String

    bmk = Me.Recordset.Bookmark
    ' do something with Me.Recordset
    ' knowing that any requery, including filtering,
    ' will invalidate bookmarks
    Me.Recordset.Bookmark = bmk

It is also possible that something you are doing in your "code that steps through the relationships..." is invalidating the bookmarks in Me.Recordset.

 

[strive4peace]

hi Richard @The_Doc_Man

Bookmarks are temporary. If the Recordset changes, bookmarks aren't valid. Perhaps store the Primary Key field value and find it again?

Here is some generic code I call FindRecordN, that I use a LOT! To find records using a numeric primary key:

Find Record

Find a Record on a form, usually using a combo box

msaccessgurus.com

for example, if you aren't IN the control with the value and want to pass it (skipped psCtrlName_SetFocus)

call FindRecordN( Me, "myPKname", , nPKvalue)

Me can be any form reference, such as Me.subformcontrolname.form or forms!anotherformname

 

[The_Doc_Man]

I knew about finding by PK, but I was hoping to not have to go back into the assignment code to include management of a PK. It is rather complex code already. But, auto-number PK with occasional empty-the-table/reset-the-PK, here I come.

I either forgot, or perhaps never came across the issue before, that bookmarks are temporary. That fact would, however, completely explain what I have been seeing. That table was originally supposed to be a simple log, but I had to add a flag to indicate that I had already visited the record and, because of its nature, couldn't use it again in the same scan. (A re-visit would be indicative of circular relationships if it happened.) The update of that flag must have invalidated the bookmark.

Darn the bad luck. Back to the drawing board, I guess. But that leads me to the question: Where did people go after making mistakes before drawing boards were invented?

 

[GPGeorge]

Spoiler:

I knew about finding by PK, but I was hoping to not have to go back into the assignment code to include management of a PK. It is rather complex code already. But, auto-number PK with occasional empty-the-table/reset-the-PK, here I come.

I either forgot, or perhaps never came across the issue before, that bookmarks are temporary. That fact would, however, completely explain what I have been seeing. That table was originally supposed to be a simple log, but I had to add a flag to indicate that I had already visited the record and, because of its nature, couldn't use it again in the same scan. (A re-visit would be indicative of circular relationships if it happened.) The update of that flag must have invalidated the bookmark.

Darn the bad luck. Back to the drawing board, I guess. But that leads me to the question:

Where did people go after making mistakes before drawing boards were invented?

Mostly into the jaws of the prey animal chasing them at the time, I suppose.

 

[GPGeorge]

I knew about finding by PK, but I was hoping to not have to go back into the assignment code to include management of a PK. It is rather complex code already. But, auto-number PK with occasional empty-the-table/reset-the-PK, here I come.

I either forgot, or perhaps never came across the issue before, that bookmarks are temporary. That fact would, however, completely explain what I have been seeing. That table was originally supposed to be a simple log, but I had to add a flag to indicate that I had already visited the record and, because of its nature, couldn't use it again in the same scan. (A re-visit would be indicative of circular relationships if it happened.) The update of that flag must have invalidated the bookmark.

Darn the bad luck. Back to the drawing board, I guess. But that leads me to the question: Where did people go after making mistakes before drawing boards were invented?

I sympathize, genealogy is a tough racket.

I long ago made a mistake in my family history database in the way I recorded "households". I later realized that sometimes people remarry and form new households following a death or divorce. For the most part, it's a non-issue, but there is one function which I've never been able to properly define to avoid children being assigned to "previous" households in which they never resided. If they were step-children as part of a second marriage, no problem. It's only when they were born into the second (or third) marriage and were NOT part of the prior marriage household that I have the problem. Short of throwing it all out and starting over, I haven't figured out how to finesse it with the current tables. One of these days, maybe I'll be moved to rearchitect it....

What you are doing is at least one order of magnitude greater, so yeah, good luck.

 

[The_Doc_Man]

The Ancestry.COM site allows for divorces and multiple sequential marriages, but there is one member of my family that REALLY stretched their web generator. A particular woman has her biological father, birth-certificate father, adopted father, and step-father. The only one that was hard to get in there was "birth-certificate father" - the guy who was married to her mother but who, when the mother died, didn't want anything to do with the kid, and the biological father was a couple of states away when the mother died. So eventually her aunt and uncle adopted her to keep her in the family. But later, the aunt and uncle split and they both re-married. I gave up on the uncle's relationships because he married three times in total, making the later two wives step-mothers. Let's just say the diagram wasn't pretty.