Inserting a duplicate record

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

Michael T

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.
 
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
 
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.
 
Michael,

Most (not all) ID fields are autonumber, so the act of inserting all of the
other fields would automatically generate a new ID field. In your case, it
looks like you want to enter that manually. In that case it might look
like:

Parameters [New Beaker Number] Long;
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 [New Beaker Number], 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'
Click to expand...

The down side of this method is that the [New Beaker Number] cannot be
tested to determine whether it has already been used. Instead, I would
recommend you create a dialog form that would popup when the user selects to
copy the record. This form would only need a text box for the
NewBeakerNumber, along with Next and Cancel buttons. When this form pops
up, prompt the user to enter a new id value, when they click Next, check to
make sure that the newBeakerNumber has not already been used, and if not
execute code similiar to above. If you save this query as qry_CopyRecord,
the code in your Next button might look like:

Private Sub cmd_Next_Click

Dim qdf as dao.querydef

'insert some code here to test to see if the new beaker number is
already in use.
If NOT ISNULL(DLOOKUP("BeakerNumber", "Beaker Master", "BeakerNumber = "
& me.txtNewBeakerNumber)) THEN
msgbox "Number is already in use"
exit sub
endif

set qdf = currentdb.querydefs("qry_CopyRecord")
qdf.parameters(0) = me.txtNewBeakerNumber
qdf.execute
set qdf = nothing

'You might also want to requery the main form after doing this, so that
the new record will show up. If you need help with that, let me know.

Exit Sub


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...
 
Worked perfectly thanks.

M.

Dale Fye said:
Michael,

Most (not all) ID fields are autonumber, so the act of inserting all of
the other fields would automatically generate a new ID field. In your
case, it looks like you want to enter that manually. In that case it
might look like:

Parameters [New Beaker Number] Long;
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 [New Beaker Number], 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'
Click to expand...

The down side of this method is that the [New Beaker Number] cannot be
tested to determine whether it has already been used. Instead, I would
recommend you create a dialog form that would popup when the user selects
to copy the record. This form would only need a text box for the
NewBeakerNumber, along with Next and Cancel buttons. When this form pops
up, prompt the user to enter a new id value, when they click Next, check
to make sure that the newBeakerNumber has not already been used, and if
not execute code similiar to above. If you save this query as
qry_CopyRecord, the code in your Next button might look like:

Private Sub cmd_Next_Click

Dim qdf as dao.querydef

'insert some code here to test to see if the new beaker number is
already in use.
If NOT ISNULL(DLOOKUP("BeakerNumber", "Beaker Master", "BeakerNumber =
" & me.txtNewBeakerNumber)) THEN
msgbox "Number is already in use"
exit sub
endif

set qdf = currentdb.querydefs("qry_CopyRecord")
qdf.parameters(0) = me.txtNewBeakerNumber
qdf.execute
set qdf = nothing

'You might also want to requery the main form after doing this, so that
the new record will show up. If you need help with that, let me know.

Exit Sub


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 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...
 
You're welcome

Michael T said:
Worked perfectly thanks.

M.

Dale Fye said:
Michael,

Most (not all) ID fields are autonumber, so the act of inserting all of
the other fields would automatically generate a new ID field. In your
case, it looks like you want to enter that manually. In that case it
might look like:

Parameters [New Beaker Number] Long;
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 [New Beaker Number], 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'
Click to expand...

The down side of this method is that the [New Beaker Number] cannot be
tested to determine whether it has already been used. Instead, I would
recommend you create a dialog form that would popup when the user selects
to copy the record. This form would only need a text box for the
NewBeakerNumber, along with Next and Cancel buttons. When this form pops
up, prompt the user to enter a new id value, when they click Next, check
to make sure that the newBeakerNumber has not already been used, and if
not execute code similiar to above. If you save this query as
qry_CopyRecord, the code in your Next button might look like:

Private Sub cmd_Next_Click

Dim qdf as dao.querydef

'insert some code here to test to see if the new beaker number is
already in use.
If NOT ISNULL(DLOOKUP("BeakerNumber", "Beaker Master", "BeakerNumber =
" & me.txtNewBeakerNumber)) THEN
msgbox "Number is already in use"
exit sub
endif

set qdf = currentdb.querydefs("qry_CopyRecord")
qdf.parameters(0) = me.txtNewBeakerNumber
qdf.execute
set qdf = nothing

'You might also want to requery the main form after doing this, so
that the new record will show up. If you need help with that, let me
know.

Exit Sub


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
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