Search code examples
ms-accessautocompletelistboxrecordset

How to populate a ListBox with a ADODB.Recordset (Error 91) To Do Autocompletion in Access

I work on an Access DB and I have to use a Datasource connection to a SQL Server.

To do that I use the ADODB object with :

-ADODB.Connection

-ADODB.Recordset

Code Up-to-date, following an observation of Ian Kenney 

   Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection
   Dim rs As ADODB.Recordset

   cnn.ConnectionString = "driver={SQL Server};provider=SQLOLEDB;server=10.****;uid=****readonly;pwd=****readonly;database=****"
   cnn.Open

  Set rs = cnn.Execute("SELECT [MATRI], [NOMPRE] FROM SCHEME_DB.TABLE WHERE NOMPRE LIKE '*" & Me.Textbox_recherche.Text & "*'")



  Me.Liste_choix.RowSourceType = "Table/List"
  Me.Liste_choix.Recordset = rs

  rs.Close
  cnn.Close

(This code (a part of the code) is a way to do an Autocompletion in Access with a TextBox and a ListBox)

And I have an error 91 when I run this code : "Error 91: Object variable or With block variable not set" .

I don't understand how to resolve this issue.

Thanks in advance.

Solution

  • You told us that code throws Error 91, "Object variable or With block variable not set". Unfortunately, you didn't indicate which line triggers the error. That forces us to guess where the problem lies.

    One issue is here:

    Me.Liste_choix.Recordset = rs

    That attempts an assignment of one object to another. The = sign is sufficient for assignments with simple data types ... ie MyVariable = 2. However you must include the Set keyword with object assignments.

    Set Me.Liste_choix.Recordset = rs

    Although you should make that change, I'm not certain that was the cause of error 91; I would have guessed Access would complain "Invalid use of property" instead.

    The SELECT statement is another problem, but again I'm uncertain whether it contributes to the error you reported. The WHERE clause uses a Like comparison with a pattern which has * as the wild card character. That query might return what you expect when you run it from DAO. But you're using ADO which treats * as just an asterisk character without any special meaning. So that query probably returns no rows when you run it from ADO. Replace * with %.

    As general advice, if your code module does not already include Option Explicit in its Declarations section, add it. Then run Debug->Compile from the VB Editor's main menu. Fix anything the compiler complains about. Make sure you've done those things before any further troubleshooting.