Re: Inserting a "duplicate" record

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Looks to me like what you've posted is exactly what you need.

"Michael T" <michaelj@xxxxxxxxxxxxx> wrote in message
news:4MGdndZTusLqqTfbnZ2dnUVZ8smonZ2d@xxxxxxxxxxxx
Hi Dale,

Thanks for this - I've tried it and it broadly works except that I want to
have a NEW value for the key field (the ID).

The SELECT works fine but it is not a true "duplicate" that I want, it's a
duplicate of all fields bar the ID field - the ID field I'd like to be a
new
VALUE that I specify via a dialogue, something like below where I've added
comments between < >

INSERT INTO [Beaker Master] ('8888' <==== <THIS IS THE NEW VALUE FOR THE
KEY>, ItemType, MonarchOrPerson, EventName, CorrectDate, Maker, Style,
GoddenReference, DesignNumber, RimColour, RimCondition, MadeInTown,
MadeIn,
Material, MaterialColour, PatternColour, RegistrationNumber,
LimitedEditionNumber, LimitedEditionOf, LimitedEditionCertificate,
ItemHeight, ItemDiameter, DougNumber, PurchasePrice, PurchasedFrom,
PurchaseDate, EBayReference, CurrentValueEstimate, CurrentValueEstimateBy,
Status, ForSale, StorageLocation, SoldTo, SoldDate, SoldFor, DateAdded,
CommemorationTown, CommemorationPerson, CommemorationSite,
CommemorationEvent, CommemorationEventDate, Vendor, Description,
DescriptionHidden, DaveyNumber )
SELECT BeakerNumber <==== <THIS IS THE KEY FIELD>, ItemType,
MonarchOrPerson, EventName, CorrectDate, Maker, Style, GoddenReference,
DesignNumber, RimColour, RimCondition, MadeInTown, MadeIn, Material,
MaterialColour, PatternColour, RegistrationNumber, LimitedEditionNumber,
LimitedEditionOf, LimitedEditionCertificate, ItemHeight, ItemDiameter,
DougNumber, PurchasePrice, PurchasedFrom, PurchaseDate, EBayReference,
CurrentValueEstimate, CurrentValueEstimateBy, Status, ForSale,
StorageLocation, SoldTo, SoldDate, SoldFor, DateAdded, CommemorationTown,
CommemorationPerson, CommemorationSite, CommemorationEvent,
CommemorationEventDate, Vendor, Description, DescriptionHidden,
DaveyNumber
FROM [Beaker Master] WHERE BeakerNumber = '1362'

Where the '8888' is a new key value using all other field values from the
record with the key (BeakerNumber) of '1362'.

Any further thoughts would be appreciated.

Thanks,

Michael.



"Dale Fye" <dale.fye@xxxxxxxxxx> wrote in message
news:5FFE47A1-D08C-4C81-AD06-3F14A4E4EEC9@xxxxxxxxxxxxxxxx
I'm going to assume you have a continuous form, and that the table that
populates this form contains an ID field, and this ID value is numeric
and
is
displayed in a txt_ID textbox (actually I wouldn't display it, but would
set
the textboxes visible property to No).

If this is the case, you could have a command button in the footer of
the
form (I'll call it cmd_Copy_Record) and use that buttons Click event to
execute a sql statement something like:

Private cmd_CopyRecord_Click

Dim strSQL as string

'leave the ID field out of the insert statement
strSQL = "INSERT INTO yourTable (field2, field3, ....) " _
& "SELECT field2, field3, ..... " _
& "FROM yourTable " _
& "WHERE ID = " & me.txt_ID
currentdb.execute strsql

me.requery

End Sub

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


"Michael T" wrote:

Please can someone advise me of the easiest way to insert a "duplicate"
row
as follows:

Select a row to copy from a table - put it somewhere temporary
Replace the single key field with a new value
Insert the new "duplicate" row into the table

The table has over a dozen fields in each row, some are look-up fields
(which must all be valid in the row to be copied) so that should not be
a
problem.

A quick coding in Access VBA would be helpful.

I was wondering if there was some way to use a dynamic array to pop the
row
to be copied into it or perhpas an identical dummy table as an interim
state.

All the best,

Michael.








.

Quantcast