Inserting a "duplicate" record

  • Thread starter Thread starter Michael T
  • Start date Start date
M

Michael T

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.
 
Looks to me like what you've posted is exactly what you need.

Michael T said:
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.
Click to expand...
 
Duh, sorry, shoulda looked at it properly. This is what you need:

INSERT INTO [Beaker Master] (BeakerNumber, 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 '8888' , 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'

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

Michael T said:
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 said:
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.


:

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
Click to expand...
Click to expand...
be
a
Click to expand...
Click to expand...
 
Ahhhh! Put the value into the SELECT not the INSERT. I'll try that.

Thanks.

Michael.


Baz said:
Duh, sorry, shoulda looked at it properly. This is what you need:

INSERT INTO [Beaker Master] (BeakerNumber, 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 '8888' , 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'

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

Michael T said:
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.



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.


:

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
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 
It worked perfectly thanks.

M.

Michael T said:
Ahhhh! Put the value into the SELECT not the INSERT. I'll try that.

Thanks.

Michael.


Baz said:
Duh, sorry, shoulda looked at it properly. This is what you need:

INSERT INTO [Beaker Master] (BeakerNumber, 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 '8888' , 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'

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

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.



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.


:

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top