• 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
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

0
Tammy Allen
Asked:
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;
frm.Requery
with frm.Recordsetclone
    .movelast
    frm.bookmark = .bookmark
end with

Open in new window

1
 
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
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