Explain RecordsetClone

[Autoeng]

On my first big Access project I hired an outside consultant to make the bd perform as I wanted. I have been able to figure out / understand what he did so that on my next project I can do some of the same things. One thing I don't understand is Recordsets and RecordsetClones.

Here is the AfterUpdate code for a combobox that selects a particular record to display on the form.

Private Sub cboECNLookup_AfterUpdate()
    Dim varReturn As Variant

    Me.RecordsetClone.FindFirst "[ECN Number] = '" & Me![cboECNLookup] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark

What is this doing with the RecordsetClone? What are Recordsets and when do you use them?

Autoeng

 

[Mile-O]

One thing I don't understand is Recordsets and RecordsetClones.

What are Recordsets and when do you use them?

Simply put, a Recordset is a set of records. If you open a table you are basically looking at a Recordset. You would use them to manipulate the information stored in your database.

The RecordsetClone is an alternative to using the OpenRecordset method to create a Recordset object. By using it we can create a separate copy of the Recordset that underlies a form and manipulate the form's records independently of the actual form.

It's good for using if you want to move about the form's recordset behind the scenes and don't want your manipulation reflected in the form's Recordset.

A bookmark is a way of identifying each individual row in a recordset that uses a Byte array. Basically it lets us align the Recordset and the RecordsetClone, ensuring that they are in sync.

So, what your code is doing is using the clone of your form's Recordset to find a record and then telling your form's Recordset to go to the result found behind teh scenes.

Me.RecordsetClone.FindFirst "[ECN Number] = '" & Me![cboECNLookup] & "'"

Using the clone and without the user visibly knowing we found out where the first record is where [ECN Number] is equal to the combobox cboECNLookup on your form.

Me.Bookmark = Me.RecordsetClone.Bookmark

Now that the first record has been found with the condition specified in the previous line, the code aligns the current recordset with the first record that the clone found.

Hope this helps...

 

[Autoeng]

Thanks Mile:

The RecordsetClone is an alternative to using the OpenRecordset method to create a Recordset object. By using it we can create a separate copy of the Recordset that underlies a form and manipulate the form's records independently of the actual form.

So, if you don't create a Recordset or RecordsetClone you are working directly within the table? Why is that bad? Do you gain performance with the creation of the Recordset / Clone?

I my case you can select a record via the cbo OR you can enter it manually. If you manually enter it and it doesn't exist you are prompted based on your security rating.

Just trying to figure out how it relates to my project.

Autoeng

 

[Mile-O]

So, if you don't create a Recordset or RecordsetClone you are working directly within the table?

Basically, yes.

Why is that bad? Do you gain performance with the creation of the Recordset / Clone?

I'm self-taught so I don't know why things are good and bad and regarding performance I can't answer that either but by using a clone you can move about in the recordset and mark things off by storing bookmarks as variants which you can immediately move a form's Recordset to.

I think the old DoCmd.GotoRecord , , whichever is slower than using the clone method so maybe that will impact upon performance.

Personally, I use a RecordsetClone for my First, Prev, Next, Last record buttons as, when on the current record on the form I can use the clone to check the records on either side of the current record without visibly going to those records and determining if there are any present - should I be at the first record in the Recordset then I can disable the First and Prev buttons without the user pressing them and getting the annoying "You are at the start of the recordset" message that I hate.

 

[Autoeng]

Very good explaination. Thanks again Mile.

Autoeng