How to Quickly Duplicate Records in Your Access

Learn how you can create a VBA function that allows you to copy certain data from previous records with just a click of a button. No need to repeatedly type the same things.

Have you ever had an experience where you need to enter a new record in Access that has exactly the same set of values with a previous record except for one field? It can be tiresome, right? This tutorial will teach you how you can create a function that with a click of a button, you can copy certain values of a previous record – saving you time from retyping. Just follow the steps below.

Download Now

If you want to start to use the feature as soon as possible, then you can:

Download the Sample Database with VBA Codes Now

Otherwise, if you want to DIY, you can read the contents below.

Steps to Add a ‘Create Record Copy’ Functionality in Access

  1. Open your form in design view.Open Your Form In Design View
  2. Add a command button. Skip the command button wizard.
  3. Add an On Click event to the button.Add An On Click Event To The Button
  • While in design view, select the button.
  • Open the Property Sheet (ALT + Enter)
  • Go to the Event tab > On Click > …
  • When the Choose Builder menu pops up, select Code Builder.
  • Click OK.
  • This will open the VBA Editor.
    1. Copy the following code and modify it to fit your needs. Use the comments as guide.
    Private Sub Command24_Click()
        Dim currentID As Long
     
        'TO DO: change all instances of 'BookID' with the actual name of your table's ID or primary key
     
        If IsNull(BookID) Then
            MsgBox prompt:="Please select the record to copy first.", buttons:=vbExclamation
            Exit Sub
        End If
     
        currentID = BookID
        DoCmd.GoToRecord record:=acNewRec
     
        'TO DO: set the fields to be copied (those that most likely will have the same values)
        'FORMAT: fieldName = Dlookup("fieldname", "tableName", "primaryKeyField=" & currentID)
     
         Author = DLookup("Author", "Books", "BookID=" & currentID)
         Country = DLookup("Country", "Books", "BookID=" & currentID)
         Language = DLookup("Language", "Books", "BookID=" & currentID)
         Genre = DLookup("Genre", "Books", "BookID=" & currentID)
         Publisher = DLookup("Publisher", "Books", "BookID=" & currentID)
     
         Title.SetFocus ‘TO DO: change 'Title' with name of field that is going to be edited by the user
     
    End Sub
  1. Now test the code if it works.

Code Explained

The code above was used for a bookstore’s database. Because of book series, there are instances where all book details (e.g. Author, Country, Language, etc.) are the same except for one field – the Title. Thus, the need for the code.

The list below shows the program flow.

  1. Check if user has selected a record to copy. An error message will pop up if user clicks on the button right after clicking the Add New Record button – making the ID null.
If IsNull(BookID) Then
     MsgBox prompt:="Please select the record to copy first.", buttons:=vbExclamation
     Exit Sub
End If
  1. Get the ID or primary key of the current record and assign it as value for the currentID This will be used as basis for the values to be copied.
currentID = BookID
  1. Add a new record.
DoCmd.GoToRecord record:=acNewRec
  1. Set values for certain fields base on the currentID This is where data is copied from the selected record.
'FORMAT: fieldName = Dlookup("fieldname", "tableName", "primaryKeyField=" & currentID)

Author = DLookup("Author", "Books", "BookID=" & currentID)
Country = DLookup("Country", "Books", "BookID=" & currentID)
Language = DLookup("Language", "Books", "BookID=" & currentID)
Genre = DLookup("Genre", "Books", "BookID=" & currentID)
Publisher = DLookup("Publisher", "Books", "BookID=" & currentID)
  1. Set focus to field that is most likely to be first edited by the user. This enhances user experience as it eliminates the need for user to manually select the field that still has to be updated.
 Title.SetFocus      ‘TO DO: change 'Title' with name of field that is going to be edited by the user

Repair a Corrupt Database

If for an unfortunate reason, you wake up one day unable to open your database, you can try third-party tools that performs Access repair. It works wonders.

Author Introduction:

Jayme Stack is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and excel recovery software products. For more information visit www.datanumen.com

Leave a Reply

Your email address will not be published.