Introduction

Welcome! Copy Records Without Field Names

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Developer Level 48. In this course we will build a function to copy one or more records from any table in Microsoft Access without needing to specify field names. We will discuss issues with duplicating records, the broken duplicate record macro, manually copying records, and problems with append queries that require listing all field names. We will walk through creating a table- and field-agnostic copy records function, explore handling primary keys and unique indexes, use the Indexes collection, and add error handling. Prerequisite topics from previous developer lessons will also be referenced throughout the course.

Navigation

Prev: D48
Next: Lesson 01

Keywords

Access Developer, copy records function, VBA copy record, duplicate record code, avoid specifying field names, unique index fields, detect autonumber, Fields collection, TableDefs, append queries, Indexes collection, is field unique, error handling VBA

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Developer Level 48 brought to you by AccessLearningZone.com. I am your instructor Richard Rost.

In today's class, we are going to build a function that will allow you to copy one or more records from any table without having to specify field names.

I have been contacted many times that trying to duplicate a record is a pain. First of all, there is a macro option for it, and it is broken. It does not work. We will talk about that in lesson one.

Unless you want to manually copy and paste records yourself, it is a pain if you have any fields that are indexed with no duplicates, whether it is your autonumber field or just any other field like email address. If it is indexed with no duplicates, copying it, whether it is with VB code or SQL, is a pain unless you specify the entire list of fields from the table, which no one wants to do. Because if you make changes in the future, if you add fields, change fields, delete fields, you have to update all your code.

So we are going to write a function where you do not have to do that. I like to call it table and field agnostic. It does not care.

This class follows Access Developer 47. I do strongly recommend you take all my classes in order, not because I am trying to sell more lessons, but because they really are designed to follow one after the other. My course is best taken in order: all the beginner lessons, expert, advanced, developer, developer 1.

Obviously, Intro to VBA and some other ones are important. Developer 15 covers error handling, which we are going to do a lot of today. Developer 16 covers recordsets. Definitely take that one. In 24, we cover the Fields collection, which we are going to be using today. In 26, we cover TableDefs. These are all things that I am going to be using a lot today.

Plus, we have a bunch of new stuff coming up today, including the Indexes collection, which is pretty cool.

Do not skip levels because you will miss important information.

This class is recorded with Microsoft Access as part of a 365 subscription, which I strongly recommend. It is currently January 2025. If you are using the retail version, this is roughly equivalent to Access 2024. The lessons covered today should work with any version going back to probably 2007 or so. I am not sure exactly when they broke that macro that I talked about earlier, but this is the version you need.

If you have any questions regarding the material covered in today's class, scroll down to the bottom of the page you are on and post your questions there. Also, take a minute to read through any other questions that have been posted as your answer may have already been answered.

Make sure you slam that red subscribe button so you will get notified if any other questions are posted or answers to your questions. Be sure to check out my Access forums. I have an awesome group of moderators. They rock. I get a lot of questions about anything Access related posted there.

Now let us take a closer look at what is covered in today's class.

In lesson one, we are going to cover the background behind why we want to build the advanced copy records function. We will see how to manually copy a record. That is a beginner task, but we are going to go over it real quick.

Then we are going to learn about the broken duplicate record macro. There is a macro item that just does not work. It used to work years ago. It does not work anymore. Then we will take that macro and convert it over to VBA code. I will show you how to fix it with, of course, one line of code.

Then we will talk about issues with append queries and how you can use them to copy records. But again, you have to know all of the fields that you want to copy individually, which is a pain.

We will review Developer 24's code where I did show you how to loop through the fields, but again, you still have to know the field names. We will talk about all of that in lesson one. That will explain why we are building this function.

In lesson two, we are going to start writing the copy record subroutine. We are going to loop through all of the fields. We are going to avoid the specified primary key, but we are going to specify it first. Then in the next lesson, we will learn how to not specify it.

In lesson three, we are going to get rid of having to specify what the primary key field name is. We are just going to replace it with a basic where condition. We are going to learn about the field attributes and the auto increment field attribute, which basically means this field is an autonumber. So now we can detect the autonumber, the primary key, instead of having to tell what it is.

In lesson four, we are going to start off by writing a function to list all of the table indexes in a table's TableDef so we can see what the index fields are. Then we will determine what the fields in each index are. Knowing this information, we can then write a function to determine if a specific field is unique. So I can say, hey, is the email address unique? In other words, is it indexed with no duplicates? That is what we are going to do in this lesson.

In lesson five, we are going to take that is field unique function and put it inside of our copy records function, which we are going to make a function now, so that we can check all the fields before we copy them and make sure they are not indexed with no duplicates. We are going to add some error handling.

Then we are going to turn copy records into a function and return the value of the newly created record, so we can do stuff with it like open up the form to that new record. Cool stuff.

Quiz

Q1. What is the main purpose of the function being built in this class?
A. To copy one or more records from any table without specifying field names
B. To delete records from multiple tables at once
C. To generate report layouts automatically
D. To create new tables based on queries

Q2. Why is duplicating records in Access often considered a pain?
A. You need to specify the full list of fields, which requires updates if tables change
B. Access does not allow record duplication at all
C. Duplicated records are never allowed in any table
D. The only way is to import and export tables

Q3. What is one major issue with Access's built-in duplicate record macro option?
A. It is broken and does not work in modern versions
B. It only works for reports, not tables
C. It accidentally deletes the original record
D. It only works on linked tables

Q4. What problem is there with copying records when tables have fields indexed with no duplicates?
A. Copying creates errors due to violating unique constraints
B. The copy ignores all the field data
C. Fields are automatically renamed
D. It creates infinite loops in VBA

Q5. Why is manually specifying all field names when copying records problematic?
A. Because any change in the table requires updating the code every time
B. Because Access only supports 10 fields at a time
C. Because only form fields can be specified, not table fields
D. Because SQL does not allow specifying fields

Q6. What important object collections or properties from prior Developer classes are referenced as being important for this lesson?
A. Fields collection, TableDefs, and Indexes collection
B. Form controls and Reports
C. Macros and Queries only
D. Data Macros and XML

Q7. What new feature/topic is introduced in this class?
A. The Indexes collection in TableDefs
B. Data macros
C. SQL Exporting
D. OLE Automation

Q8. What does the improved function being built try to avoid the need for?
A. Knowing or specifying primary key and unique field names in advance
B. Using append queries altogether
C. Directly editing table structures
D. Exporting to Excel

Q9. How will the function detect which fields to avoid copying?
A. By checking for AutoNumber and indexed-with-no-duplicates fields
B. By checking only text fields
C. By ignoring all numeric fields
D. By always skipping the last field

Q10. What will the function return after copying records, as per the design of the final lesson?
A. The value of the newly created record
B. The number of fields in the table
C. The name of the user who copied the record
D. An error log file

Q11. Which prior Developer class specifically covers the Fields collection?
A. Developer 24
B. Developer 10
C. Developer 8
D. Developer 30

Q12. What is a recommended prerequisite for this class to ensure you are prepared?
A. Taking all previous lessons in order, especially those covering VBA, recordsets, TableDefs, and error handling
B. Only watching the most current videos
C. Reading Access documentation online
D. Skipping to the lessons you are interested in

Q13. Why should you avoid skipping levels in these classes?
A. You will miss important information necessary for understanding future lessons
B. It is against Access rules
C. It affects your Office license
D. You may get duplicate records in your database

Q14. For which versions of Access will these lessons most likely work?
A. Access 2007 and newer
B. Only Access 2024
C. Any version of Office
D. Only pre-2010 versions

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from the Access Learning Zone Developer series focuses on building a function in Microsoft Access that lets you copy one or more records from any table without having to specify the field names manually. This is a common request I get, because duplicating records in Access has always been something of a headache, especially when dealing with fields that are indexed with no duplicates allowed, such as autonumber fields or something like a unique email address. If you try to use the built-in macro option for duplicating records, you will quickly find that it's broken and has not worked properly for some time.

Most workarounds like manually copying and pasting records, writing SQL statements, or doing it in VBA require you to know every field name in your table. This gets tedious, since any change to the structure of your table means you have to go back and revise all of your copy logic. Our goal in this class is to write a function that handles all of that for you. It won't care about the table or the field names and will copy records no matter what fields you add or remove in the future.

This lesson builds on concepts covered in Access Developer 47 and earlier classes. I always encourage everyone to take these courses in order. It's not just to sell more lessons. Each lesson builds upon information covered previously, so if you skip ahead, you might miss out on critical information, especially topics like error handling, recordsets, the Fields collection, and TableDefs, which are all key to what we are focusing on today. We will also introduce some new concepts, like working with the Indexes collection, which will help us figure out which fields are unique in your tables.

For these lessons I am using Microsoft Access as part of a Microsoft 365 subscription, which I recommend for the latest features and updates. It's currently January 2025, so if you are using a retail version, Access 2024 is roughly equivalent. However, everything we will cover today should work in versions going back to around Access 2007. The only caveat is with the duplicate record macro option, since it has been broken for some time.

If you have questions during the class, make sure to check the comment section on the page for answers that may have already been posted by other students or myself. Subscribing to updates is also a good idea, as I post frequent answers and updates to student questions there. Also, if you are looking for more community help, my Access forums are an excellent resource, and we have a strong group of moderators helping out.

Now, let's go over what we'll tackle in today's lessons.

In the first lesson, we will discuss why we need a more advanced method for copying records in Access. We will take a quick look at how to manually copy a record, which is something all beginners should be familiar with. After that, we will explore the broken duplicate record macro and demonstrate how it no longer works properly, then walk through converting it into working VBA code with a simple fix.

Next, I will explain the issues and pitfalls of using append queries to copy records, especially since you have to specify all the field names. We will revisit methods from previous lessons, like looping through fields programmatically, but again, we face limitations if the field names change since you have to specify them each time.

In the second lesson, we will begin building our new copy record subroutine. We will set it up to loop through all the fields in a record automatically, skipping the primary key but starting off by specifying which field that is. Shortly afterwards, we will improve on this so you do not even need to specify the primary key by name.

In lesson three, we will further enhance the function so that it can automatically detect which field is the primary key by looking for fields with the auto increment (autonumber) attribute. This way, your copy routine truly becomes independent of field names.

Lesson four will introduce the TableDef's Indexes collection. Here, we will write a helper function that lists all the indexes in a table and identifies which fields belong to each index. Using this, we can determine whether a field is set to be unique, such as an email address field with an index of no duplicates allowed.

In the fifth lesson, we will pull together everything we've written so far by incorporating the "IsFieldUnique" check into our copy records function, adding error handling, and then converting the routine into a function that returns the ID of the newly created record. This is extremely useful if you want to perform additional actions, like opening up a form directly to your new record right after copying it.

You'll find the complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Manually copying records in Access
Reviewing the Duplicate Record macro and its issues
Converting a broken macro to VBA code
Fixing the duplicate record macro with VBA
Using append queries to copy records
Limitations of append queries needing field names
Review of Developer 24's field loop code
Explaining the need for a table-agnostic copy function
Writing a subroutine to loop through fields
Avoiding primary key fields when copying
Detecting autonumber (AutoIncrement) fields
Listing all table indexes with TableDefs
Determining fields in each index
Checking if a field is indexed as unique
Integrating uniqueness checks into copy function
Adding error handling to copy records function
Returning the new record's value from the function

Article

Welcome to this tutorial where we will be tackling one of the more persistent challenges in Microsoft Access: copying one or more records from any table without having to manually specify all the field names every time. This is a common issue, especially for developers who are working with tables that have unique indexes or fields with restrictions like no duplicates. It can be very tedious to maintain code that lists every field, especially as your database evolves and fields get added, changed, or deleted.

First, let us briefly discuss the common hassles in duplicating a record in Access. While there is a macro option loosely called Duplicate Record, many have found it unreliable. At some point, this macro worked, but in more recent versions it can be broken, leaving us to search for a better solution. Even manual methods like copying and pasting records are impractical if you have fields indexed as Unique or as Autonumber. Problems arise because you cannot just blindly copy something like an email address or an autonumber field if those are set to only allow unique values in the table. That means copying using basic SQL append queries or simple VBA code can fail or require you to dig through field settings.

Typically, if you wanted to use an Append query to copy a record, you would have to write something like this:

INSERT INTO TableName (Field1, Field2, Field3) SELECT Field1, Field2, Field3 FROM TableName WHERE ID = 123

This works, but every time your table structure changes, you have to update your code: adding new fields, removing old ones, and restructuring lists throughout your app. This is not only tedious but error-prone. What if you forget to update your code? That is where our solution comes in. We are going to write a function in VBA that is table- and field-agnostic. In other words, it will work regardless of your table's structure and you won't have to specify which fields to copy. The function will be smart enough to skip the primary key and any fields that are indexed as Unique (meaning the index property is set to No Duplicates).

Before we build the solution, let us understand what happens when we try to copy data. Microsoft Access does not like it if you try to insert a value into an Autonumber field or into a field that is set to Unique. Doing so will either fail silently or throw a run-time error. To avoid this, a good approach is to programmatically check each field's properties before copying, which we will do using VBA.

The first step in our solution is to use the Fields collection in Access VBA. This allows us to loop through all the fields in a table without listing them manually. The idea is to use DAO (Data Access Objects) to create a recordset on the source record and then another recordset to add a new record to the same table. For each field, we will check if it is an Autonumber field or if it is indexed as Unique with no duplicates, and then skip those fields when copying values.

Here is how that looks in VBA:

Function CopyRecord(TableName As String, PrimaryKeyField As String, PrimaryKeyValue As Variant) As Variant

Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim fld As DAO.Field
Dim NewPrimaryKey As Variant

Set db = CurrentDb

' Find the record to copy
Set rsSource = db.OpenRecordset("SELECT * FROM [" & TableName & "] WHERE [" & PrimaryKeyField & "] = " & PrimaryKeyValue)
If rsSource.EOF Then
CopyRecord = Null
Exit Function
End If

' Open target recordset for adding new record
Set rsTarget = db.OpenRecordset("SELECT * FROM [" & TableName & "] WHERE 1=0") ' empty set
rsTarget.AddNew

For Each fld In rsSource.Fields
' Skip the Primary Key and any fields that are indexed No Duplicates
If fld.Name <> PrimaryKeyField Then
If Not IsFieldUnique(TableName, fld.Name) Then
rsTarget.Fields(fld.Name).Value = rsSource.Fields(fld.Name).Value
End If
End If
Next fld

rsTarget.Update

' Return the new record's primary key value
rsTarget.Bookmark = rsTarget.LastModified
NewPrimaryKey = rsTarget.Fields(PrimaryKeyField).Value

rsSource.Close
rsTarget.Close
Set rsSource = Nothing
Set rsTarget = Nothing
Set db = Nothing

CopyRecord = NewPrimaryKey

End Function

This function takes the table name, the primary key field name, and the primary key value of the record you want to copy. It opens two recordsets: one to read the existing record and one to insert the new one. The loop through the fields checks two important things: that the field is not the primary key and that it is not a unique field that would cause a duplicate index violation. For this, we use a helper function to determine if a field is unique.

Here is an example of the IsFieldUnique function:

Function IsFieldUnique(TableName As String, FieldName As String) As Boolean

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim i As Integer

Set db = CurrentDb
Set tdf = db.TableDefs(TableName)

For Each idx In tdf.Indexes
If idx.Unique And Not idx.Primary Then
For i = 0 To idx.Fields.Count - 1
If idx.Fields(i).Name = FieldName Then
IsFieldUnique = True
Exit Function
End If
Next i
End If
Next idx

IsFieldUnique = False

End Function

This function loops through all Indexes on a TableDef object and checks if the given field is included in an index that is marked as Unique (No Duplicates) but not the Primary Key index. If it is, the function returns True, which tells our CopyRecord function to avoid copying this field.

With these two functions, you can now easily duplicate any record from any table without worrying about keeping up with field name changes or causing unique constraint errors. When you want to use this function, just call it with something like:

NewID = CopyRecord("Customers", "CustomerID", 17)

This would copy the "Customers" record where CustomerID is 17 and return the new CustomerID for the created record.

It is especially important to include error handling in any production code, so you will want to wrap the logic in a proper error handler. This way, if something unexpected does happen, you can respond gracefully.

By using the TableDefs, Fields, and Indexes collections in DAO, you have a flexible solution that will continue to work as your tables evolve. This saves you valuable time and headaches when maintaining your Access applications.

If you have further questions or want to tweak this function for special cases like copying related child records (for example, order and order details), you can build on this pattern by looping through each level of related data. The key is that with this setup, you do not need to worry about field lists or duplicate index errors anymore.