CODEWITHSUNDEEP
ms-accessdatasheet
Adam Kowalski
Adam Kowalski

Reputation: 11

Copy from datasheet to ListBox

I have a problem with copying data. I have a subform shown as datasheet with 8 columns and dynamic number of rows. I have a code, that if you select a cell in a datasheet and press button it copies that row to ListBox. But i do not know how to make a loop that will copy all the rows (or couple selected) to the list box. If more than one row is selected only the 1st one is copied.

Private Sub Command_Click()
 Dim item(1 To 8) As String
  With frmPresentationList

    item(1) = ![Col1]
    item(2) = ![Col2]
    item(3) = ![Col3]
    item(4) = ![Col4]
    item(5) = ![Col5]
    item(6) = ![Col6]
    item(7) = ![Co87]
    item(8) = ![Col1]
 End With

 With Me.selectedItems
    .AddItem Join(item, ";")
 End With
End Sub

Thank you!

Upvotes: 0

Views: 92

Answers (1)

June7
June7

Reputation: 21370

Example code. I always name subform/subreport container control different from the object it holds. Subform container control is named ctrRates.

Option Compare Database
Option Explicit

Dim intHeight As Integer    'stores value for number of records selected
Dim intTop As Integer   'stores value for position of first selected record

Private Sub ctrRates_Exit(Cancel As Integer)
    intHeight = Me.ctrRates.Form.SelHeight
    intTop = Me.ctrRates.Form.SelTop
End Sub

Private Sub btnFillListbox_Click()
    Dim N As Integer
    If intHeight > 0 Then
        Me.SelectedItems.RowSource = ""
        With Me.ctrRates.Form.RecordsetClone
            .MoveFirst
            'AbsolutePosition property is 0 based counter so must -1 to set RecordsetClone position
            .AbsolutePosition = intTop - 1
            For N = 1 To intHeight
                Me.SelectedItems.AddItem (!RateID)
                .MoveNext
            Next
        End With
    End If
End Sub

Upvotes: 0

Related Questions