How to populate list box (or combo!) from Access recordset? 1

[qjd2004]

Hiya,

I've come up with some code that goes and gets a recordset from ms access and I wanted to put the values into a list box or combo box.

This seems to just hang up the program (excel) and crash. Can anyone suggest where I'm going wrong?

Public Sub PopulateListBox(rectype As Integer)
'Populate the list box with the correct set of values

Dim con As ADODB.Connection
Dim thisSQL As String
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection

    'Evaluate the args recieved from calling method
    Select Case rectype
        Case 1:
            thisSQL = "SELECT DISTINCT Client FROM Sender WHERE " _
                    & "RecType = 1"
        Case 2:
            thisSQL = "SELECT DISTICT Client FROM Sender WHERE " _
                    & "RecType = 2"
        Case Else
            thisSQL = ""
    End Select

'Get the connection
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
                     & "D:\Data\LIVE\oppdagelse.mdb"
                     
con.Open
'con.BeginTrans

'Execute query to return as resultset
Set rs = con.Execute(thisSQL)

    'Add unique account refs from the resultset to the listbox in frm1
    Do While Not rs.EOF
        For Each Field In rs.Fields
            UserForm1.UltimateListBox.AddItem
        Next
    Loop

con.Close

End Sub

Is it the repetition control loop that I'm doing wrong, in which case is it the 'Field' reference to rs.Fields or is it the AddItem (does this not apply in this case?) because I couldn't see how to use RowSource because I thought that only applies to worksheet ranges.

Please help!?

 

[PHV]

Have you tried something like this ?
Set rs = con.Execute(thisSQL)
rs.MoveFirst
Do While Not rs.EOF
UserForm1.UltimateListBox.AddItem rs.Fields("Client")
rs.MoveNext
Loop


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[qjd2004]

Hiya,

Why this line

rs.Fields("Client")

I don't understand the syntax?

 

[qjd2004]

Hiya PHV,

thanks that works a treat!

Would you be kind enough to explain 2 other things to me please?

1. How do I refer to the selected item in the listbox in another sub?

I have a control on my form that gets a recordset from access based on the item selected in the list box.

(Can I ask, are the items in the list box being held in memory?)

2. How do I clear the listbox out when I'm finished?

Thanks for your help!

 

[PHV]

In your object browser, take a look at the ListBox class.
Pay attention to things like ListCount, ListIndex, ItemData, ...

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[qjd2004]

Ok will do,

thanks again!