Populate ListBox from SQL table

[JagsBrian]

Here is my code so far:

Private Sub UserForm_Initialize()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim i As Integer
cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Forecast;Data Source=sqlaxrpt;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BRIANBECK-LT;Use Encryption for Data=False;Tag with column collation when possible=False"
rst.Open "SELECT [PurchID], [ITEMID], [INVENTSITEID], [REMAINPURCHPHYSICAL], [DELIVERYDATE] FROM PurchasingTable WHERE ItemID = 'K3930903';", cnn, adOpenStatic ', adLockUnspecified
rst.MoveFirst
i = 0
    With Me.ListBox1
    .ColumnCount = 5
    .ColumnWidths = "50;50;50;50;50"
    .Clear
        Do
        .AddItem
        .List(i, 0) = rst![PurchID]
        .List(i, 1) = rst![ItemID]
        .List(i, 2) = rst![INVENTSITEID]
        .List(i, 3) = rst![REMAINPURCHPHYSICAL]
        .List(i, 4) = rst![DELIVERYDATE]
        i = i + 1
        rst.MoveNext
        Loop Until rst.EOF
    End With
End Sub

I am getting an error at .List(I,0) = rst![PurchID] (Run-time error '424' Object Required)

Any ideas what I'm doing wrong?

 

[JagsBrian]

I noticed that when I set the code to Option Explicit, it says that I have a Compile Error: Variable not defined (on the same line that I am getting the error before)

 

[Norie]

Does this work?

        .List(i, 0) = rst.Fields("PurchID").Value

PS You could use GetRows to put the data from the recordset into an array and then use that array to populate the listbox directly without having to loop.