Microsoft Office Forums - View Single Post

Kaatje · 02-27-2016, 08:08 AM

I created a listbox in a UserForm. The list needs to contain 8 columns and is filled with data from access.

The code seems to work: all 8 columns show, the data is correct and I can use all data in textboxes and in the document.

The only strange thing is that with 3 columns or more in the listbox I get an error message. With 2 columns no error message.

Anyone an idea?

Code:

Private Sub UserForm_Initialize()
 
On Error GoTo UserForm_Initialize_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim i As Integer
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=C:\Users\Laptop Karin\Desktop\Test met adressen.mdb"
rst.Open "SELECT [Bedrijf], [Achternaam],[Voorletters], [Adres], [ID] FROM NAW_Query ORDER BY [Bedrijf];", _
  cnn, adOpenStatic
 
rst.MoveFirst
i = 0
With Me.ListBox1
  .Clear
  Do
    .AddItem
      .List(i, 0) = rst![Bedrijf]
      .List(i, 1) = rst![Achternaam]
      .List(i, 2) = rst![Voorletters]
      .List(i, 3) = rst![Adres]
      .List(i, 4) = rst![ID]
      i = i + 1
      rst.MoveNext
  Loop Until rst.EOF
End With
 
UserForm_Initialize_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
 
End Sub