How to duplicate ParentRecord/Childrensubrecords in VBA

[revver]

I have 2 tables with 1-many relationship. I need to be able to duplicate a record in the parent table and all its children.
So far I have a snippet thus

BeginTrans
    ' First Copy the Parent Record (from [rsS]) and then the Child records one by one
    
    With rst
       .AddNew
       .CatNum = rsS.CatNum ... and other fields
       .Notes = rsS.Notes & "Copied from " & txtSource
       .Update
 **    NewParentID = DLookup("ParentID", "ParentTable", "CatNum = '" & txtTarget & "'"
    End With
'Create Recordset of Children to copy to new parent ...
'Create New Children with NewParentID

All very good in theory but DLookup fails. It returns the first ever record in the Parent. What can I do to determine with certainty the NewParentID?

 

[Micron]

Not enough there to provide a more focused answer so I suggest you Debug.Print NewParentID after the lookup line and check what txtTarget is by using the immediate window, as in
?txtTarget
You can also check the lookup in the immediate window by substituting an actual value for txtTarget and see what you get. If it looks right but returns a value other than what you expect, I can't think of what might cause that except if the lookup itself returns Null (i.e. there is no match) and you have Nulls in the ParentID field of the table/query. However, I have no idea what you're doing with NewParentID after what you've shown.

 

[revver]

Clearly, I haven't explained myself well enough.
Simple form, frmCopy, has only 2 text boxes, txtSource and txtTarget and cmdCopy button. The VBA code shown is in the Click event of cmdCopy.

Let's say I want to duplicate existing GYn016 (txtSource) and children to new GYn064 (txtTarget) and new children.

NewParentId should be the ID of the fresh parent (for GYn064) generated by the Autonumber action of the .Update line and I expected it to be beyond the EOF of the Parent table before .Update executed but the lookup returns a value of 5, (NYn001) which is the very first remaining record in the table. (This tells me lookup didn't really find it although the .Update generated no error. Lookup just reported the first thing it found.) I need it so the new children can be linked to this new parent.

txtTarget is known. It is the content of the field on the frmCopy, (GYn064)
I am monitoring the pertinent variables using Watches.
.
I hope I have done better explaining this time.

 

[jackd]

From MSoft:
If more than one field meets criteria, the DLookup function returns the first occurrence. You should specify criteria that will ensure that the field value returned by the DLookup function is unique

 

[revver]

Thanks Jack.
In this case there is only one possible match for DLookup to find and that's the record just created. Here's why -

Earlier in the code I test CatNum for
1. the existence of the source GYn016 (bail on fail) and
2. the existence of the target GYn064 (proceed only if not found).
So, if it's there at all, there can be only one match for GYn064.
These Values are unique; that's why I want GYn064 and not just another GYm016.

It's probably less hassle to manually enter GYn064 and children. I'd certainly be done by now if I had.

 

[revver]

Solved It.
I replaced DLookup with ELookup from Allen Browne.
I still don't know (yet) why DLookup failed.

 

[Micron]

If you got ELookup to work, I have to believe there's an issue with your data. Maybe even just one record in the field being looked up has an empty string? AFAIK, it only takes one, even if it's not the record you want.