Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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 from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Get Selected Rows from Datasheet SubformHelpful Member! 

TheInsider (Programmer) (OP)
10 Sep 08 16:16
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!
Helpful Member!  Remou (TechnicalUser)
10 Sep 08 16:27
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
TheInsider (Programmer) (OP)
10 Sep 08 17:24
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
TheInsider (Programmer) (OP)
10 Sep 08 17:53
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.
Remou (TechnicalUser)
10 Sep 08 18:08
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
 
TheInsider (Programmer) (OP)
10 Sep 08 18:16
I found thread702-1276011: Subform SelHeight and SelWidth Always Zero, which explains why my SelHeight was always returning 0.

You're right, Remou. Everything works now. Thank you very much!
zevw (MIS)
25 Sep 08 18:08
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.
 

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!

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