Create a recordset from a subforms displayed records

[Kryst51]

I have a continuous subform which is bound to a table. The parent form is unbound. I have an unbound checkbox on the parent form. I would like to check that check box, and have it set the checkbox for each record displayed in the subform. (SelectAll/DeselectAll)

The subform adds data only. From the reading that I have done in a book I own and on the internet it seems as if a couple of things need to happen.

1. I need to create a recordset from only the records currently in the subform. I want to use ADO.

2. I then need to loop through each record and perform my If..Then and update SQL. (Currently my SQL only updates the selected record on the form if I run it from the QBE. Which tells me that it is only recognizing the selected record)

I understand, at least in theory, how to do #2. What I don't know how to do is #1. How do I create a recordset of the records on the subform (all newly entered, if the form is closed and reopened it will be blank with no way to find out what was just entered)?

The checkboxes that I want to select or deselect are a field in my underlying table and is only used as a temporary reference so that when I click a button things will happen with the selected records.

 

[pbaldy]

You should be able to set a recordset to the RecordsetClone property of the subform. More info in VBA help and here (note the standard "find a record" code uses this method).

 

[vbaInet]

Here's another idea, if you are not using the Filter property and all your filtering is done by building the sql string for your form's record source then you can:

1. Use an UPDATE statement to set that field to Yes, then
2. Requery the form

Something to this effect:

    CurrentDb.Execute "UPDATE [COLOR=Red][B]MyTable[/B][/COLOR]  SET [COLOR=Blue][B]YesNoField[/B][/COLOR] = Yes WHERE [COLOR=Red][B]MyTable[/B][/COLOR].[B][COLOR=Blue]ID[/COLOR][/B] In  (" & _
                        "SELECT Q.[COLOR=Blue][B]ID[/B][/COLOR]  FROM (" & Left(Me.RecordSource, Len(Me.RecordSource) - 1) & ")  AS Q)", dbfailonerror

    Me.Requery

Well, I suppose if you are using the Filter property you could still incorporate that but you would need to do some more coding for that.

 

[LPurvis]

I'd have to agree with both fellas (i.e. potentially a combination).
Are the records in your subform identifiable by some criteria you've applied - very possibly a foreign key field value?
If so then I'd agree the update statement is the preferable option. (The recordset loaded in the subform would have to be updated iteratively - potentially a heavier process.)

The alternative is to either build up a set of key values of the subform records to build into a SQL update statement (rather than using a FK to identify them) or use the recordset directly to update the records.
This is where Paul's mentioning of the RecordsetClone comes in.
Your interest in making this an ADO recordset wouldn't make much sense unless your form was already bound to an ADO recordset. You really are then creating work for yourself (but at least it's possible - generate an ADO recordset and iteratively fill it with data from the subform's DAO recordset. Possible - but essentially pointless).

Finally, I'd check the purpose of these "checkbox" fields.
Are they fields in the table to which the subform records are bound?
Does it relate to what I'd call a genuine data attribute (as opposed to a application / extended attribute - i.e. a field stuck in there for functionality purposes ;-).

If the latter (and it's just for temporary marking of selection for some other purpose - like a mailmerge or so) then I'd advise against that anyway.
Concurrrency issues alone would prevent it from being robust. And it's less intrusive on the data design to store such a selection field by other, local, means.
There's an example of such in the List Select demo in the page linked to in my sig.

Otherwise - if it's for a genuinely valid data flag - then as you were.

Cheers.

 

[Kryst51]

Thanks all for the responses!

Paul, I will look that up this morning, Thank you for the nudge in the direction I should be going.

vbaInet, I am not doing any filtering, The form is a continuous form where records are entered, so the only records on the screen are ones that are entered at this time only. Once the form is closed and reopened it is ready for more new records to be entered. There will be a different form, for editing at a later time which will filter records.

Leigh, I would rather not use the available fk, as there could be other records with that fk in my table, just not records I want to use, as I only want what is visible on my form.

As far as the functionality of the checkbox, it is more like your first example in your ListSelect db. Yes, the checkboxes are field in the table to which the subform is bound. My goal is to give the user an easier way to manipulate data. The parent form builds an item (it is unbound) from ODBC linked tables. Inventory tags for this item are entered on the subform (The tags are numbers that provide traceability back to the purchase order of the material, the vendor and the Material test report for the particular heat the material was produced under). If there are 5 tags being entered two of those tags could belong to one vendor, while the other three belong to a different vendor, and thus different POs, etc. On the parent form there will be a box, as an example, to enter a PO number, then the user will check the tags that belong to that PO and click a button. The button will then relate the selected tags to the PO, then the user can enter a new PO in the box, select the tags that belong to it etc... The select all/deselect all check box on the parent form are for the cases where every tag being entered has the same information, it is purchased on the same PO/vendor, so rather than having to check EACH records box and clicking the button.

Should I not be trying to do this?

 

[Kryst51]

Your interest in making this an ADO recordset wouldn't make much sense unless your form was already bound to an ADO recordset. You really are then creating work for yourself (but at least it's possible - generate an ADO recordset and iteratively fill it with data from the subform's DAO recordset. Possible - but essentially pointless).

All I meant by that, is if my code requires me to use DAO vs ADO, which I don't even know what I am going to do exactly yet, then I want to use ADO, I thought it would be best to keep my code consistent, and the form already uses some ADO. If it doesn't require either, the GREAT!

 

[LPurvis]

There's nothing wrong with the concept at all.
A "check all" is perfectly reasonable (and common) functionality.
And if this is genuinely data elements then it is fair enough to set.

Ah - what the 'eck.
Check the example download again now.
Very simple demo of iteration.

Cheers.

 

[Kryst51]

There's nothing wrong with the concept at all.
A "check all" is perfectly reasonable (and common) functionality.
And if this is genuinely data elements then it is fair enough to set.

Ah - what the 'eck.
Check the example download again now.
Very simple demo of iteration.

Cheers.

Fantastic, I am glad that I am not thinking in ways that are completely off base. Thanks for the demo, it's wonderful.

I read up on recordset cloning like Paul suggested, so armed with that info and your example and confirmation of a valid method of action, I am off to implement this into my form....

Thanks!

 

[LPurvis]

No worries. Glad you're sorted.