copying current record to new record

[JumpinJimRivers]

I posted a thread on this previously but I'm not sure I explained it very well, so I'm going to try again because I am really stuck on this.

I need to copy all of a Current record to a New record using some append or insert method- no problem. But the record has subrecords that need to be copied too, so the problem is, after the New record is made (and obviously has a New ID from the Current) I still need the Current ID to copy the subrecords because the Current ID is obviously its foreign key. So how do I use the Current ID as the criteria to copy over the records but write the New ID as its actual foreign key so the new record has new subrecords!

Hope that makes sense and you can help. Thanks much.


This is probably not as hard as I am making it to be

 

[gol4]

If I understand what it is you are asking then I suggest you try looking into the bookmark property

if thats no help then perhaps something like this

dim oldid as variant
oldid = me!itemid
docmd.runcommand.accmdselectrecord
newid = inputbox("enter new id&quot
me!itemid = newid
docmd.runcommand.accmdpasteappend
me.itemid = oldid

what the above does is assigns the old record a newid
pastes the new record in then assigns it the oldid

 

[JumpinJimRivers]

Thanks for the help. I'm not sure that is what I need though. The bookmark property seemed liked a good idea at first but I'm not sure anymore.

To reiterate the problem yet a third time, I need to copy the subitem records along with a new main record, but if I do an append on the subitems they will still have the old record's ID which would be useless since they wouldn't have the new record's ID to relate with. So how do I use the Old Record ID as the criteria to copy over the new subitems but write the New Record ID to the new subitems without affecting the old subitems?

 

[gol4]

Jim I am sorry but I dont understand the problem here unless you are using an autonumber as the fields newid.
lets assume your tables look like this and not using autonumber

tblcar 'main
vehid desc purdate cost

tblparts 'sub has many parts
vehid partdesc vendor cost

3210 is the old id 2345 is the new

docmd.runsql "INSERT INTO Tblcar( vehid,desc,purdate,cost )
SELECT 2345 AS newid,desc,purdate,cost
FROM tblcar
WHERE (((Tblcar.vehID)=3210));"
docmd.runsql "INSERT INTO Tblparts( vehid,partdesc,vendor,cost )
SELECT 2345 AS newid,partdesc,vendor,cost
FROM tblparts
WHERE (((Tblparts.vehID)=3210));"


now if you are using an autonumber then you will need to append move to the max then use this number in place of 2345

 

[JumpinJimRivers]

Thanks for the reply gol4. After struggling with it for some time last week I decided to give myself a little brake and I haven't thought about it since. But yes, it is an autonumber. Based on your autonumber scenario, how would I get just the new sub records so I can replace the foreign key values with the new main record autonumber value?