O'Reilly logo

Access™ 2007 VBA Programmer's Reference by Armen Stein, Geoffrey Griffith, Rob Cooper, Teresa Hennig

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required