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
rst.MoveLast
frm.Bookmark = rst.Bookmark
rst.Close
'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
frm![ipEmployeeNo].SetFocus
End Function

Open in new window

Tammy AllenAdministrative Project CoordinatorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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;
frm.Requery
with frm.Recordsetclone
    .movelast
    frm.bookmark = .bookmark
end with

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
        DBEngine.Workspaces(0).BeginTrans
        Me.Recordset.AddNew        
            For Each fld In Me.Recordset.Fields
            fld = rstflt.Fields(fld.CollectionIndex)
		Next fld
        Me.Recordset.Update
        DBEngine.Workspaces(0).CommitTrans
cmdCLONE_Click_Exit:
    If Not rst Is Nothing Then Set rst = Nothing
    If Not rstflt Is Nothing Then Set rstflt = Nothing
    Me.Refresh
    Exit Sub
    
cmdCLONE_Click_Err:
DBEngine.Workspaces(0).Rollback

Resume cmdCLONE_Click_Exit
End Sub

Open in new window

0
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
0
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.
0
Gustav BrockCIOCommented:
Just replace Me! with Forms!YourFormName!
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.