Public Function Dupe Project Record (1 Viewer)

tlgallen · May 16, 2018

Good afternoon,

I am new to Microsoft 2016 and am trying to write a VBA code to duplicate a record and only specific fields to a new record with a new primary key number. The primary key is a field called ProjID. Currently, I know the VBA is incorrect because under my general modules, this is a Public Function and the Me is misused. Does anyone know how I can re-write the code to duplicate a record without over-writing any other records? Here is the current code. I think Me should be defined as frm, but I keep getting "variable not defined" errors when I run the code to test it. I also keep getting errors saying I am not using "end if" and "end with" commands correctly.

PHP:

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 rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  
  If frm.NewRecord = True Then Exit Sub
    
  Set rstInsert = Me.RecordsetClone
  Set rstSource = rstInsert.Clone
  With rstSource
    If .RecordCount > 0 Then
      ' Go to the current record.
      .Bookmark = Me.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 = Me.EmployeeNo
                !ProjType = Me.ProjType
                !ProjDesc = Me.ProjDesc

                !Time = Me.Time
                !DueDate = Me.DueDate
                !Prog = Me.Prog
                !Mod = Me.Mod
                !Improve = Me.Improve
                !Workplan = Me.Workplan
                !WorkUnits = Me.WorkUnits
                !Comment = Me.Comment

              End If
            End With
          Next
        .Update
        ' Go to the new record and sync form.
        .MoveLast
        Me.Bookmark = .Bookmark
        .Close
      End With
    End If
End Function

bastanu · May 16, 2018

Much easier would be to create an append query with all the fields you need (leaving the autonumber ProjID key out) and calling that in your function. When building the query make sure you add a where clause to select the current record on your fProjectData form (WHERE [ProjID] =Forms![fProjectData]![ProjID].

Cheers,
Vlad

tlgallen · May 16, 2018

Thank you Vlad for the quick reply. How would I create a query in VBA? The form is called PreProj, the table is called "Project". The fields to copy to a new record are "Employeeno", "Projtype", etc". The form currently has the VBA I previously posted which sounds like I should delete all the prior DBA and replace with the append query you are suggesting, I think, if I understood correctly?

June7 · May 16, 2018

Why don't you put the code behind the form?

Action SQL in VBA like:

CurrentDb.Execute "INSERT INTO Project (field1, field2, field3) SELECT field1, field2, field3 FROM Project WHERE ID=" & Me!ID

bastanu · May 16, 2018

I was suggesting to build the append query using the query editor and call that in your function, but you can use June7's approach and execute the SQL statement from VBA, just use your own field names.

Cheers,
Vlad

tlgallen · May 17, 2018

Thank you Vlad and June 7. Executing the SQL statement from the VBA sounds like the solution. Why do you think a General Module for Duping a Project Record was possibly created? I noticed throughout our database, on several forms, there is VBA code which calls up the Public Function Dupe Project Record rather than each form having its own append query. I know the Dupe code is present in the form's right click menu.

June7 · May 17, 2018

Your database must not be in a functional state. Certainly can't use Me. (nor Me!) in general module as you already recognized. It's use (as well as RecordsetClone) in the code indicated this procedure was relevant to only one form, hence my question. Calling this procedure as is won't work. If you want to call procedure from multiple forms, then pass the ID as an argument of the procedure.

I would make it a Sub instead of Function, unless you want modify to return a result.

Doubt I would use KeyDown event.

The original code is missing End With line for the first With.

tlgallen · May 18, 2018

Thank You June7,

I did find the following original general module which works as a public function, but it duplicates all fields instead of specific fields. It does currently work, I just need to copy the fields EmployeeNo, ProjType, ProjDesc, Time, DueDate, Prog, Mod, Improve, WorkPlan, WorkUnits, and Comment from the record I am currently on to the new record fields. How could this be modified to choose only certain fields, not all fields?

PHP:

Public Function DupeProjRecord()'called from shortcut menu pProjPopup - 'Dupe''called from [fProjectData]Form KeyDown event - if keycode is'for Ctrl+D or Ctrl+dDim frm As FormSet frm = Forms![fPREPROJECT]![fProjectData].Form'if activated from new (unsaved) record do not continueIf frm.NewRecord Then Exit FunctionOn Error GoTo Err_DupeProjRecord'copy data from the record    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append'place cursor in Employee No Fieldfrm![ipEmployeeNo].SetFocusExit FunctionErr_DupeProjRecord:    MsgBox Err.Description    ResumeEnd Function

bastanu · May 18, 2018

Can't modify that as it is simply using copy and paste to copy the entire record. Why don't you modify the insert into statement that June7 gave you?

Cheers,
Vlad

tlgallen · May 21, 2018

Thank you for the valued guidance. UK access programmers are the BEST. You have both provided me several workable options to resolve the duplicate record issue in my public function

Public Function Dupe Project Record (1 Viewer)

tlgallen

New member

bastanu

AWF VIP

tlgallen

New member

June7

AWF VIP

bastanu

AWF VIP

tlgallen

New member

June7

AWF VIP

tlgallen

New member

bastanu

AWF VIP

tlgallen

New member

Similar threads

Users who are viewing this thread