Clone Record With Primary Key Indexed as No Duplicates

[tristap]

Am trying to clone an existing record in a MS Access database by a the click of a button on a form. Currently experiencing two issues with my code.

ISSUE 1:
The below code has an issue and advises that it cannot clone as it will cause a duplicate ID in the table where the Reference field is a primary key Autonumber indexed for No Duplicates.

When I investigated I found that the database next Reference number being assigned is a number already assigned in the database. Eg. The last autonumber assigned to the Reference field in the table was 861, however when I run the below code it is generating an autonumber of 190.

Is there any way around this error, am I doing something wrong in the code below?

Corruption issue?

ISSUE 2:
To progress past issue 1, I have removed the primary key indexing and now allowing the Reference field to allow duplicates.

The main form is cloning fine but it also has a subform (which is a continuous form) of whos records I am also trying to clone but I am getting a "SYNTAX ERROR IN INSERT INTO STATEMENT" error message when it gets to the "DoCmd.RunSQL strSql" below. The main form and subform are linked by the 'Reference' field.

I would like the code to clone the current records (may be more than one) on the subform to the clone subform.

FYI - The database is also relationshiped.

Can anyone tell me where I am going wrong.



Private Sub cmdDupe_Click()
'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 myString As String

'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 "You must select a record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!FileHeldBy = Me.FileHeldBy
!DateReceived = Me.DateReceived
!FileName = Me.FileName
!CustID = Me.CustID
!TOWID = Me.TOWID
!BusOwnerID = Me.BusOwnerID
'etc for other fields.
.Update

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

'Ask the user if they wish to copy the records notes as well
myString = MsgBox("Would you like to copy the notes of this record across to the duplicate record?", vbYesNo)
If myString = vbYes Then
'DoCmd.SetWarnings False
'Duplicate the related records: append query.
If Me.[frmDAS subform(frmWorkRequest2005)].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblDAS] (Reference, Date, Description) " & _
"SELECT " & lngID & " As Reference , Date, Description " & _
"FROM [tblDAS] WHERE Reference = " & Me.Reference & ";"
DoCmd.RunSQL strSql
Else
MsgBox "Main record duplicated, but there were no related records."
End If
Else
MsgBox "Main record has been duplicated, please complete remaining fields."
End If

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

Me.FileHeldBy.Value = ""


Exit_Handler:
Exit Sub

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

End Sub



Any help would be much appreciated.

 

[Pampers]

Maybe you can use ReplacationID as autonumber field type?
This what I found on the subject in Access Help (F1):

-----------------------------------------

About AutoNumber field size and replicated databases (MDB)
Show All
Hide All
Note The information in this topic applies only to a Microsoft Access database (.mdb).

If you plan to replicate (replication: The process of copying a database so that two or more copies can exchange updates of data or replicated objects. This exchange is called synchronization.) your database, you need to consider what field size is appropriate for the AutoNumber field used as a table's primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.). If you use an AutoNumber field as the primary key for a table in the replicated database, set its FieldSize property to either Long Integer or Replication ID.

If fewer than 100 records are routinely added between synchronizing (synchronization: The process of updating two members of a replica set by exchanging all updated records and objects in each member. Two replica set members are synchronized when the changes in each have been applied to the other.) replicas (replica: A copy of a database that is a member of a replica set and can be synchronized with other replicas in the set. Changes to the data in a replicated table in one replica are sent and applied to the other replicas.), use a Long Integer setting for the FieldSize property to take up less disk space.
If more than 100 records are routinely added between synchronizing replicas, you should use Replication ID for the FieldSize property setting to prevent records from being assigned the same primary key value in each replica. Note, however, that an AutoNumber field with a Replication ID field size produces a 128-bit value that will require more disk space.


Pampers
Keeping it simple can be complicated

 

[tristap]

Whilst I thank you for the post, not sure how this will help. I am not trying to replicate a database just a record.

Any other suggestions?

Can anyone else help?

 

[jimati]

This might help, I needed to do the same thing you are doing and I simply created an append query that had the ID field of the query = to ID field of the form. I selected every field in the table except the autonumber field. When I ran the query, it added the exact record to the table and assigned a new autonumber to it.

You can also run a 2nd query to append records for the subform.

This may not help you, but it did for me exactly what you asked above. If you think it might help, let me know and I'll explain it further.