combobox in vba?

Question

I have problem I write this code for padding combobox but combobox didn't padding

Private Sub refArt_Enter()
    refArt.Clear
    refArt.AddItem ""
      Dim conn As ADODB.Connection
      Dim rs As ADODB.Recordset
      Dim rq As String
      Set conn = getConn()
      Set rs = New ADODB.Recordset
      rq = "SELECT ref FROM tblArticles;"
      rs.Open rq, conn, adOpenDynamic, adLockBatchOptimistic
      Do While Not rs.EOF
          refArt.AddItem rs("ref").value
          rs.MoveNext
      Loop
End Sub

Please explain what you mean by *padding*. In HTML/CSS padding refers to adding *marginals* inside an element. — , Mar 12 '19 at 12:26
Ok, if you put a breakpoint in the do-while loop, does the code reach that part? — , Mar 12 '19 at 12:30
Can you please submit more info, on what line does this error occur? — , Mar 12 '19 at 13:12
What line gives your the error? do you have a separate sub to get the `ADODB Connection`? The reason I ask is because of this line `Set conn = getConn()` — Zack E, Mar 12 '19 at 13:12
error is here Do While Not rs.EOF refArt.AddItem rs("ref").value rs.MoveNext Loop — HamzaWakrim, Mar 12 '19 at 13:28
So it looks like your source data is not compatible - some (or all) values cannot be added to a combobox .... presuming it crashes in line [ refArt.AddItem rs("ref").value ] Could you be getting a null value ? Check the data source value rs("ref").value ... is it a string, a number what ? — Tin Bum, Mar 12 '19 at 15:08
I would suggest reading through this post here on SO https://stackoverflow.com/questions/967399/how-to-populate-a-combobox-with-a-recordset-using-vba. — Zack E, Mar 12 '19 at 15:46
Have you tried `rs.Fields("ref").value` instead of `rs("ref").value`? — Foxfire And Burns And Burns, Mar 12 '19 at 15:55
i try this but didn't add all data Do refArt.AddItem rs("ref").value rs.MoveNext Loop Until Not rs.EOF — HamzaWakrim, Mar 12 '19 at 16:05

Zack E · Accepted Answer · 2019-03-12T15:58:04.887

I found this on another website and have used the structure for my needs, but if you edit it to your needs it should work. One thing to point out is that you should call the macro on the UserForm_Intialize event and not a CommandButton Event that way it will repopulate the data from the Database each time the userform loads.

   Private Sub UserForm_Initialize()
        On Error GoTo UserForm_Initialize_Err
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        cnn.Open "Set Connection String Here"
        rst.Open "SELECT ref FROM tblArticles"
        rst.MoveFirst
        With Me.ComboBox1
            .Clear
            Do
                .AddItem rst![ref]
                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

combobox in vba?

1 Answers1