Get Selected Rows from Datasheet Subform 2

[TheInsider]

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!

 

[Remou]

Here is a sample function:

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]

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]

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]

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]

I found thread702-1276011, which explains why my SelHeight was always returning 0.

You're right, Remou. Everything works now. Thank you very much!

 

[zevw]

I modified your code like this

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

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.