Form that creates records from a template

[techaddiction7]

I'm trying to create a "template" form where the user can duplicate a set of records but modify a certain number of fields. for example, PC1 exists in the database and has 35 parts related to it. the user wants to be able to create a new pc, PC2, with the same 35 parts. instead of inputting each part individually i want the user to be able to create PC2 based off of PC1 but renaming it PC2 and changing who the "record updater" is for that pc. the steps i got planned are:

1. Duplicate and Append records to Temporary Table
2. Update records in temporary table based on user input.


3. Append results to main table
4. Delete records in Temporary table.

i have step one complete using an append query, but im stuck on the update query for step 2. the SQL im using for the update query is

 
UPDATE tblTemporary SET tblTemporary.AssemblyNumber = DLookUp("ID","tblTopAssembly","AssemblyNumber"=forms!frmUpdateTempTable!txtAssemblyNumber), tblTemporary.Updater = [Forms]![frmUpdateTempTable]![cboUpdater];

theres a pop-up form "frmUpdateTempTable" where the user selects the updater for the new set of records (cboUpdater) and types in the assembly number (txtAssemblyNumber). The record on this form is supposed to be saved to the table "tblTopAssembly" first then the the value typed in txtAssemblyNumber is passed to the records in tblTemporary. the updater is passed correctly, but the Assembly number isnt. the value stored in the temporary table for assembly number is the AutoNumber ID that corresponds to the AssemblyNumber that the user typed in. Any suggestions on how to successfully pass this value to tblTemporary or notes on whats wrong with my SQL? thanks in advance

[June7]

Is the field Assembly Number a text datatype? If so, use apostrophe delimiters and think DLookup syntax needs concatenation:

"AssemblyNumber='" & forms!frmUpdateTempTable!txtAssemblyNumber & "'"

[techaddiction7]

No its not , the "AssemblyNumber" field in the table "tblTemporary" is a ID number that correlates to the text of the actual "Assembly Number" that the user types in. if what im saying makes sense...

[June7]

Then try what I suggested without the apostrophes but still concatenate.

[techaddiction7]

can you clarify what im supposed to concatenate? the ampersands give me an error saying that there's an extra " ) " in the DLookup statement. however, that is needed to close out the dlookup

[June7]

UPDATE tblTemporary SET tblTemporary.AssemblyNumber = DLookUp("ID","tblTopAssembly","AssemblyNumber=" & forms!frmUpdateTempTable!txtAssemblyNumber), tblTemporary.Updater = [Forms]![frmUpdateTempTable]![cboUpdater];

[techaddiction7]

I finally figured it out! It was a syntax error. I had to put a quotation around [forms]![frmUpdateTempTable]![txtAssemblyNumber] so that access recognizes the value as a text. The correct SQL code now is

UPDATE tblTemporary SET tblTemporary.AssemblyNumber = DLookUp("ID","tblTopAssembly","AssemblyNumber = " & '[forms]![frmUpdateTempTable]![txtAssemblyNumber]'), tblTemporary.Updater = [Forms]![frmUpdateTempTable]![cboUpdater];

thanks for the help June7