I have an access database with a generated key in the format DAA00001X ( not using autonumbering in the main table).
What I want to be able to do is to copy a record from the table to a new record. I cannot seem to use the PasteAppend code for some reason – perhaps it is because of the generated key rather than using the autonumbering.
Is there some way to copy individual data field values across to the new record.
I have some code that finds the record and saves the name to a field called Firstfield. What is held in this field is the correct data but I cannot seem to get it to go into the new record. When I attempted to Update it updated all name fields in the table with the firstfield name value rather than just the current record.
Is someone able to help?
I have attached my sample code:
_____________________________________________
Private Sub cmdCopyRecord_Click()
On Error GoTo Err_cmdCopyRecord_Click
Dim rst As Recordset
Dim fld As Field
Dim StLinkCriteria As String
Dim stDocName As String
Dim strSQL As String
Set rst = Me.RecordsetClone
‘opens the form to assign the delegation no
stDocName = “frmNewDel”
DoCmd.OpenForm stDocName, , , StLinkCriteria
With rst
.MoveLast
Firstfield = Me![name]
Me![name] = .Fields(“name”)
Debug.Print Firstfield
End With
‘the below updates all name fields with the one just chosen – NOT
‘what I want – only want to update the current record with the
‘Firstfield data
strSQL = “UPDATE [Delegation] SET name = ‘” & Firstfield & “‘”
CurrentDb.Execute strSQL
DoCmd.Close acForm, “frmDelProperties”
Exit_cmdCopyRecord_Click:
Exit Sub
Err_cmdCopyRecord_Click:
MsgBox Err.Description
Resume Exit_cmdCopyRecord_Click
End Sub
___________________________________________
Thanks for any help.
