• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 58
  • Last Modified:

Duplicate Record Command does not generate new Project ID Number with duplicated records- Last row of data ends up overwritten

I have an Access 2003 database which was converted to an Access 2007, then and Access 2013 database.  Since our agency switched to Windows 10, the duplicate record command no longer works.  I think it may be coded in an older Access database VBA code no longer recognized.  Rather than duplicate the entire record and assign a new autonumber to the Project ID field, the command overwrites the last record of the database with the copied record, but leaves the overwritten record Project ID.  Does anyone know how to modify this module to work in Access 2013 on a Windows 10 OS?  Also, in Access 2013, should I be using DAO or ADODB in coding?Snapshot of the fields on the main form.  Project ID is an Autonumber field which should create a new Project ID number in the Duplicate Record Process  

Public Function DupeProjRecord()
'called from shortcut menu pProjPopup - 'Dupe'
'called from [fProjectData]Form KeyDown event - if keycode is
'for Ctrl+D or Ctrl+d
Dim frm As Form
Set frm = Forms![fPREPROJECT]![fProjectData].Form
'if activated from new (unsaved) record do not continue
If frm.NewRecord Then Exit Function
'copy data from the record with focus into temporary Hold fields
Dim HoldEmployeeNo
Dim HoldProjType
Dim HoldProjDesc
Dim HoldTime
Dim HoldDueDate
Dim HoldCompleteDate
Dim HoldProg
Dim HoldMod
Dim HoldImprove
Dim HoldWorkPlan
Dim HoldWorkUnits
Dim HoldComment
HoldEmployeeNo = frm![ipEmployeeNo]
HoldProjType = frm![ipProjtype]
HoldProjDesc = frm![ipProjDesc]
HoldTime = frm![ipTime]
HoldDueDate = frm![ipDueDate]
HoldCompleteDate = frm![ipCompleteDate]
HoldProg = frm![ipProg#]
HoldMod = frm![ipMod#]
HoldImprove = frm![ipImprove]
HoldWorkPlan = frm![ipWorkplan]
HoldWorkUnits = frm![ipWorkUnits]
HoldComment = frm![ipComment]
'move to last record
Dim rst As DAO.Recordset
Set rst = frm.RecordsetClone
frm.Bookmark = rst.Bookmark
'move to 'new' record below 'last' record
SendKeys "{PGDN}", True
'copy data from Hold fields to fields in new record
frm![ipEmployeeNo] = HoldEmployeeNo
frm![ipProjtype] = HoldProjType
frm![ipProjDesc] = HoldProjDesc
frm![ipTime] = HoldTime
frm![ipDueDate] = HoldDueDate
frm![ipCompleteDate] = HoldCompleteDate
frm![ipProg#] = HoldProg
frm![ipMod#] = HoldMod
frm![ipImprove] = HoldImprove
frm![ipWorkplan] = HoldWorkPlan
frm![ipWorkUnits] = HoldWorkUnits
frm![ipComment] = HoldComment
'place cursor in Employee No Field
End Function

Open in new window

Tammy Allen
Tammy Allen
3 Solutions
Dale FyeCommented:
Personally, I prefer to use an append query:
INSERT into yourTable (Field1, Fiel2, ...)
SELECT Field1, Field2, ...
FROM yourTable 
WHERE IDField = CurrentRecordID

Open in new window

Then requery the form and position the pointer on the new record;
with frm.Recordsetclone
    frm.bookmark = .bookmark
end with

Open in new window

John TsioumprisSoftware & Systems EngineerCommented:
Here is a cloning code i had written some time ago...just make the adjustments you want (like fields to omit)
Private Sub cmdClone_Click()
On Error GoTo cmdCLONE_Click_Err
Dim ChoiceToTake As VbMsgBoxResult
Dim rst As Object
Dim rstflt As Recordset
Dim fld As Field
Dim fldCounter As Integer
Dim strDesc, strCenter As Variant
Dim ProcessLookup As Integer
If IsNull(Me.ID) Then Exit Sub
        Set rst = Me.RecordsetClone
        rst.Filter = "ID = " & Me.ID <-- This the filtering criteria
        Set rstflt = rst.OpenRecordset
            For Each fld In Me.Recordset.Fields
            fld = rstflt.Fields(fld.CollectionIndex)
		Next fld
    If Not rst Is Nothing Then Set rst = Nothing
    If Not rstflt Is Nothing Then Set rstflt = Nothing
    Exit Sub

Resume cmdCLONE_Click_Exit
End Sub

Open in new window

Gustav BrockCIOCommented:
You are making it a little too complicated, and the subject has been asked many times.
Here's proven method and code:

Clone Record
Tammy AllenAdministrative Project CoordinatorAuthor Commented:
Thank you so much for the two solutions append vs. clone.  These solutions did work on a private sub, but I have a Public sub which did not seem to like the Me! command.  I read somewhere this has to be used on the specific form, but I still am unable to get the General Module corrected.  At least I am half-way there with a solution for the private sub on the forms.
Gustav BrockCIOCommented:
Just replace Me! with Forms!YourFormName!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now