problem with recordset, please help...

I want to populate a data combo box using ADO. The problem is that although my database table has data, the recordset does not return them (the record count of the recordset = -1). Therefore the data combo does not get the values. Please, any suggestions?????

I have attached my code below.

PS: I have checked that the connection opens. the table name and columns are also correct

Option Explicit
Private qryAllCategories As String
Public conADO As ADODB.Connection
Public g_AdminDBPath As String


Private Sub dbcboCategory_Load()
If LoadDataComboBox(qryAllCategories, conADO, dbcboCategory) > 1 Then
dbcboCategory.Enabled = True
Else
dbcboCategory.Enabled = False 'disable datacombobox if only one value in it
End If
End Sub

Private Sub Form_Load()
On Error GoTo Error_FormLoad

qryAllCategories = "SELECT CatID, Desc FROM tblOccupCat"
g_AdminDBPath = App.Path & "\Database\admin.mdb"

' open the connection - conADO is defined in module modIni
Set conADO = New ADODB.Connection
conADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & g_AdminDBPath

dbcboCategory_Load

Exit Sub

Error_FormLoad:
MsgBox "An error occured." & vbLf & Err.Number & ": " & Err.Description, vbCritical
Set conADO = Nothing
End Sub



Public Function LoadDataComboBox(MyQry As String, _
myConn As ADODB.Connection, _
ByVal dbcName As DataCombo) As Long

Dim myRS As ADODB.Recordset
'Open recordset
Set myRS = New ADODB.Recordset
myRS.CursorType = adOpenDynamic
myRS.LockType = adLockOptimistic
myRS.Open MyQry, myConn, , , adCmdText

'set the rowsource to nothing incase this datacombobox is being reloaded
Set dbcName.RowSource = Nothing

'Set fields in recordset to datacombobox
dbcName.BoundColumn = myRS.Fields(0).Name
dbcName.ListField = myRS.Fields(1).Name

'Bind recordset to datacombobox
Set dbcName.RowSource = myRS

'return the number of rows that will be in this datacombobox
LoadDataComboBox = myRS.RecordCount

End Function

Hi,


...
Set myRS = New ADODB.Recordset
myRS.CursorLocation=adUseClient
myRS.CursorType = adOpenDynamic
myRS.LockType = adLockOptimistic
myRS.Open MyQry, myConn, , , adCmdText
...


By the way with in your case adOpenDynamic will be switched to adOpenStatic
behind the scenes (no error will occur though)

Another solution is:

Set myRS = New ADODB.Recordset
myRS.CursorType = adOpenKeyset
myRS.LockType = adLockOptimistic
myRS.Open MyQry, myConn, , , adCmdText
...


Shurik.