I have a form with a continuous subform in its header. The subform is bound to the same table as the main form and is used to navigate to records in the main form by means of the following code in its (the subform's) On Current event:
Dim rs As Object
Set rs = Me.Parent.Recordset.Clone
rs.FindFirst "[Field1] = '" & Me!Field1.Value & "'"
If Not rs.EOF Then Me.Parent.Bookmark = rs.Bookmark
The attached database illustrates what I mean.
Now, this is just some code that I picked up from somewhere and I don't really understand what happens when a recordset is cloned. So my question is this: Would it be more efficient to declare 'rs' as a public variable in separate module, and to set it to Me.Parent.Recordset.Clone only in the main form's On Load and After Insert events? My concern is that cloning a large recordset once the database is full of data will be a slow business, so that it would be best to do so as infrequently as possible.