loop thru the recordset in DataSheet view and retrieve the selected records using VB

I have created  a DataSheet in a Subform within a Form using MS Access 2000. I have created a Submit Button that would save the changes made in the DataSheet.

In the click button event, I am trying trying to loop thru the recordsetl in DataSheet view and retrieve the selected records using VB but somehow, I can't find the correct objects.

I was thinking along the lines of form.subform.datasheetview.SelectedRecord but can't seem to find it.
meinhoonaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jefftwilleyCommented:
There are a couple of issues with the your approach. First off, there's a focus issue. If you select records using the standard record selectors, once you leave the form to press your button, your subform no longer has the focus, which in essence means you don't have anything selected anymore. Secondly, the datasheet itself doesn't act like a combo or listbox in that you can select multiple items then use the index to loop through them.

You should approach this with the idea of using a checkbox for each record instead. Users check the records they want, then you can go through and create a recordset based on just the items that are checked. Once you are done with the recordset, you can reset all the checkboxes.
OR
You very well could use a listbox to perform the same operation that you're trying to do with a subform datasheet, but we don't have a lot of information about how ELSE you are using your subform;
J
0
meinhoonaAuthor Commented:
I tried using the checkbox but when I select one check box it selects all the checkboxes in the DataSheet.

The datasheet allows selection by highlighting it and I am not having any focus issue.
0
jefftwilleyCommented:
The checkbox has to be a part of your datasheet's recordsource, so you'll need to add it to one of the tables.

As far as focus goes, you can't add a control to a datasheet, therefor when you click off the datasheet to go to a button or control on another form, main form, etc...the subform does lose focus.

When you select rows in a datasheet by using the record selectors on the left margin, no event fires. So you can't capture an ID or some other record identifier. It's just not part of the datasheet properties.

You can use a click event for the record to capture an ID, but that's not the same thing is it?

J
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

meinhoonaAuthor Commented:
I have created the checkbox and its working fine.

How can I create the recordset based on just the items that are checked. Once I am done with the recordset, I can reset all the checkboxes.

Tks
0
meinhoonaAuthor Commented:
I have created the checkbox and its working fine.

How can I create the recordset based on just the items that are checked. Once I am done with the recordset, I can reset all the checkboxes.

I have Attached the snippet
 Do While oDS
         If Me.RecordSelectors = True Then
            MsgBox ("checked")
     Loop

Open in new window

0
jefftwilleyCommented:
how about something like so. You would put this code on a button or some other click event on your main form.

Dim rs As DAO.Recordset
Set rs = Me.tstSubForm.Form.RecordsetClone   ' Replace tstSubForm with your subform control's name
If rs.EOF Then Exit Sub
rs.MoveFirst
Do Until rs.EOF
If rs!Check Then    '"Check" is what I called my checkbox field. If the box isn't checked, it simply goes to the next record.
'Do whatever you need to do with the checked record here
rs.MoveNext
Loop
rs.Close

J
0
meinhoonaAuthor Commented:
What if I decide to use radio buttons?  Can I  capture the selected record without making it part of the table?

Thanks.
0
jefftwilleyCommented:
No,
If you're not doing anything except displaying the data to the user so that he can select rows, and you're not doing any kind of data entry or modification, why don't you use a listbox?
0
meinhoonaAuthor Commented:
Let me explain  what I am trying to do?
I am creating 2 subforms within a form.  These subforms will consist of tables.
The first table will consist of checkboxes that will allow the user to select multiple rows.
The second table will consist of radio buttons in each row that will allow the user to select only one row.
After making the selection from each table, the user will click submit button that will do internal processing.
How can I trap the selected row for the radio button? Is there a better way to do it? What do u think?
0
jefftwilleyCommented:
ok,
subforms contain a datasheet view of your data from table 1. You've added a checkbox to this table so your user can go down and select one or more.
The second subform the user can only select 1 from table 2.
Your users don't edit the data. They don't add new entries and they don't delete existing records. They just select checkboxes.

So again, don't use datasheets for this. Use Listboxes. You can display the same data. You don't need checkboxes, you can set one to allow multiple-selections, and the other to limit to just a single selection.

You can then put code on your button that will grab the records from the listboxes and use them like you wanted to originally, by looping through the ones that are selected.

Give it a try, you'll find that you are much more satisfied with the results. And if you need help with the code to pull the records from the listboxes, let me know.

J
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
meinhoonaAuthor Commented:
tks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.