Link to home
Start Free TrialLog in
Avatar of Tammy Allen
Tammy AllenFlag for United States of America

asked on

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?User generated image  


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

ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Tammy Allen

ASKER

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.
Just replace Me! with Forms!YourFormName!