Duplicate record code from Allen Browne

[Guest]

I have copied and am attempting to use the following code from Allen Browne,
but for some reason it won't execute properly. I get the message that the
..Update in the code is incorrect. Can someone help me figure out what I have
done wrong? Thanks in advance.



Private Sub cmdbtnRepeat_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.

'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
!ToDoID = Me.ToDoID
!ToDoCategory = Me.ToDoCategory
!ToDoAction = Me.ToDoAction
!Other = Me.Other
!ProgressNotes = Me.ProgressNotes

'etc for other fields.
.Update

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

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

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdbtnRepeat_Click"
Resume Exit_Handler
End Sub

 

[Guest]

Is ToDoID an autonumber???

If so, you cannot assign a number to it. Comment out the line:

!ToDoID = Me.ToDoID


and see if it runs.


You should also be able to comment out these two lines

.Bookmark = .LastModified
lngID = !ToDoID

In the example, they were used to duplicate subform records.


HTH

 

[Guest]

SteveS - Thanks so much for your help!! I did as you suggested and was
thrilled that it did it the first time! Then, when I tried to do it the
second time it debugged at .Update and gave me the message "The changes you
requested to the table were not successful because they would create
duplicate values in the index, primary key, or relationship. Change the data
in the field or fields that contain duplicate data, remove the index, or
redefine the index to permit duplicate entries and try again."

Any suggestions?

 

[Guest]

Hi Sharon,

Apparently, you migh have another field (other than the PK) that has an
index that does not allow duplicates.

Is the recordsource of the form a table or a query?


I created a table with 3 fields: ID (autonumber - PK), texttest (text) and
numtest (long).

I created a form and added the 3 fields (continous forms). I named the 2
text boxes T_Test and N_TEST.
I added a button and pasted in this code:


'------------------------------
On Error GoTo Err_Handler
'Purpose: Duplicate the main form record

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 not on a new record.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!texttest = Me.T_Test
!numtest = Me.N_TEST
.Update

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

Exit_Handler:
Exit Sub

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

'------------------------------


I added records with no problems. Note the two lines after ".AddNew" .
Access names controls that are bound to a field the same name as the field
name. I always rename the control to be able to tell which is the field and
which is the control..

So, if the code ran once, then there is not a problem with the code.

One way to determine which control/field is the problem is to comment out
all but one line that updates the new record:

!ToDoCategory = Me.ToDoCategory
' !ToDoAction = Me.ToDoAction
' !Other = Me.Other
' !ProgressNotes = Me.ProgressNotes

If the record is added OK for the ToDoCategory field, comment it out and
uncomment the next line. Do this until you get an error. Then determine what
the problem is with that field.

You ARE doing this on a *copy* of your mdb (FE and BE)..... Right???

HTH

 

[Guest]

SteveS

Thank you so much for the Velcro!!!!!

We did it After reading your response, I changed the field name in the
table to ToDoActionID (number) and in the form I changed the code to:

!ToDoCategoryID = Me.cboToDoCategory
!ToDoActionID = Me.cboToDoAction
!Other = Me.txtOther
!ProgressNotes = Me.txtProgressNotes

and it all worked.