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.

Copying Trees of Parent and Child Records

This is a task that can stump an Access programmer when 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 also copy over all of its Specification records to the new PC.

Dim

db As Database

Dim

recPC As DAO.Recordset

Dim

recSpecFrom As DAO.Recordset

Dim

recSpecTo As DAO.Recordset

Dim

lngPCKey as Long

Set

db = CurrentDb

If Not IsNull(Me.PCKey) Then

Set recPC = db.OpenRecordset("tblPC", dbOpenDynaset)

'copy the parent record and remember its key

recPC.AddNew

recPC!PCName = "Copy of " & Me!PCName

recPC.Update

recPC.Bookmark = recPC.LastModified

lngPCKey = recPC!PCKey

recPC.Close

Set recPC = Nothing

Set recSpecTo = db.OpenRecordset("tblSpecification", dbOpenDynaset)

Set recSpecFrom = db.OpenRecordset("Select * From tblSpecification

Where PCKey = " & Me!PCKey)

Do While Not recSpecFrom.EOF

recSpecTo.AddNew

recSpecTo!PCKey = lngPCKey 'set to the new parent key

recSpecTo!SpecificationName = recSpecFrom!SpecificationName

recSpecTo!SpecificationQty = recSpecFrom!SpecificationQty

recSpecTo.Update

recSpecFrom.MoveNext

Loop

recSpecTo.Close

Set recSpecTo = Nothing

recSpecFrom.Close

Set recSpecFrom = Nothing

Me.Requery

End

On Error Resume Next Set db = Nothing Exit Sub Error_Handler:

MsgBox "An error has occurred in this application. " _ & "Please contact your technical support person and tell them this information:" _

& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & Err.Description, _

Buttons:=vbCritical, title:="My Application"

Resume Exit_Procedure

Resume

This code has several key things to understand:

□ The variable lngPCKey stores the key of the newly created copy of PC record. It's defined as a Long because our example assumes we are using AutoNumber keys, which are Long Integers.

□ To find the record that was just created, you can use the LastModified property of the recordset. This returns a Bookmark to the record that was added, which you can use to find the new key.

□ Setting the Bookmark property of a recordset positions it to that record.

□ Use Me.Requery to requery the form's recordset so that the newly added record will be shown.

If your backend database is Access (Jet), there's a simpler way to find the AutoNumber key of a newly added record. Anywhere between the .AddNew and the .Update, the AutoNumber key field of the table has already been set, so you can save it into a variable. Using this method, you don't need the Bookmark or LastModified properties. However, use caution here: if your back-end database is SQL Server or another ODBC database, the key won't be set after the .AddNew, and your code won't work. The technique shown here works for both Jet and ODBC databases.

Some developers are tempted to find the key with the highest value immediately after adding a record, thinking that this is a good way to find the new record. Don't do it! There are two problems with this approach. First, it will fail in a multiuser environment if another user just happens to add a record before your code finds the "highest" value. Secondly, you shouldn't write code that depends on an AutoNumber key to have any certain value or sequence. If your database is ever switched over to random keys (which can happen if it is replicated), this technique will fail.

Using Bookmarks and RecordsetClones

In the previous example, there's one annoying behavior. After the form is requeried, the record selector is repositioned to the very top of the list. This is disconcerting and can make it difficult to find the record that was just created.

It's easy to reposition the form to the new record—after all, we already know its key. Just after the Me.Requery, we add some code to find the new record in the just-requeried recordset and reposition the form to it.

To reposition the form, we use a RecordsetClone. This is a strange concept to developers when they first use it. Think of a RecordsetClone as a "twin" of the main recordset that the form is bound to. The nice thing about a RecordsetClone is that it has its own record cursor (with separate FindFirst, EOF, et cetera), but it uses the exact same set of records as the form. The way to synchronize the two "twin" recordsets is using a Bookmark, which is essentially a pointer to an exact record in both recordsets.

If you find a record using a form's RecordsetClone, you can use the Bookmark to instantly reposition the form to that record. Here's the same code, with the extra repositioning section.

Dim

db As Database

Dim

recPC As DAO.Recordset

Dim

recSpecFrom As DAO.Recordset

Dim

recSpecTo As DAO.Recordset

Dim

lngPCKey as Long

Set

db = CurrentDb

If Not IsNull(Me.PCKey) Then

Set recPC = db.OpenRecordset("tblPC", dbOpenDynaset)

'copy the parent record and remember its key

recPC.AddNew

recPC!PCName = "Copy of " & Me!PCName

recPC.Update

recPC.Bookmark = recPC.LastModified

lngPCKey = recPC!PCKey

recPC.Close

Set recPC = Nothing

Set recSpecTo = db.OpenRecordset("tblSpecification", dbOpenDynaset)

Set recSpecFrom = db.OpenRecordset _

("Select * From tblSpecification Where PCKey = " & Me!PCKey)

Do While Not recSpecFrom.EOF

recSpecTo.AddNew

recSpecTo!PCKey = lngPCKey 'set to the new parent key

recSpecTo!SpecificationName = recSpecFrom!SpecificationName

recSpecTo!SpecificationQty = recSpecFrom!SpecificationQty

recSpecTo.Update

recSpecFrom.MoveNext

Loop

recSpecTo.Close

Set recSpecTo = Nothing

recSpecFrom.Close

Set recSpecFrom = Nothing

Me.Requery

'reposition form to new record

Set recPC = Me.RecordsetClone

recPC.FindFirst "PCKey = " & lngPCKey

If Not recPC.EOF Then

Me.Bookmark = recPC.Bookmark

End If

recPC.Close

Set recPC = Nothing

End If

Exit_Procedure:

On Error Resume Next

Set db = Nothing

Exit Sub

Error_Handler:

MsgBox "An error has occurred in this application. " _

& "Please contact your technical support person and tell "

& "them this information:" _

& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _

& Err.Description, _

Buttons:=vbCritical, title:="My Application"

Resume Exit_Procedure

Resume

Notice that you can reuse the recPC recordset object for the repositioning logic, because we are finished using it from earlier in the code, and it has an appropriate name. Of course, we need to close it and set it to Nothing again when we are done.

+2 0

Responses

Post a comment