Flexible recordsetclone?

[helmpost]

Folks

From a newbie ...

I've started using the the recordsetclone property and it's very useful. Is there a way however to use if more flexibly? Suppose that you have a set of records in a form and are using the clone ... is it possible to select a reduced set of records by introducing a WHERE clause - aircoding - set rs2=me.recordsetclone (field = some value), a real value or more likely a variable or reference to a control? As far as I can see the recordsetclone is wholly standalone

On the same theme, is it possible to reorder the records in the clone?

Thanks for any thoughts

Helmpost

 

[MarkK]

Suppose that you have a set of records in a form and are using the clone ... is it possible to select a reduced set of records by introducing a WHERE clause - aircoding - set rs2=me.recordsetclone (field = some value)

I would more commonly simply change the recordsource of the form directly. A DAO.Recordset does have an OpenRecordset method, but I've never used it. I would rather just create a new one from scratch. In a form, just set the RecordSource.

On the same theme, is it possible to reorder the records in the clone?

Again, why work with the clone when you can work with the original? Change the RecordSource of the form or create a new Recordset with the ORDER BY you want. SQL is very fast, and how your code works will be more explicit, which is good for maintenance.

My thoughts on the RecordsetClone property of a form is that it used to be the only way to access the recordset driving a form. Since Access 2000, forms expose a Recordset property which gives you direct access to that recordset. For record navigation it's common in Access Help to see them 1) produce a clone, 2) search the clone for a record using FindFirst, 3) get the bookmark of the found record and 4) assign this bookmark to the bookmark property of the form.

With the 'new' Form.Recordset you can do this in one line of code...

Me.Recordset.FindFirst "MyID = " & someID
[COLOR="Green"]'and if you want to get fancy...[/COLOR]
If Me.Recordset.NoMatch then Msgbox someID & " Not found"

Just my 2 cents.
Mark

 

[helmpost]

Hi Mark

Sorry for the dely. Thanks very much for these thoughts. The Help seems to assume that one wants to work on a particular record whereas what I more often do is work with all the records on the form in different ways. In this case, it's a multiple table query. As you say, I could take an sql "copy" of the underlying query and reorder the data using ORDERBY - just need to be careful to get all that punctuation correct! How much easier it would be to "select * from me.recordsetclone ORDERBY or by selecting a subset of the records using the WHERE statement .... much less code to produce and maintain.

Thanks again for your insight.

Best Regards

Helmpost

 

[Ian Mac]

Helmpost,

Mark isn't assuming you want to work with one record.
You can use an event to make a Recordset on the fly, give your criteria and order preference. Once you have done this attach it to your Form.

Set Me.Recordet = rsMyRecordset

Where rsMyRecordset is the recordset object you have just programmatically populated. This is by far the best way to do it, you can cleverly tailor the SQL as you need it.

I actually use ADO as a preference over DAO as you can use disconnected recordsets and UpdateBatch to update the database with several changes/additions/deletions at once. If you have a lot of users it's a good idea.

Cheers,

 

[helmpost]

Thanks Ian and Mark,

The form I'm developing makes use of user selected criteria from a number of combo boxes (it's management statistics so single user). I've used the combobox(es) selections into CASE statements and used filters to apply filters to get the initial recordset. Following some work on this recordset, I then need to get a subset of this initial data to do some more work - select a set of the initial records and reorder the records by one of the recordset fields - not the original.

If I have this right, could I/should I produce a copy of the form recordset (me.recordetclone) and filter that to get my subset and at the same time use an ORDER BY to reorder the original data? Sorry for my ignorance if I have missed the point but as far as I am aware, I cannot use sql to pull the needed data from the form's recordset ... ? I could, I guess use the original recordset to populate a new table and use sql to pull it from there?

Others have advised me to move to ADO ... DAO does seem simpler to understand.

Thanks for your patience

Helmpost

 

[MarkK]

If I have this right, could I/should I produce a copy of the form recordset (me.recordetclone) and filter that to get my subset and at the same time use an ORDER BY to reorder the original data?

No. I've advised directly against that.
But what do you need to do with this recordset anyway? If your end goal is clearer you might get more useful advice about a process.

 

[helmpost]

Thanks Ian,

I understand now that it's quite complex to filter records that have already been filtered. Much of what I'm doing follows this sort of process:-

1. select a set of records from multiple tables based on user criteria selected from combo boxes on the user form. Perhaps wrongly in hindsight (?), I have selected all records as the record source and then used the cobo box selection and case statements and filters to get the initial set of records to be processed.

2. The records are processed against criteria from the combo boxes and a smaller set of the original records are taken forward for further processing. It's here that I need to reorder the records.

3. Since starting this thread and looking through the site, it's occured to me that I could write my subset of records into a table (I only need just a few of the original fields at this point) and then use sql to select and order my data from there. A quick test suggests that this approach is simple to write and hence easier to maintain - although perhaps not quick or technically ideal.

One question. If me.recordset.clone is a copy of the underlying recordset of the form, is there a format for just the underlying form recordset, i.e. me.recordset? I think Ian may have made reference to this when he said
"Set Me.Recordet = rsMyRecordset "

Thanks again to Ian and Mark