Populate text box from listbox results

[cstickman]

Hello fellow Access users,

I have a listbox with 2 columns that populates from a query depending on a drop down that the user selects. I need to capture all the results from the list box which can range from 1 to 100. I thought I had an idea on how to do it, but it does not work. Below is what I came up with, was using the same theory of a recordset to loop through values to transfer the data to the text box, but it only writes one.

Dim i As Long
 
    For i = 0 To Me.lstviewresults.ListCount - 1
 
        Me.txtcombined = Me.lstviewresults.Column(0, i)& Me.lstviewresults.Column(1, i)

            
    Next i

 

[Minty]

Can you not use a recordsetclone of the listbox recordset ?
(I haven't tried!)

 

[cstickman]

Hi Minty,

I have no idea what you mean. Do you have any example? Also, it is an unbound form if that helps.

 

[Minty]

Your listbox has a recordset - so you can make a copy of it , then do stuff with it;
Make a button and add this code to the on click event.

    Dim rs As Recordset
    
    Set rs = Me.[COLOR="Red"]YourListBoxName[/COLOR].Recordset
    
    With rs
        .MoveLast
        MsgBox .RecordCount
    End With
    
    Set rs = Nothing

 

[isladogs]

At the moment you are overwriting the result on each loop so you just get the last record
Change the code to

Dim i As Long
 
    For i = 0 To Me.lstviewresults.ListCount - 1
 
        Me.txtcombined = Me.txtcombined & vbcrlf & Me.lstviewresults.Column(0, i) & Me.lstviewresults.Column(1, i)

            
    Next i

[/QUOTE]

Make your textbox fairly tall and have vertical scroll bar on.

 

[cstickman]

Thanks Minty and ridders!! It worked like a charm!!