Access™ 2007 VBA Programmer's Reference

8.5. Using Multiple Recordsets

You can easily keep track of multiple open recordsets at once. Each one needs to be defined with a Dim statement and opened using OpenRecordset, and they are kept completely separate by Access. Each recordset has its own current record pointer (often called a cursor), end of file (EOF), and beginning of file (BOF) values, and so on.

This technique is necessary to perform the following trick: Copy a parent record and all of its child records into the same tables.

8.5.1. Copying Trees of Parent and Child Records

Here's a task that can stump an Access programmer trying to tackle it for the first time. The problem is as follows: There are two tables, tblPC and tblSpecification. Each (parent) PC has many (child) Specifications. Many PCs have almost identical Specifications, but with slight variations. You need to write some code to copy one PC to another, along with all of its Specifications. The user will then manually update the copied PC's Specifications.

At first, you might think that this seemingly simple problem can be performed using only queries. However, you soon run into a problem—you need to know the key of the newly copied PC so that you can assign the copied Specifications to it.

You can solve the problem by using multiple recordsets. Let's say that you have a continuous form showing a list of PCs and a Copy button at the bottom of the form. The desired functionality is to copy the PC record (with "Copy of " as a prefix of the new PC) and ...