Now What About Those Clones

Scientists have cloned mice, sheep, fish, and a variety of plants, and it seems every new generation of politician is a poor quality clone of the ones that preceded them. It's a pity I can't clone my salary check! In terms of recordsets, a clone is a functional replica of the original. There are two clone methods: Clone and RecordsetClone.Clone is a method of the Form object, whereas RecordsetClone is a method of the Recordset object. Both are identical in function, except for the following issues: The Clone method is not supported for ForwardOnly-type recordsets, and you can't set the Filter or Sort properties for recordsets created using the RecordSetClone method.

Microsoft states in the online help that the recordset returned by the Clone method has no current position when it is first created. Calling AbsolutePosition straight after creating the clone indicates that it does; however, I'd be inclined to take Microsoft at their word and not rely on a clone having a current position until after I had executed one of the Move methods.

If you use the Clone or RecordsetClone method to create a copy of the original recordset, all the bookmarks are identical, because, rather than creating a new recordset from scratch, the two clone methods simply point an object variable at the original set of rows. The clone operates on exactly the same data as the original, so any changes made in one are reflected in the other. But (and here's the nifty part), although the data and bookmarks are identical, you can operate on the clone independently of the original; that is, you can change the cursor position in the clone (by using any of the navigation methods) and have no effect on the cursor position in the original. It is for this reason that recordset clones and bookmarks are usually mentioned together.

Let's say you are designing a data entry form for customers. Let's also say you want to allow the users to type in a customer number, and have the form immediately display the record for the customer with that number. There are several ways you can do this, not all of them satisfactory.

You could use DoCmd.ApplyFilter or reopen the form using a filter with DoCmd.OpenForm, but at best, they would return only one record, and your form navigation buttons would then be useless. At worst, they would return an empty recordset. What good is that? The answer is to use a bookmark and recordset clone together. In the AfterUpdate event of your Customer Number text box, you could add the following code:

Private Sub txtEnterCustNo_AfterUpdate() Dim rsClone As DAO.Recordset Dim strCustNo As String

'Remove leading and trailing spaces strCustNo = Trim(Me.txtEnterCustNo)

'Check that the text box contains a value If strCustNo <> "" Then

'Create a clone of the form's recordset Set rsClone = Me.RecordSetClone

'Search for the customer's record rsClone.FindFirst "[CustNo] = & strCustNo &

'The FindFirst method is explained in the following section

'Test the result of the search If rsClone.NoMatch Then

'NoMatch returned True (not a match) DoCmd.Beep

MsgBox "Customer not found."

Else

'NoMatch returned False (found)

'The clone's bookmark is now set to its 'current position, which is the row 'returned by the FindFirst method

'Move the form's current cursor position 'to the one pointed to by the clone's bookmark Me.Bookmark = rsClone.Bookmark End If End If

'Clean up

On Error Resume Next rsClone.Close Set rsClone = Nothing End Sub

Examining the above code, you can see that the real work is done in no more than four lines.

1. Create a clone of the form's recordset. Set rsClone = Me.RecordsetClone

2. Search for the record using the clone (leaves the original recordset untouched). rsClone.FindFirst "[CustNo] = & strCustNo &

3. Check if the search failed. If so, we return a message box to inform the user. If the search passes, we execute line 4.

If rsClone.NoMatch Then

4. Change the form's Bookmark. Me.Bookmark = rsClone.Bookmark

0 0

Post a comment