duplicate form and subform

[Hamm]

Hi

I have a form with a subform and i am trying to duplicate everything to a new record. the Subform relates to a different table to the record details from the parent form. I am trying to use the code from Microsoft support http://support.microsoft.com/?kbid=208824 but i get the following error on the append query.



"The INSERT INTO statement contains the following unknown field name: '[Forms]![Job Details].[Tag]'. Make sure you have typed the name correctly and try the operation again."

Job Details is the name of the parent form which i am trying to duplicate.

can anyone point me to where i am going wrong and how to fix this?

thanks
Hamm

[ketbdnetbp]

Hamm -

It would appear as though there is a syntax issue in the INSERT statement. The Me.Tag property is being confused for a field name and, as such, the field cannot be found.

Reply back with your code and the SQL for the append query.

All the best,

Jim

[Hamm]

Hi Jim

The code is as follows:

Private Sub Duplicate_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form


' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone


On Error GoTo Err_btnDuplicate_Click


' Tag property to be used later by the append query.
Me.Tag = Me![ID]


' Add new record to end of Recordset object.
With Rst
.AddNew
![Job Number] = Me![Job Number]
!Status = Me!Status
![Job Type] = Me![Job Type]
![Service Line] = Me![Service Line]
![Assignment Type] = Me![Assignment Type]
![Valuation Type] = Me![Valuation Type]
![Date Billed] = Me![Date Billed]
![Fee Currency] = Me![Fee Currency]
![Gross Fee] = Me![Gross Fee]
![Fee Share To] = Me![Fee Share To]
![Fee Share Amount] = Me![Fee Share Amount]
![Net Fee] = Me![Net Fee]
![Net Fee GBP] = Me![Net Fee GBP]
!Client = Me!Client
![Client Contact] = Me![Client Contact]
![Client Type] = Me![Client Type]
![Other Parties] = Me![Other Parties]
!NAMA = Me!NAMA
![Fee Share Breakdown] = Me![Fee Share Breakdown]
![Project Name] = Me![Project Name]
!Country = Me!Country
!Size = Me!Size
![Project Leader] = Me![Project Leader]
![Assisted By] = Me![Assisted By]
!Stages = Me!Stages
![Job Stage] = Me![Job Stage]
![Meeting Comments] = Me![Meeting Comments]
![Archive Location] = Me![Archive Location]


.Update ' Save changes.
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark


' Run the Duplicate Order Details append query which selects all
' detail records that have the OrderID stored in the form's
' Tag property and appends them back to the detail table with
' the OrderID of the duplicated main form record.


DoCmd.SetWarnings False
DoCmd.OpenQuery "Duplicate Subform"
DoCmd.SetWarnings True


'Requery the subform to display the newly appended records.
Me![Assets Subform].Requery


Exit_btnduplicate_Click:
Exit Sub


Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub


and the SQL is:

INSERT INTO Assets ( ID, [Forms]![Job Details].[Tag], [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.ID, Assets.[Job ID], 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 NewOrderID
FROM Assets;


thanks
Hamm

[ketbdnetbp]

Hamm –

As suspected, in the posted code, there is a reference to the Tag property of the form, i.e. Me.Tag = Me![ID]

Then in the SQL of the append query, there is a reference to that property

INSERT INTO Assets ( ID, [Forms]![Job Details].[Tag],

First, is the [ID] field referred to above an autonumber field? If so, I don’t think it’s needed, as I have heard this will produce an error.

Secondly, the .[Tag] property reference may also produce an error, as it’s not a field in the Assets table. From what I know, usually, in that part of the SQL, you are identifying the fields to append data into, omitting, of course, any autonumber fields.

So, I would ask, which field in the assets table relates to that property’s value?

Third, I’m not an SQL expert but, from what I have heard, if you’re using a SELECT statement in conjunction with the INSERT statement, there is usually a WHERE clause that identifies which records should be selected. Which, I think, is why the tag property was originally set/used.

Lastly, does (CLng([Forms]![Job Details]![ID]) AS NewOrderID) relate to [Job ID]?
And, is [Forms]![Job Details]![ID] a text field?

Please advise.

Jim

[Hamm]

Jim

the [ID] field is an autonumber in the table Jobs and this needs to transfer to the [Job ID] field in the table Assets to link the multiple assets to the single Job.

I have taken the Tag reference direct from the code on the Microsoft website so i assumed that would work.

(CLng([Forms]![Job Details]![ID]) AS NewOrderID) relates to [Job ID] in that it is the information i want to transfer across.
[Forms]![Job Details]![ID] is an autonumber field

[Hamm]

I am still having trouble with this. Maybe there is another way around it.

I have a form which is used to input data into the table "Jobs". Within this form i have a subform which inputs data into the table "Assets". There is a field in the table "Assets" called "Job ID" which links to the ID (primary key) filed of the Jobs table. this means there can be multiple assets in the subform with the same Job ID.

I want to have a button that duplicates the record in the "Jobs" table and duplicates all the records in the subform. ie duplicate all "assets" records with the same "Job ID".

Any ideas?

Thanks
Hamm

[ketbdnetbp]

Hamm -

What have you tried recently?

Can you post the db?

If so, make a copy, remove all confidential data, compact & repair, attach to post using the Atachment Manager.

In the alternative, take a screen shot of the QBE Grid for the append query and post.


Thanks,

Jim

[Hamm]

Jim

My database (after removing all sensitive info and a bunch of non required forms and reports) is 1.16mb so cannot be uploaded. is there any way i can reduce the size?

I have attached two screen dumps of the query grid if that helps.



thanks again
Hamm

[ketbdnetbp]

Hamm - I took a very quick look at the query. I think the entry in the "append to" line in Job ID column needs to be in the moved to the Criteria row. Make the change, test, and post back the results.


Thanks,

Jim

[Hamm]

Jim

now i get a message saying "The INSERT INTO statement contains the following unknown field name: '[Job Number]'. Make sure you have typed the name correctly, and try the operation again."

Can you help with this?

[ketbdnetbp]

Hamm -

In the last column of the query, "NewOrderID:...", try putting brackets [] around Job ID, in the append to row.

Jim

[Hamm]

Jim

that hasnt changed the message i was getting
"The INSERT INTO statement contains the following unknown field name: '[Job Number]'. Make sure you have typed the name correctly, and try the operation again."

hopefully we are getting there

[ketbdnetbp]

Hamm -

Check the [Job Number] field in the query, spelling, etc.. Make sure there are not any additional spaces and enclose the name in [], if the field name in the table contains any spaces. If the error persists thereafter, I would delete that column and then re-add it to the grid and then add [Job Number] to the append to row.

Jim

[Hamm]

Jim

I have discovered that by removing the [] on each item in the append to row I don't get the error, or any error. However, when I press duplicate form it only duplicates the main form and not the sub form details.

Hamm

[ketbdnetbp]

Hamm -

I'm going to try and replicate your issue on an old DB of mine. It may take a day or so. I'll post back with findings.

Jim