Duplicate Project Record

[tlallen]

I have a form called fPreproject. I have a code to duplicate a record in this form and paste into a new record with a new primary Key and then end with the cursor in the employee id field of the new record. Unfortunately, it does not work. I keep getting a mismatch error on the field copy code. I do not want to copy all the fields, so I specified each one to copy. I know there is an error in my code, but I cannot find it. I am working in Microsoft Access 2016? Am I making my code too complicated?

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

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  
  If frm.NewRecord = True Then Exit Function
    
  Set rstInsert = frm.RecordsetClone
  Set rstSource = rstInsert.Clone
  With rstSource
    If .RecordCount > 0 Then
      ' Go to the current record.
      .Bookmark = frm.Bookmark
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              Else
                ' Copy field content.
                !EmployeeNo = frm.EmployeeNo
                !ProjType = frm.ProjType
                !ProjDesc = frm.ProjDesc
                !Time = frm.Time
                !DueDate = frm.DueDate
                !Prog = frm.Prog
                !Mod = frm.Mod
                !Improve = frm.Improve
                !Workplan = frm.Workplan
                !WorkUnits = frm.WorkUnits
                !Comment = frm.Comment
         End If
            End With
          Next
        .Update
        ' Go to the new record and sync form.
        .MoveLast
        frm.Bookmark = .Bookmark
        .Close
      End With
    End If
    .Close
  End With

  Set rstInsert = Nothing
  Set rstSource = Nothing


      'place cursor in Employee No Field
frm![ipEmployeeNo].SetFocus

End Function

 

[Andrzejek]

If the record you want to copy (from [tt]MyTable[/tt]) can be the one with AutoNumber =[blue] 123[/blue]

SELECT EmployeeNo, ProjType, ProjDesc, Time, DueDate, Prog, 
Mod, Improve, Workplan, WorkUnits, Comment
FROM MyTable
WHERE MuAutoNumberField = [blue]123[/blue]

You may just do:

INSERT INTO MyTable
(EmployeeNo, ProjType, ProjDesc, Time, DueDate, Prog, 
Mod, Improve, Workplan, WorkUnits, Comment)
SELECT 
EmployeeNo, ProjType, ProjDesc, Time, DueDate, Prog, 
Mod, Improve, Workplan, WorkUnits, Comment
FROM MyTable
WHERE MyAutoNumberField = [blue]123[/blue]

---- Andy

There is a great need for a sarcasm font.

 

[tlallen]

Thank you Andy. The record to duplicate is whatever the current record is the user is on in the Project form, so would replacing the following part of your code:

SELECT 
EmployeeNo, ProjType, ProjDesc, Time, DueDate, Prog, 
Mod, Improve, Workplan, WorkUnits, Comment
FROM MyTable
WHERE MyAutoNumberField = 123

be replaced with:

SELECT 
EmployeeNo, ProjType, ProjDesc, Time, DueDate, Prog, 
Mod, Improve, Workplan, WorkUnits, Comment
FROM  .Bookmark = frm.Bookmark

 

[Andrzejek]

Is the [tt].Bookmark[/tt] the name of your table in your data base? That's where I use [tt]MyTable [/tt]. And what's the name of your AutoNumber field? And what do you use to keep the PK number of the record you want to copy? frm.Bookmark ?

My code is just an example of the idea of how this could be accomplish...


---- Andy

There is a great need for a sarcasm font.

 

[tlallen]

My autonumber field and PK are both the same field named ProjID
The table is called Project, but a user is duplicating the record using a form called Preproject.

 

[Andrzejek]

So your Insert statement in your[tt]
Public Function Sub DupeProjRecord()[/tt]
may look like this:

strSQL = "INSERT INTO Project " & vbNewLine _
& " (EmployeeNo, ProjType, ProjDesc, Time, DueDate, Prog, " & vbNewLine _
& " Mod, Improve, Workplan, WorkUnits, Comment) " & vbNewLine _
& " SELECT " & vbNewLine _
& " EmployeeNo, ProjType, ProjDesc, Time, DueDate, Prog, " & vbNewLine _
& " Mod, Improve, Workplan, WorkUnits, Comment " & vbNewLine _
& " FROM Project " & vbNewLine _
& " WHERE ProjID = " & Preproject.ID

And just execute this statement.

If Preproject.ID holds the value of the PK of the record you want to copy.


---- Andy

There is a great need for a sarcasm font.