Processing table using code: need to duplicate a record

[tsman]

I have a record that I want to duplicate using code and only change the key.  I cannot find any way to do this.  I ended up writing some append queries to do the trick.  This is awkward for what I am doing.  Please help!!!

 

[JARE]

The easiest way would be to set up a form with a<br>button using docmnd on the On_Click() event<br>Example:<br>&nbsp;docmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70<br>&nbsp;docmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70<br>&nbsp;docmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 <br>'executes a paste append (copies record)<br>Me![ID] = 6<br>Me![KEY] = &quot;EXAMPLE&quot;<br>'assigns the number 6 to [ID] and the word EXAMPLE to [KEY] 'in the new record.<br>

 

[WP]

Jare,<br><br>This is scary stuff !&nbsp;&nbsp;Though, if it works then why not.<br><br>P.S. I have not forgotten about your barcode stuff.<br><br>Tsman,<br><br>If your felling bolder you may want to use some SQL for this e.g.<br><br>Docmd.RunSQL &quot;INSERT INTO Table2 (Field1, Field2 etc) Values(txtField1, txtField2 etc);<br><br><br>Note that txtField1... is the name of the text field on yor form.<br><br>WP<br> <p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[tsman]

SQL will not work since I do not want to define every field (56 fields in just one of the tables).&nbsp;&nbsp;I will try the copy paste.&nbsp;&nbsp;I do not know how this works when the records is being read in code not from the form.<br><br>JARE: Will this work my way or is a form required for copy/paste?

 

[JARE]

To use it you will need to perform these actions<br>from a form, and be sure before the code is ran<br>that your form has selected the correct record to <br>copy. there is probably a more efficient way to<br>do this but I am no Programmer. this actually<br>emulates SELECT,COPY,PASTE APPEND from the &quot;EDIT MENU&quot;.<br>Actually you could probably use it in a TABLE if you<br>set the code to run in a macro.<br>

 

[WP]

tsman,<br><br>When you say read in code do you mean from a defined DAO recordset ?<br><br><br>WP<br> <p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[MichaelRed]

tsman,<br><br>From a different point of view, you can set up a &quot;copy&quot; of the table, with no records (e.g. copy structure only).&nbsp;&nbsp;When you need to duplicate the record, copy it to the Duplicate table, change the ID field, copy the record from the Dup table to the base table, delete the record from the Dup table and continue.&nbsp;&nbsp;This will allow you to use SQL for all of the processing, without needing to enumerate the fields, at the expense of having a small extra table.<br><br>It should also be possible to do the operation as two or three queries without enumerating the fields, by having a parameter for the field(s) which are changed.&nbsp;&nbsp;With out more details (and time) it is hard to give more advice.&nbsp;&nbsp;Please look at Parameter Queries in the MS Access documentation.&nbsp;&nbsp;Mostly, they give examples for inputting criteria in the selection(s). however - as usual w/ MS - hte examples are just a hint as to what can be done.&nbsp;&nbsp;Also, you can build the queries in the query builder, test them there, and then copy the SQL into your code for any run time changes.<br><br>MichaelRed<br>There is never time to do it right but there is always time to do it over.<br>

 

[tsman]

WP: Yes,&nbsp;&nbsp;Reading a table in code is using recordsets.<br><br>JARE:&nbsp;&nbsp;I am using Access 97 and cannot seem to find a good resource for the Windows 95 menus used in acMenuVer70.&nbsp;&nbsp;Do you have a list or a resource I can visit?&nbsp;&nbsp;Help text does not help in this case.

 

[tsman]

MichaelRed:&nbsp;&nbsp;I have a response time issue here.&nbsp;&nbsp;Doing all that you mentioned adds too much overhead to the processing.&nbsp;&nbsp;It is working using append queries, I just don't like having to define every field in the query since the table structure could change and then all queries that access the table must be revisited.&nbsp;&nbsp;The fewer the better.<br><br>I always fall back on using DAO in code to handle anything I can since it is much more flexible.

 

[MichaelRed]

tsman,<br><br>you <b>do not</b> need to be concerned w/ the table structure using parameter queries, except for the field(s) with the parameters.<br><br>If response time is really a problem, then the query approach should be faster than the SQL, but probably not by very much.&nbsp;&nbsp;The major difference there is the queries are (supposedly) pre-compiled by MS Access, but compile timme for single queries are generally in the milli-seconds.&nbsp;&nbsp;I do not know your whole process, so the advice is just from the perspective of what you have shared.&nbsp;&nbsp;From here, there won't be much difference in the time requirements for