CODEWITHSUNDEEP
ms-accessvbams-access-2010adodb
Maxs728
Maxs728

Reputation: 621

VBA The object you entered is not a valid recordset property

Ok so what I am trying to do is assign a ADODB.Recordset to a ListBox in VBA. I am getting the Error: "The object you entered is not a valid recordset property".. What am I doing wrong here?

Note: adoConn is valid and set somewhere else in the code.

Private Sub Form_Load()
' Error Management
On Error GoTo ErrHandler:

Dim adoRS As New ADODB.Recordset
Dim sqlStmt As String

' Create the SQL statement
sqlStmnt = "SELECT GroupName FROM tblGroups"

' Execute the statement
adoRS.Open sqlStmnt, adoConn

' Add items to the lstGroups
If (adoRS.RecordCount <> 0) Then
    Set lstGroups.Recordset = adoRS
End If

' Clean up
adoRS.Close
Set adoRS = Nothing
Exit Sub

ErrHandler:
' Clean up
If (adoRS.State = adStateOpen) Then
    adoRS.Close
End If

Set adoRS = Nothing

If Err <> 0 Then
    MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub

This is how ADO is opened

Public Sub openConnection()
' The path to the database
Dim strDBPath As String
strDBPath = "C:\Users\Vincent\****\****\"

' The database name to connect to
Dim strDBName As String
strDBName = "Permissions_be.accdb"

' Full path to the database
Dim strDBFull As String
strDB = strDBPath & "\" & strDBName

' Instantiate an ADO object
Set adoConn = New ADODB.Connection

' Connect to database
With adoConn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Mode = adModeShareDenyNone
    .Open (strDBPath & strDBName)
End With
End Sub

Update So if anyone comes across this problem on Access 2010/13. - Set the list box to a values list. - Then on the VBA side loop through the recordset

' Add items to the lstGroups
If (adoRS.RecordCount <> 0) Then

    Do While Not adoRS.EOF
        ' This is how to add two columns to one listbox if you need only
        ' one then put only the (adoRS.Fields(0))
        lstGroups.AddItem (adoRS.Fields(0) & ";" & adoRS.Fields(1))
        adoRS.MoveNext
    Loop
    lstGroups.Requery
End If

Upvotes: 0

Views: 1039

Answers (2)

user5412293
user5412293

Reputation:

The problem is this line 

Set lstGroups.Recordset = adoRS

If you look at the documentation you will notice that the is not a "Recordset" property in the list.

There is a property of the ListBox called List and it accepts a variant array. You can get the values from the record set and put them into an array and then onto the List.

Something like 

' Add items to the lstGroups
If (adoRS.RecordCount <> 0) Then
    lstGroups.List= adoRS.GetRows
End If

I did not test any of this but it might get you in the right track.

Also if you do not get it to work with the list this link has a very good example on how to add them one by one enter link description here

Upvotes: 0

Muhammad Rizal Abu Hassan
Muhammad Rizal Abu Hassan

Reputation: 1

how do you set you adoConn ? should be something like below :-

Dim cnn As ADODB.Connection 
Set adoConn= New ADODB.Connection

 With adoConn
     .Provider = "Microsoft.Access.OLEDB.10.0"
     .Properties("Data Provider").Value = "SQLOLEDB"
     .Properties("Data Source").Value = "10.******"
     .Properties("User ID").Value = "*****readonly"
     .Properties("Password").Value = "*****readonly"
     .Open
 End With

Upvotes: 0

Related Questions