Duplicate record and amend selected fields

[SilverFlyer]

Hi all

First time poster and long time lurker.

I've been tasked with creating a basic Access database for a colleague. I've worked as a reporting analyst for many years so I have a good understanding of database structure and SQL but this is my first time creating a database from scratch and writing VBA.

In essence, the DB is based on two linked tables; 1. Projects, 2. Project Tasks. The core form contains the Project Details with a subform containing a continuous list of the Project Tasks. Double clicking on the task list opens up the Task details form in a dialog window. This is all working fine.

I have a requirement to create a 'Duplicate Project' button on the Project form. On clicking the button, I need the following steps to perform:
1. open a new blank record
2. copy/load 'ProjectTitle', 'ProjectCategory','ProjectMember' from original record (Record A)
3. set the 'ProjectID' to the next number in the Autonumber sequence (not leave as (New))
4. set the 'PriorProjectID' on Record B from 'ProjectID' from Record A
5. set the 'ProjectStatus' to "Work in Progress"
6. set the 'ProjectStartDate' to Date()

I've tried various approaches but I can only get 90% of the above to work. I can detail the precise approaches I tried and the VBA but I would be interested to hear opinions on the best practice approach to take rather than derail with my possibly clumsy attempts.

Many thanks.

 

[SilverFlyer]

I've worked through a new approach below and gotten it working except for one issue.

Public Sub CommandProjectDuplicate_Click()

'set form controls as Variants
Dim varProjectID As Variant
Dim varProjectTitle As Variant
Dim varProjectSponsor As Variant
Dim varCategory As Variant

varProjectID = ProjectID
varProjectTitle = ProjectTitle
varProjectSponsor = ProjectSponsor
varCategory = Category

'create in new project record
DoCmd.GoToRecord , , acNewRec

'insert variants into new records controls
PriorProjectID = varProjectID
ProjectTitle = varProjectTitle
ProjectSponsor = varProjectSponsor
Category = varCategory
Me.ProjectStatus = "Work in Progress"

End Sub

One issue:
If I create a new duplicate record (Record B based off Record A), if I create a new Task record in the subform (which opens TaskForm as dialog), when I close the TaskForm, it returns to Project A, not Project B.
Any ideas how I could close the TaskForm and return to the Project B?