INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Get Selected Rows from Datasheet Subform

Get Selected Rows from Datasheet Subform

(OP)
Hi,

I have a parent form with a subform in datasheet view. I'd like to be able to select multiple rows in the datasheet subform and then change a value on each of those rows. I'm willing to use the continuous forms view instead of datasheet if it makes a difference.

Here's an example of what I'm doing. The parent form has a textbox called txtValue and a command button. When the user clicks the command button, I want to change the value of a particular column in each of the selected rows to the value in the parent form's textbox.

I realize this is a "hack" replacement for a basic SQL Update, but I must do it this way for reasons I won't bother going into here.

Can someone point me towards a VBA code snippet that will let me determine which rows in the datasheet or continuous form are selected?

Thanks!

RE: Get Selected Rows from Datasheet Subform

Here is a sample function:

CODE

Function DisplaySelectedCompanyNames()
     Dim i As Long
     Dim F As Form
     Dim RS As Recordset
     
     ' Get the form and its recordset.
     Set F = Forms![Customers1]
     Set RS = F.RecordsetClone
     
     ' Move to the first record in the recordset.
     RS.MoveFirst
     
     ' Move to the first selected record.
     RS.Move F.SelTop - 1
     
     ' Enumerate the list of selected records presenting
     ' the CompanyName field in a message box.
     For i = 1 To F.SelHeight
       MsgBox RS![CompanyName]
       RS.MoveNext
     Next i
     
  End Function

-- http://wiki.lessthandot.com/index.php/Allow_the_User_to_Select_Multiple_Records_for_Processing

RE: Get Selected Rows from Datasheet Subform

(OP)
Thank you for your reply. I was trying SetTop and SelHeight, but I couldn't seem to get SelHeight to return anything other than 0.

I'll try your code sample tonight, and I'll post my results.

Thanks again

RE: Get Selected Rows from Datasheet Subform

(OP)
I haven't had a chance to run your code yet, but I do have one question:

If a person selects upwards (from bottom to top), will the SelHeight property be negative? If not, how will I know which direction to loop?

For example, if I start my selection at row 6 and stop at row 2, then SelHeight should be 6-2+1=5, but SelTop seems to return 6, which would mean I incorrectly loop from 6 to 10 rather than from 6 to 2. However, if SelHeight returns -5, then I would know to loop from 6 to 2.

I'm just wondering, since my previous tests always returned 0 for SelHeight.

RE: Get Selected Rows from Datasheet Subform

SelTop will always be the record highest up the list regardless of the direction of the selection and SelHeight will be positive.

Please change:
Dim rs As Recordset
To:
Dim rs As DAO.Recordset
 

RE: Get Selected Rows from Datasheet Subform

I modified your code like this

CODE

Function DisplaySelectedCompanyNames()
     Dim i As Long
     Dim F As Form
     Dim RS As Recordset
     
     ' Get the form and its recordset.
     Set F = Forms![AnashForm]
     Set RS = F.RecordsetClone
     
     ' Move to the first record in the recordset.
     RS.MoveFirst
     
     ' Move to the first selected record.
     RS.Move F.SelTop - 1
     
     ' Enumerate the list of selected records presenting
     ' the CompanyName field in a message box.
     For i = 1 To F.SelHeight
        RS.Edit
            If Nz(RS!Active, False) = False Then
                RS!Active = True
            Else
                RS!Active = False
            End If
        RS.Update
        RS.MoveNext
     Next i
  End Function
It works nicely when I highlighted some fields and ran the function manually it populated the fields and set the ACTIVE field to TRUE.

What should I do when I want to run it from a form in Datasheet View, when should I call this function?

It seems that if I put it the Call in the On Current Event Procedure like this

CODE

Private Sub Form_Current()

    Call DisplaySelectedCompanyNames
    
End Sub

it does not see anything selected yet. How can I start the procedure once I seleted all records I want Highlighted. Basicly after the last record was highlighted.
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close