Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Duplicate record and amend selected fields

Duplicate record and amend selected fields

Duplicate record and amend selected fields

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.

RE: Duplicate record and amend selected fields

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

CODE -->

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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close