?
Solved

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

Posted on 2008-01-29
11
Medium Priority
?
898 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:meinhoona
  • 6
  • 5
11 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 20774378
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
 

Author Comment

by:meinhoona
ID: 20774521
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 20774657
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:meinhoona
ID: 20778131
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
 

Author Comment

by:meinhoona
ID: 20780129
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 20783398
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
 

Author Comment

by:meinhoona
ID: 20794150
What if I decide to use radio buttons?  Can I  capture the selected record without making it part of the table?

Thanks.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 20797796
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
 

Author Comment

by:meinhoona
ID: 20802873
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
 
LVL 34

Accepted Solution

by:
jefftwilley earned 2000 total points
ID: 20802938
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
 

Author Closing Comment

by:meinhoona
ID: 31426277
tks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Course of the Month3 days, 19 hours left to enroll

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question