CODEWITHSUNDEEP
ms-accessado
Brad
Brad

Reputation: 12255

Get recordset from selected records on datasheet

I have a subform with a datasheet view. On the parent form I am trying to edit records based on what is selected in the child datasheet. The forms are not linked using master/child fields.

I'm capable of getting the top row that is selected and the number of selected rows using SelTop and SelHeight like below.

Dim rs As New ADODB.Recordset
Set rs = Me.Child_Form.Form.RecordsetClone
If SelHeight > 0 Then
    rs.MoveFirst
    rs.Move SelectionTop - 1
    For i = 1 To SelectionHeight
        If Not rs.EOF Then
            Debug.Print rs("ID")
            rs.MoveNext
        End If
    Next i
End If

What I cannot do is get, say, the 10 records selected on the subform if I have filtered or sorted the form at all. The Filters and Sorts are at the form level and cannot be applied to the underlying recordset.

I've tried creating a new recordset with a query something like this

sql = "Select * from [" & Me.RecordSource & "] where " & Replace(Me.Filter, """", "'") & " order by " & Me.OrderBy

but there are multiple problems here. 1) ADO does not support the IN clause which the form filter will sometimes generate, and 2) the order order is not always the same and predictable.

How can I get a sorted, filtered recordset and find only those records which a user has selected in a datasheet view?

I am connecting to Sql Server with an ADP file.

Upvotes: 0

Views: 2486

Answers (1)

Brad
Brad

Reputation: 12255

I came up with a frustrating solution but it seems to work.

  • added an unbound (to my recordset) checkbox control to my subform.
  • named it chkSelect.
  • made the controlsource =IsChecked(ID)

I have this code running in the subform

Dim selectedRecords As Dictionary


Private Sub chkSelect_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If selectedRecords.Exists(Me("Analytical_ResultID").Value) Then
        selectedRecords.Remove Me("Analytical_ResultID").Value
    Else
        selectedRecords.Add Me("Analytical_ResultID").Value, Me("Analytical_ResultID").Value
    End If
    chkSelect.Requery
End Sub

Private Function IsChecked(Analysis_ResultID As Long) As Boolean
    IsChecked = selectedRecords.Exists(Analysis_ResultID)
End Function

Private Sub Form_Load()
     If selectedRecords Is Nothing Then
        Set selectedRecords = New Dictionary
    End If
End Sub

This works but it's flickery and not ideal. I would much prefer another answer.

Upvotes: 1

Related Questions