Creating a user "template" for creating new records

[lex404]

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

 

[spikepl]

Why do you wish go via a temp table instead of appending records directly and then modifying the appended records as needed? here is a solution for copying a record with child records http://allenbrowne.com/ser-57.html

 

[lex404]

Because if the user makes an error i didnt want it to affect the data stored on the main table. I wanted them to have the ability to look at the records to ensure they are as they should be before they are put into the main table. your suggestion is a great alternative. Thanks! I'm going to modify the code to see if i can get it to meet my needs.

 

[lex404]

spikepl,

the suggestion you provided is great, but the more i think about it, the more i dont want the new records to be added until the user verifies the records are correct and that they dont need to make any changes to some of the records. is there something wrong with my SQL statement thats causing it to not grab and store the ID of the AssemblyNumber? if so, why is it storing the ID for the Updater but not the AssemblyNumber? thanks for the help.

 

[spikepl]

I have been a bit imprecise. But sorry, I still don't get it. If you copy a given record, and insert it into your normal form as new record, the record is just as good (and virginal) as your temp table, and the main table does not contain the new record, until it has been saved.

As to your SQL : strings must be wrapped eg. in single quotes - if your AssemblyNumber is a string the input needs to be between ' and '

 

[lex404]

AssemblyNumber is the field in the temp table where the ID from the corresponding txtAssemblyNumber is stored. txtAssemblyNumber is where the actual string is typed. I did as you suggested for the SQL and put the single quotes around the input 'forms!frmUpdateTempTable!txtAssemblyNumber)'. still no luck. the reason i wanted to do it this way is because there is a list of PC's (pc1, pc2, pc3, pc4.....) and on a form, the user selects what pc they want to create another pc based off of. so they select PC1 from a combo box, and then press a button which appends the records to the temp table, and a pop-up form is shown where the user can type in the AssemblyNumber (Pc1, pc2...) and select the updater (person1, person2, person 3...) but say they mess up and type something in that they dont want, they should be able to look at the entries to ensure they are correct before it is added to the table.

 

[lex404]

SOLVED!!! I finally figured it out! It was a syntax error I did 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 you guys for the help!