duplicate form and subform

[ketbdnetbp]

Hamm -

I followed the process (from Microsoft) you provided in your first post on an old db that had a form and subform. The main form and subform were linked together using link child & link master fields using SalesID (an autonumber in the SalesTBL and an fklng in the SalesDetailsTBL). The code and process worked without error. Below is the append query SQL for my test (example):

INSERT INTO SalesDetailsTBL ( SKU, CSQTY, UnitCost, Freight, SalesID )
SELECT SalesDetailsTBL.SKU, SalesDetailsTBL.CSQTY, SalesDetailsTBL.UnitCost, SalesDetailsTBL.Freight, CLng([Forms]![SalesTBLFRM]![SalesID]) AS NewSID
FROM SalesDetailsTBL
WHERE (((SalesDetailsTBL.SalesID)=[Forms]![SalesTBLFRM].[Tag]));

So, I suspect, there is just a syntax or field alignment issue in your append query which when fixed, should produce the desired results.

Question, in the Assets Table, is ID an autonumber field? If so, I would try something like...

INSERT INTO Assets ([Job Number], [Currency], [Market Value/Sale Price], NIY, [Income Type], [Property Name], [Asset Type], [Parent Brand], Brand, Category, [No of Rooms], Airport, Golf, Country, City, Address, [Service Line], [Assignment Type], Client, [Project Name], [Project Leader], [Date Billed], [Archive Location], [Size], [Job ID] )


SELECT Assets.[Job Number], Assets.Currency, Assets.[Market Value/Sale Price], Assets.NIY, Assets.[Income Type], Assets.[Property Name], Assets.[Asset Type], Assets.[Parent Brand], Assets.Brand, Assets.Category, Assets.[No of Rooms], Assets.Airport, Assets.Golf, Assets.Country, Assets.City, Assets.Address, Assets.[Service Line], Assets.[Assignment Type], Assets.Client, Assets.[Project Name], Assets.[Project Leader], Assets.[Date Billed], Assets.[Archive Location], Assets.Size, CLng([Forms]![Job Details]![ID]) AS NewJobID
FROM Assets WHERE(((Assets.[Job ID])=[Forms]![Job Details].[Tag]));

Test and see if this produces the needed results.

All the best,

Jim

[alcapps]

usually I add vba code to the subform .. Before Insert
that moves the id to the records.

example.. main form, ID on it.
sub form field jobid
Before Insert-of subform
me.jobid = me.parent.ID

then before the new record is inserted it puts ID into the JobID field. So I hide this field on the sub form because you are taking care of this field yourself.

Al