How to Clone a record.

[Peter Hibbs]

Access 2003 SP3

I have a table (tblProducts) which has about 50 fields, the main field
is named ItemCode (Text) which defines the product code. The field is
Unique and Indexed (No Duplicates).

The users often have to add a new product to the table which is
sometimes almost identical to an existing product in the table, i.e.
Widget-Red, Widget-Green, etc. So rather then having to enter the same
data in all the fields I want to provide them with a button on the
Products Entry form which will 'clone' the existing record.

The idea is that they would find a similar product to the new one,
click the Clone button which would pop up a form into which they can
enter a new item code and when they click an OK button it will add
another record to the products table which will have the new item code
but the same data from all the other fields.

Now that is relatively easy to do using SQL BUT what I would like to
do, if possible, is to do it without referencing the field names. The
reason for that is I occasionally have to add a new field to the
products table and it is quite possible that I will forget that I also
will need to change this code each time I do that.

What I had in mind was something like :-

CurrentDb.Execute "INSERT INTO tblProducts SELECT * FROM tblProducts
WHERE ItemCode = '" & txtOldProduct & "'"

This doesn't work, of course, because the ItemCode field is Unique so
the new record does not get added to the table. I could create a new
record first with the INSERT INTO command and then use UPDATE to
update the other 50 fields from the existing record but that has to be
done by defining each field name in turn.

Is there any way to do an UPDATE without knowing the field names or
how many fields there are in the table?

Thanks.

Peter Hibbs.

 

[Douglas J. Steele]

Why not generate the SQL dynamically?

You can loop through the TableDef object's Fields collection to get the name
of each field. Just ignore the ItemCode field when building the list.

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim strFieldList As String
Dim strSQL As String

set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("tblProducts")
For Each fldCurr In tdfCurr.Fields
If fldCurr.Name <> "ItemCode" Then
strFieldList = strFieldList & "[" & fldCurr.Name & "], "
End If
Next fldCurr
strFieldList = Left$(strFieldList, Len(strFieldList) - 2)
strSQL = "INSERT INTO tblProducts (" & strFieldList & ") " & _
"SELECT " & strFieldList & " FROM tblProducts " & _
"WHERE ItemCode = '" & txtOldDProduct & "'"

 

[Peter Hibbs]

Of course, I had forgotten that technique (even though I've used it
before).

Thanks very much Doug, I will look into that.

Peter.

 

[Roger Carlson]

Unless I'm missing something, there's an easier way. The Button Wizard has
a setting for Duplicating a Record. The code created comes down to this:
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

After that, you can manually modify the fields you want.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L