Duplicate the record in form and subform

  • Thread starter Thread starter Dorci
  • Start date Start date
D

Dorci

Access 2003 - I copied the code from http://allenbrowne.com/ser-57.html, and
I'm getting the following error message...

"Run-time error '3022'. The changes you requested to the table were not
successful because they would create duplicate values in the inces, pirmary
key, or reltionship. Change the data in the field or fields that contain
duplicate date, remove the index, or redefine the index to permit duplicate
entries and try again."

....on this piece of the code...

.AddNew
![MTNumber] = Me.[MTNumber]
![CustomerID] = Me.[CustomerID]
![CompanyName] = Me.[CompanyName]
![Address] = Me.[Address]
(etc.)
.Update

"MTNumber" is an autonumber and is the indexed primary key. I tried
removing that line from the code, thinking the table would automatically
generate the next autonumber, but that didn't work. Any ideas on how to
duplicate the record without removing primary key?

Thanks, Dorci
 
Look at the field properties of the table and you're see that one or more of
the fields have the INDEX property set to YES (NO DUPLICATES). That's the
field that's causing the issue. The error is indicating that a value already
exists AND the settings for the field indicate that the value can only exist
in any one record.

Off hand, I would guest that the issue is the CustomerID. If CustomerID
isn't the primary key for the table, change the INDEX property from YES (NO
DUPLICATES) to YES (DUPLICATES OK).

Out of curiosity, what is the nature of the underlying records in the table?
Based on the information provided, the [CompanyName] and [Address] fields are
a red flag suggesting that there may be an opportunity to improve the design
of the database.
 
Oh and if MTNumber is an autonumber is SHOULD be removed as it will continue
to cause the error in and of itself.
 
The records are material transfer forms; they identify the contents and
chemical composition of items shipped from our plant. Normally I would
capture customer information in a separate table for efficiency, but this
particular group prefers to enter the customer information for each record
(thus the need for a "duplicate" function).

MTNumber Indexed = Yes (No Duplicates)
CustomerID Indexed = No
CompanyName Indexed = Yes (Duplicates OK)
ALL other fields Indexed = No

MTNumber is the only field that specifies no duplicates.

David H said:
Oh and if MTNumber is an autonumber is SHOULD be removed as it will continue
to cause the error in and of itself.

Dorci said:
Access 2003 - I copied the code from http://allenbrowne.com/ser-57.html, and
I'm getting the following error message...

"Run-time error '3022'. The changes you requested to the table were not
successful because they would create duplicate values in the inces, pirmary
key, or reltionship. Change the data in the field or fields that contain
duplicate date, remove the index, or redefine the index to permit duplicate
entries and try again."

...on this piece of the code...

.AddNew
![MTNumber] = Me.[MTNumber]
![CustomerID] = Me.[CustomerID]
![CompanyName] = Me.[CompanyName]
![Address] = Me.[Address]
(etc.)
.Update

"MTNumber" is an autonumber and is the indexed primary key. I tried
removing that line from the code, thinking the table would automatically
generate the next autonumber, but that didn't work. Any ideas on how to
duplicate the record without removing primary key?

Thanks, Dorci
Click to expand...
Click to expand...
 
Is it the government? Don't they care about NOT duplicating work, not to
mention the potential to introduce errors into the database?

Dorci said:
The records are material transfer forms; they identify the contents and
chemical composition of items shipped from our plant. Normally I would
capture customer information in a separate table for efficiency, but this
particular group prefers to enter the customer information for each record
(thus the need for a "duplicate" function).

MTNumber Indexed = Yes (No Duplicates)
CustomerID Indexed = No
CompanyName Indexed = Yes (Duplicates OK)
ALL other fields Indexed = No

MTNumber is the only field that specifies no duplicates.

David H said:
Oh and if MTNumber is an autonumber is SHOULD be removed as it will continue
to cause the error in and of itself.

Dorci said:
Access 2003 - I copied the code from http://allenbrowne.com/ser-57.html, and
I'm getting the following error message...

"Run-time error '3022'. The changes you requested to the table were not
successful because they would create duplicate values in the inces, pirmary
key, or reltionship. Change the data in the field or fields that contain
duplicate date, remove the index, or redefine the index to permit duplicate
entries and try again."

...on this piece of the code...

.AddNew
![MTNumber] = Me.[MTNumber]
![CustomerID] = Me.[CustomerID]
![CompanyName] = Me.[CompanyName]
![Address] = Me.[Address]
(etc.)
.Update

"MTNumber" is an autonumber and is the indexed primary key. I tried
removing that line from the code, thinking the table would automatically
generate the next autonumber, but that didn't work. Any ideas on how to
duplicate the record without removing primary key?

Thanks, Dorci
Click to expand...
Click to expand...
Click to expand...
 
LOL! Good one. :)

But any idea why the code fails even though there are no other indexes?
Could it be the relationships? There is a one-to-many relationship (w/ref.
integ.) on the MTNumber field between the "MT" table and the "Items" table.

David H said:
Is it the government? Don't they care about NOT duplicating work, not to
mention the potential to introduce errors into the database?

Dorci said:
The records are material transfer forms; they identify the contents and
chemical composition of items shipped from our plant. Normally I would
capture customer information in a separate table for efficiency, but this
particular group prefers to enter the customer information for each record
(thus the need for a "duplicate" function).

MTNumber Indexed = Yes (No Duplicates)
CustomerID Indexed = No
CompanyName Indexed = Yes (Duplicates OK)
ALL other fields Indexed = No

MTNumber is the only field that specifies no duplicates.

David H said:
Oh and if MTNumber is an autonumber is SHOULD be removed as it will continue
to cause the error in and of itself.

:

Access 2003 - I copied the code from http://allenbrowne.com/ser-57.html, and
I'm getting the following error message...

"Run-time error '3022'. The changes you requested to the table were not
successful because they would create duplicate values in the inces, pirmary
key, or reltionship. Change the data in the field or fields that contain
duplicate date, remove the index, or redefine the index to permit duplicate
entries and try again."

...on this piece of the code...

.AddNew
![MTNumber] = Me.[MTNumber]
![CustomerID] = Me.[CustomerID]
![CompanyName] = Me.[CompanyName]
![Address] = Me.[Address]
(etc.)
.Update

"MTNumber" is an autonumber and is the indexed primary key. I tried
removing that line from the code, thinking the table would automatically
generate the next autonumber, but that didn't work. Any ideas on how to
duplicate the record without removing primary key?

Thanks, Dorci
Click to expand...
Click to expand...
Click to expand...
 
Ok so I took the most obviously way out.

Can you post the entire code? I'd like to see information on the record
source that you're using.
 
Here goes:

'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
' ![MTNumber] = Me.[MTNumber] 'commented out
![CustomerID] = Me.[CustomerID]
![CompanyName] = Me.[CompanyName]
![Address] = Me.[Address]
![City] = Me.[City]
![PostalCode] = Me.[PostalCode]
![StateOrProvince] = Me.[StateOrProvince]
![Country] = Me.[Country]
![ContactName] = Me.[ContactName]
![DateEntered] = Date
![TransportationCharge] = Me.[TransportationCharge]
![PayMethod] = Me.[PayMethod]
![Carrier] = Me.[Carrier]
![ProjectNumber] = Me.[ProjectNumber]
![SalesOrder] = Me.[SalesOrder]
![SJNumber] = Me.[SJNumber]
![ShippingPlant] = Me.[ShippingPlant]
![Reference] = Me.[Reference]
![ReceivedBy] = Me.[ReceivedBy]
![Category] = Me.[Category]
![AuthorizedBy] = Me.[AuthorizedBy]
![Address2] = Me.[Address2]
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !MTNumber

'Duplicate the related records: append query.
If Me.[subfrmMTItems].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [MT Items] (MTNumber, Quantity,
Description, UnitCost, Amount ) " & _
"SELECT " & lngID & " As NewID, MTNumber, Quantity,
Description, UnitCost, Amount " & _
"FROM [subfrmMTItems] WHERE MTNumber = " & Me.MTNumber &
";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdCopy_Click"
Resume Exit_Handler
 
I've never appened records to the RecordSetClone, although I've heard that it
is possible.

I would change to the code to open the table into which the child records
should be added using code similar to...
Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset([table name here])
with rst
.AddNew
Code: 
.update
.Close
end with
Set rst = Nothing
Set db = Nothing

[QUOTE="Dorci"]
Here goes:

'On Error GoTo Err_Handler
'Purpose:   Duplicate the main form record and related records in the
subform.
Dim strSql As String    'SQL statement.
Dim lngID As Long       'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
'                ![MTNumber] = Me.[MTNumber]        'commented out
![CustomerID] = Me.[CustomerID]
![CompanyName] = Me.[CompanyName]
![Address] = Me.[Address]
![City] = Me.[City]
![PostalCode] = Me.[PostalCode]
![StateOrProvince] = Me.[StateOrProvince]
![Country] = Me.[Country]
![ContactName] = Me.[ContactName]
![DateEntered] = Date
![TransportationCharge] = Me.[TransportationCharge]
![PayMethod] = Me.[PayMethod]
![Carrier] = Me.[Carrier]
![ProjectNumber] = Me.[ProjectNumber]
![SalesOrder] = Me.[SalesOrder]
![SJNumber] = Me.[SJNumber]
![ShippingPlant] = Me.[ShippingPlant]
![Reference] = Me.[Reference]
![ReceivedBy] = Me.[ReceivedBy]
![Category] = Me.[Category]
![AuthorizedBy] = Me.[AuthorizedBy]
![Address2] = Me.[Address2]
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !MTNumber

'Duplicate the related records: append query.
If Me.[subfrmMTItems].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [MT Items] (MTNumber, Quantity,
Description, UnitCost, Amount ) " & _
"SELECT " & lngID & " As NewID, MTNumber, Quantity,
Description, UnitCost, Amount " & _
"FROM [subfrmMTItems] WHERE MTNumber = " & Me.MTNumber &
";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdCopy_Click"
Resume Exit_Handler


[QUOTE="David H"]
Ok so I took the most obviously way out.

Can you post the entire code? I'd like to see information on the record
source that you're using.[/QUOTE]
[/QUOTE]
 
The records are material transfer forms; they identify the contents and
chemical composition of items shipped from our plant. Normally I would
capture customer information in a separate table for efficiency, but this
particular group prefers to enter the customer information for each record
(thus the need for a "duplicate" function).
Click to expand...

They are WRONG. This might be good spreadsheet logic but it's NOT appropriate
for a relational database. Storing the customer information redundantly is *a
very bad idea indeed* - not only does it waste space and bloat your database,
but suppose a customer's name or address needs to be changed? You now will
have to track down every single record containing it, and make sure they're
*all* fixed, rather than fixing it once in the Customer table.
MTNumber Indexed = Yes (No Duplicates)
CustomerID Indexed = No
CompanyName Indexed = Yes (Duplicates OK)
ALL other fields Indexed = No
Click to expand...

If the MTNumber is the Primary Key then it *CANNOT* be added as a duplicate
record - the primary key is by definition unique. In addition, an Autonumber
field cannot be updated. You certainly would not want to create two records
for which every field is duplicated! At the very least, simply don't include
the MTNumber in your new record; if it's an autonumber it will be incremented,
giving you two records with different MTNumbers (allowing Access to keep track
of which record is which), even if they have redundant identical data in the
other fields.
 
While I do agree with the idea of not duplicating work and normalizing the
database, it sounded as if there might actually be an explicit need to
document the specific address to which the items were shipped and to preserve
that information. (This might also apply to the ShipFrom.) While, the ShipTo
address might be the same as the address on file, there might be a specific
business need to retain the address to which the items were shipped. If the
company address changes, you wouldn't want the ShipTo address to be updated
or otherwise altered.

If there's a Bill of Lading invovled, the ShipFrom and ShipTo would have to
be preserved in the event that the original document needs to be recreated.
 
While database normalization would still mandate that the ShipFrom and ShipTo
be contained in a table and the specific shipment information in another,
you'd have to build in code to restrict updating the addresses to preserve
the documentation of the From/To. If there are shipping documents invovled, I
would argue that it would be appropriate to keep this part of the database
de-normalized.
 
While database normalization would still mandate that the ShipFrom and ShipTo
be contained in a table and the specific shipment information in another,
you'd have to build in code to restrict updating the addresses to preserve
the documentation of the From/To. If there are shipping documents invovled, I
would argue that it would be appropriate to keep this part of the database
de-normalized.
Click to expand...

Good point, and I fully agreel I'd go further and say that keeping the address
actually shipped to in the shipping table IS properly normalized. I may have
misunderstood the original post - I gathered that they were storing all of the
customer data.
 
Sorry, I had gone home for the day and, truthfully, I had to get this
database out of my head for awhile. However, you two came to the right
conclusion, these are shipping records with a BOL involved, and we do not
want the historic records changed.

David, I will try your suggestion today and get back to you. Wish me luck!
 
Okay, I'm confused already (that didn't take long). I'm not sure where to
insert your suggested code. You said to "open the table into which the CHILD
records should be added..." I need to first create a new PARENT record.
That is where the original code is failing. However, I am not opposed to
scrapping the original code.

In fact, I can successfully create a new parent record using MenuItem
methods, but I can't seem to capture the MTNumber to use for the child
records. (See my other post titled "Cannot refer to autonum in new record".)

I am so sorry to be such a pain. I thought surely this must have been done
before. I appreciate your patience and assistance.
 
Try this...

Instead of using the RecordSetClone, it accesses the underlying table
directly. You'll need to update the [tableNameHere] with the correct table
for the parent records. I ran across a post that indicated that while you can
append a record using a RecordSetClone that it is dependent upon the type of
recordset that the clone is based on.
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

Dim db as DAO.Database
Dim rst as DAO.RecordSet

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.

'This will allow you to work directly with the underlying recordset.
Set db = DBEngine.Workspaces(0).Databases(0)
Click to expand...
'Update [tableNameHere] to the table for the parent records
Set rst = db.OpenRecordset([table name here])
Click to expand...
With rst
.AddNew
' ![MTNumber] = Me.[MTNumber] 'commented out
![CustomerID] = Me.[CustomerID]
![CompanyName] = Me.[CompanyName]
![Address] = Me.[Address]
![City] = Me.[City]
![PostalCode] = Me.[PostalCode]
![StateOrProvince] = Me.[StateOrProvince]
![Country] = Me.[Country]
![ContactName] = Me.[ContactName]
![DateEntered] = Date
![TransportationCharge] = Me.[TransportationCharge]
![PayMethod] = Me.[PayMethod]
![Carrier] = Me.[Carrier]
![ProjectNumber] = Me.[ProjectNumber]
![SalesOrder] = Me.[SalesOrder]
![SJNumber] = Me.[SJNumber]
![ShippingPlant] = Me.[ShippingPlant]
![Reference] = Me.[Reference]
![ReceivedBy] = Me.[ReceivedBy]
![Category] = Me.[Category]
![AuthorizedBy] = Me.[AuthorizedBy]
![Address2] = Me.[Address2]
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !MTNumber
'Duplicate the related records: append query.
If Me.[subfrmMTItems].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [MT Items] (MTNumber, Quantity,
Description, UnitCost, Amount ) " & _
"SELECT " & lngID & " As NewID, MTNumber, Quantity,
Description, UnitCost, Amount " & _
"FROM [subfrmMTItems] WHERE MTNumber = " & Me.MTNumber &
";"
db.Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
.Close
End With
End If

Exit_Handler:
'Clear the object variables here in the event that an Error was thrown
Set rst = Nothing
Set db = Nothing
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdCopy_Click"
Resume Exit_Handler
Click to expand...
 
David H said:
Try this...

Instead of using the RecordSetClone, it accesses the underlying table
directly. You'll need to update the [tableNameHere] with the correct table
for the parent records. I ran across a post that indicated that while you can
append a record using a RecordSetClone that it is dependent upon the type of
recordset that the clone is based on.
Click to expand...

I found the post. Records can be appened to a RecordSetClone if the
recordset that its based on is updatable. Since you're working with the
recordset for a form, I'm assuming that the underlying RS is. (Information
only)
 
Dave,

I am at a total loss so I'm taking a new route. I can almost get the
following code to work, with the exception of identifying the new key value
to assign to the child records (see below). I am stumpted at the comment
"Look for the new key". Can you help?
 
Sorry, here's the code:

Dim varOrigID, varNewID As Long

' Capture original MTNumber
varOrigID = Me.MTNumber

' To add the records to the main table, add all fields except of the key,
' it will be assign automatically.
DoCmd.RunSQL "INSERT INTO [Material Transfer]( [CustomerID], [CompanyName],
[Address ], " & _
"[City], [PostalCode], [StateOrProvince], [Country], [ContactName], " & _
"[TransportationCharge], [PayMethod], [NumOfCtns], [Carrier],
[ProjectNumber], " & _
"[SalesOrder], [SJNumber], [ShippingPlant], [Reference], [ReceivedBy],
[Category], " & _
"[AuthorizedBy], [Address2], [DateEntered] ) " & _
"SELECT [Material Transfer].CustomerID, [Material Transfer].CompanyName,
" & _
"[Material Transfer].Address , [Material Transfer].City, [Material
Transfer].PostalCode, " & _
"[Material Transfer].StateOrProvince, [Material Transfer].Country, " & _
"[Material Transfer].ContactName, [Material
Transfer].TransportationCharge, " & _
"[Material Transfer].PayMethod, [Material Transfer].NumOfCtns, [Material
Transfer].Carrier, " & _
"[Material Transfer].ProjectNumber, [Material Transfer].SalesOrder, " & _
"[Material Transfer].SJNumber, [Material Transfer].ShippingPlant,
[Material Transfer].Reference, " & _
"[Material Transfer].ReceivedBy, [Material Transfer].Category, [Material
Transfer].AuthorizedBy, " & _
"[Material Transfer].Address2, Date " & _
"FROM [Material Transfer]" & _
"WHERE [MTNumber] = " & varOrigID

' Look for the new key
'varNewID = 'HEEEEEEEEEEEEELP here!

' Insert the new records in the second table based on the old key,
' but with the new key above.
DoCmd.RunSQL "INSERT INTO [MT Items] ([MTNumber], [Quantity], [Description],
[UnitCost], [Amount]) " & _
"SELECT " & varNewID & ", [MT Items].[Quantity], [MT Items].[Description],
[MT Items].[UnitCost], [MT Items].[Amount]" & _
"FROM [MT Items] WHERE [MTNumber] = " & varOrigID

' Refresh the form, so the new record will be added.
Me.Requery

' Look for the new record
With Me.RecordsetClone
.FindFirst "[Mtnumber] = " & varNewID
If .NoMatch Then
MsgBox "Record not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With
 
Did you try the code that I posted in my last post? I modified the original
routine.

Dorci said:
Sorry, here's the code:

Dim varOrigID, varNewID As Long

' Capture original MTNumber
varOrigID = Me.MTNumber

' To add the records to the main table, add all fields except of the key,
' it will be assign automatically.
DoCmd.RunSQL "INSERT INTO [Material Transfer]( [CustomerID], [CompanyName],
[Address ], " & _
"[City], [PostalCode], [StateOrProvince], [Country], [ContactName], " & _
"[TransportationCharge], [PayMethod], [NumOfCtns], [Carrier],
[ProjectNumber], " & _
"[SalesOrder], [SJNumber], [ShippingPlant], [Reference], [ReceivedBy],
[Category], " & _
"[AuthorizedBy], [Address2], [DateEntered] ) " & _
"SELECT [Material Transfer].CustomerID, [Material Transfer].CompanyName,
" & _
"[Material Transfer].Address , [Material Transfer].City, [Material
Transfer].PostalCode, " & _
"[Material Transfer].StateOrProvince, [Material Transfer].Country, " & _
"[Material Transfer].ContactName, [Material
Transfer].TransportationCharge, " & _
"[Material Transfer].PayMethod, [Material Transfer].NumOfCtns, [Material
Transfer].Carrier, " & _
"[Material Transfer].ProjectNumber, [Material Transfer].SalesOrder, " & _
"[Material Transfer].SJNumber, [Material Transfer].ShippingPlant,
[Material Transfer].Reference, " & _
"[Material Transfer].ReceivedBy, [Material Transfer].Category, [Material
Transfer].AuthorizedBy, " & _
"[Material Transfer].Address2, Date " & _
"FROM [Material Transfer]" & _
"WHERE [MTNumber] = " & varOrigID

' Look for the new key
'varNewID = 'HEEEEEEEEEEEEELP here!

' Insert the new records in the second table based on the old key,
' but with the new key above.
DoCmd.RunSQL "INSERT INTO [MT Items] ([MTNumber], [Quantity], [Description],
[UnitCost], [Amount]) " & _
"SELECT " & varNewID & ", [MT Items].[Quantity], [MT Items].[Description],
[MT Items].[UnitCost], [MT Items].[Amount]" & _
"FROM [MT Items] WHERE [MTNumber] = " & varOrigID

' Refresh the form, so the new record will be added.
Me.Requery

' Look for the new record
With Me.RecordsetClone
.FindFirst "[Mtnumber] = " & varNewID
If .NoMatch Then
MsgBox "Record not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With


Dorci said:
Dave,

I am at a total loss so I'm taking a new route. I can almost get the
following code to work, with the exception of identifying the new key value
to assign to the child records (see below). I am stumpted at the comment
"Look for the new key". Can you help?
Click to expand...
Click to expand...
 
Back
Top