list combobox with array from db

[pape93]

Hi all, I'm getting the error:
"Run-time error '3021': Either BoF or EOF is True, or the current record has been deleted. Requested operation requires a current record"

when i run this code below. I'm trying to populate a combobox with supplier names from a table.

Sub getSupNamFromDB()
   Dim conn As ADODB.Connection
   Dim supName As ADODB.Recordset
   Dim strConn As String
   Dim supArr As Variant
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ThisWorkbook.Path & "\eps.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set supName = New ADODB.Recordset
   supName.Open "SELECT * FROM supplier_table", conn, adOpenDynamic, adLockOptimistic, adCmdText
   If Not (supName.BOF And supName.EOF) Then
 
    For Each Field In supName.Fields
      supArr = supName("sup_name").value
    'txtDescInvItem.RowSource = supArr
    'MsgBox "found"
    supName.MoveNext
    Next
    txtDescInvItem.RowSource = supArr
 
   End If
   supName.Close
   Set supName = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Any help would be appreciated
thanks much.

 

[pcc]

not tested but try this:

Sub getSupNamFromDB()
   Dim conn As ADODB.Connection
   Dim supName As ADODB.Recordset
   Dim strConn As String
   Dim supArr As Variant
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ThisWorkbook.Path & "\eps.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set supName = New ADODB.Recordset
   supName.Open "SELECT * FROM supplier_table", conn, adOpenDynamic, adLockOptimistic, adCmdText
If supname.RecordCount <> 0 Then '******************
   If Not (supName.BOF And supName.EOF) Then
 
    For Each Field In supName.Fields
      supArr = supName("sup_name").value
    'txtDescInvItem.RowSource = supArr
    'MsgBox "found"
    supName.MoveNext
    Next
    txtDescInvItem.RowSource = supArr
 
   End If
 End If   '*******************************
   supName.Close
   Set supName = Nothing
   conn.Close
   Set conn = Nothing
End Sub  

[/code

 

[pcc]

...doh I missed the last ] in the end code tag

 

[pape93]

Thanks for the reply pcc
I tried you suggestion but no luck.. I got the same error message.
I also tried the following and got a different error message:
error: Compile error: For Each may only iterate over a collection object or an array

Sub getSupNamFromDB()
   Dim conn As ADODB.Connection
   Dim supName As ADODB.Recordset
   Dim strConn As String
   Dim supArr As Variant
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ThisWorkbook.Path & "\eps.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set supName = New ADODB.Recordset
   supName.Open "SELECT * FROM supplier_table", conn, adOpenDynamic, adLockOptimistic, adCmdText
   If Not (supName.BOF And supName.EOF) Then
 
    For Each Record In supName.RecordCount
      supArr = supName("sup_name").value
    'txtDescInvItem.RowSource = supArr
    'MsgBox "found"
    supName.MoveNext
    Next
    txtDescInvItem.RowSource = supArr
 
   End If
   supName.Close
   Set supName = Nothing
   conn.Close
   Set conn = Nothing
End Sub

I need help. I'm sure it might be very simple but i'm a newbie at this.
thanks for you help