Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1022
  • Last Modified:

Access Subform: Get ID of all Selected Rows

How can I get the ID (a selected field in the subform's record source) of all rows selected by the user in a datasheet style subform?
I know I can get the ID of the first selected record with

Forms!MyForm!MySubform.Form!ID

Is it possible to get the IDs of all selected records or do I need to use a multiselect listbox?
Thx,

MV
0
Michael Vasilevsky
Asked:
Michael Vasilevsky
  • 4
  • 3
  • 2
  • +2
3 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
One way is you can loop through the RecordsetClone of the subform ... and put the ID of each record into a Collection, Array or even write to a temp table.  So, yes ... it can be done.

mx
0
 
Rey Obrero (Capricorn1)Commented:


it will be a lot easier  to use a multiselect listbox
0
 
Michael VasilevskySolutions ArchitectAuthor Commented:
MX, would it be possible to loop through only a RecordsetClone of ONLY the records selected by the user?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
hnasrCommented:
Recordsetclone reflects the filtered/selected records of the form.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What do you have to determine which records are selected?  Meaning ... you can select a contiguous group of records by just dragging the mouse.  

But, to select individual records, you need to have a way to specify a record IS selected, like a check box bound to a 'Rec Selected' field in the underlying table.  So ... given that, then you could get just those selected records from the Clone.  Also, I think you can get the subset of records selected by the contiguous approach.

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
Oh, almost everything's been done before in some guise. :-)
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23170295.html
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What IS your current method of how the User selects records in this subform ?
0
 
Michael VasilevskySolutions ArchitectAuthor Commented:
Yeah by select I mean the user clicks the record selector of one record and then drags the mouse or holds down shift and clicks additional neighboring records.

It's looking like a listbox will definitely be a lot easier but I'll try LPurvis' approach. I do need the user to select records in a subform and then be able to click a button on the main form so I'll try to capture the info in a subform event. There is no OnExit and LostFocus doesn't work. Anyone know what event I could use?
Thx,

MV
0
 
Leigh PurvisDatabase DeveloperCommented:
There are loads of ways to tackle this.
Personally - I find listboxes fine, but a bit "run of the mill" :-).
Your problem with focus on the subform is valid - though the Exit event of the subform control should be adequate. (Alternatively just the MouseUp event of the subform source).
You can pretty easily add selection checkboxes to a subform though for the purposes of choosing records. Either an in-memory recordset or a local table to hold those selections. (I personally hate the makeshift option of including a selection field in the data table itself - to me its poor table design and limited concurrent user control).
The local table method is simplicity itself. Have a look at the "List Select" demo in the examples page link in my profile. (It's the first example in that demo). There are various listbox examples in there too.
(The in-memory recordset is cooler - but much more work for little gain - it's one of the last demos on that page).
You could indeed still fall back on a listbox - or even an ActiveX option which includes checkbox selection by default. (But, again, that would be including unecessary requirements in your application).
Cheers.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And you still have to load the list box and then extract the ID's you want.

"to me its poor table design and limited concurrent user control)."

Guess I would have to disagree.  

Check box field is cheap.  A UserID field solves any concurrency problems if that is an issue.  And a big bonus, you can retain selections the next time the database is loaded, if that were a requirement.  

And of course the check box allows non-contiguous selection and completely eliminate any lost focus issues.  This has worked very well for me over the years and is easy to implement  And of course, this could be a local table or a back end table, depending on the needs.

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
No time as it's almost the time of Eve here, but just to mention quickly that I think you've slightly misunderstood what I was saying regarding a checkbox.
That was just in reference to a boolean field in the data table maintained purely for application selection purposes.
I've no problem with using checkboxes for selection in a form. There are simple alternatives to including the boolean field in the data table to still provide this functionality.
Must dash
Cheers.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now