Possible Problems with swapping a forms Recordset obect?

[darbid]

I have a parent form with a record set (RS1) that results from a users search.

As each record can have children they are shown in a subform, but are not always part of the search results and thus the record set (RS1).

I have just tested copying (RS1) with Me.RecordSet.Clone to a form level DAO Recordset object. eg Set currentFormRecordSet = Me.RecordSet.Clone

Then letting the parent form have a new RecordSource string to show the single record from the subform.

To go back to the (RS1) and current record I am setting Me.RecordSet to the form level variable. eg Set Me.RecordSet = currentFormRecordSet

It seems to work, including showing the current record which was being viewed before leaving it and in the same order.

I am wondering if this is ok. Are there any repercussions of doing this? Or anything I am not thinking about.

 

[Banana]

One potential consequence I can think of but am not sure if it'll apply to .Clone is that while the following is legal:

Set Form1.Recordset = rs
Set Form2.Recordset = rs

If rs is edited via either one of form or by other method, then both form may need to be refreshed so they get the latest value (or they get an error about someone already edited record).

Now, I'm not sure if cloning will have similar effect - I'm sure that this is definitely true when you do a RecordsetClone which basically give you a separate cursor for the same recordset. But with .Clone method? Need to test that out.

 

[darbid]

Thank you Banana, the data in the recordset never changes so this should not be a problem.

I will continue on then. I will write back here if I find any catches or problems. Otherwise people can assume that I have not found any.

 

[darbid]

Just closing off this thread with a fail.

The RecordSet object does not hold its position if you swap. But I think it has to do with the size of the recordset. It seems smaller ones might have worked, but larger ones did not.

 

[Banana]

Why not retrieve the bookmark and set the recordset to that bookmark?

 

[darbid]

Yep that is what I am doing, but for people a long way from the server this takes some time to get to the bookmark again. But from what I can see this is the only option.

 

[Banana]

May I ask what is numbers of records we're talking here, and what is the underlying SQL for the recordset?

 

[darbid]

Sure - I have of course 2 record sets.

1. is a simple Select with about 8 fields from one table with a where that contains varying things. (Local table)
2. is a record set of a linked table with the same select but with a where for just one record based on the primary key.

The number of records I have not tested exactly, but it seemed that if I only had 6 or 7 records it worked ok, but with 3000 records in the record set it would always go back to the first. (I know 3000 is already too many for a form but the user gets that from a search he does and I do not want to limit them too much. I have in fact limited it to 3000.