Userform Combo Box Error

[Inactiveb0347]

Hey

I'm trying to get my userform combo box to link to a list I've got in access. The table name is called FoodCat and the field name is CategoryOne. I've been trying to figure out for hours why I keep getting a Run Time Error - Type Mismatch.

Is anyone able to help me with this?


Code:

Dim RecSet As ADODB.Recordset
Dim SQL As String

Private Sub UserForm_Initialize()


SQL = "SELECT * FROM FoodCat"
Set RecSet = dbConnect(SQL)


RecSet.MoveFirst
    With Me.cboFoodCat
        .Clear
        Do
            .AddItem RecSet!CategoryOne [COLOR=#ff0000][B]<---- Run Time Error Happens Here[/B][/COLOR]


            RecSet.MoveNext
        Loop Until RecSet.EOF
    End With


End Sub

Access Connection Function:

Option Explicit
Private Const AccessConnection = "[B]Have removed this so no one can see[/B]"


Public Function dbConnect(SQL As String) As ADODB.Recordset


    'On Error GoTo error_Catch


    Dim RecSet As New ADODB.Recordset
    Dim DBConn As New ADODB.Connection


    Set RecSet = Nothing


    With DBConn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open (AccessConnection)
    End With


        RecSet.ActiveConnection = DBConn
        RecSet.CursorLocation = adUseClient
        RecSet.Open (SQL)
        
    Set RecSet.ActiveConnection = Nothing
    Set dbConnect = RecSet


    Set RecSet = Nothing


    DBConn.Close


error_Catch:


    If Err <> 0 Then
        MsgBox ("Error Message")
    If DBConn.State = 1 Then
        DBConn.Close
    End If
    
    Exit Function
    End If


End Function

 

[Inactiveb0347]

Anyone able to help with this run time error?

 

[Jerry Sullivan]

Try...

Private Sub UserForm_Initialize()
 Dim RecSet As ADODB.Recordset
 Dim SQL As String
 
 SQL = "SELECT [CategoryOne] FROM FoodCat"
 Set RecSet = dbConnect(SQL)

 With Me.cboFoodCat
   If RecSet.RecordCount Then
      .List = Application.Transpose(RecSet.GetRows)
   Else
      MsgBox "No records found"
   End If

 End With
 Set RecSet = Nothing
End Sub