How to populate a ListBox using ADODB.Recordset (Error 91) to autocomplete access

Question

I am working on an access database and I need to use a Datasource connection with SQL Server.

For this, I use the ADODB object with:

-ADODB.Connection

-ADODB.Recordset

Code updated after watching Ian Kenny

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 (part of the code) is a way to do autocomplete in Access using TextBox and ListBox)

And I have error 91 when I run this code: "Error 91: object variable or with block variable not set."

I do not understand how to solve this problem.

Thanks in advance.

Answer 1

You told us that the code calls Error 91, "Object variable or With block variable not set". Unfortunately, you did not indicate which line is causing the error. This makes us guess where the problem lies.

One problem:

 Me.Liste_choix.Recordset = rs 

This is an attempt to assign one object to another. The = sign = sufficient for assignments with simple data types ... i.e. MyVariable = 2 . However, you must specify the Set keyword with the purpose of the objects.

 Set Me.Liste_choix.Recordset = rs 

Although you should make this change, I'm not sure if this was the cause of error 91; I would have guessed that Access would complain about "Inappropriate use of property."

The SELECT operation is another problem, but again I am not sure if it contributes to the error message. The WHERE uses a Like comparison with a pattern that has * as a wildcard symbol. This query may return what you expect when you start it from the DAO. But you are using ADO, which treats * as an asterisk character without any special meaning. Thus, the query probably does not return any rows when starting from ADO. Replace * with % .

As a general tip, if your code module does not yet include Option Explicit in the "Ads" section, add it. Then run Debug-> Compile from the VB Editor main menu. Fix everything the compiler complains about. Make sure you do these things before further troubleshooting.

Answer 2

I solved my problem (Error 91). There were three problems: creating ADODB.Connection, * in Select (Thanks to HansUp) and Set for listbox.recordset (Thanks to HansUp again)

I solved the error:

  Private Sub Textbox_recherche_Change() Dim cnn As ADODB.Connection Set cnn = New ADODB.Connection Dim rs As ADODB.Recordset 'A important point to solve the Error 91 is to declare your ADODB.Connection with .Properties like that : (I don't use Windows NT authentification but the SQL Server authentification) With cnn .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 'The second point is to replace the * in the search for the autocompletion by the % Set rs = cnn.Execute("SELECT [NOMPRE] FROM ****.***** WHERE NOMPRE LIKE '%" & Me.Textbox_recherche.Text & "%'") 'You have to declare the RowSourceType of your listbox to "Table/Query" Me.Liste_choix.RowSourceType = "Table/Query" 'And Finally to SET your recordset like that: Set Me.Liste_choix.Recordset = rs rs.Close cnn.Close Set cnn = Nothing Set rs = Nothing End Sub 

Answer 3

You closed the record set and connection before using it

rs here

  rs.Close 

and the connection is closed here

  cnn.Close Me.Liste_choix.RowSourceType = "Table/List" 

rs here

 Me.Liste_choix.Recordset = rs 

Update From docs :

Using the Close method to close the Connection object also closes any active Recordset objects associated with the connection. The command object associated with the Connection object that you close will be saved, but it will no longer be associated with the Connection object; that is, its ActiveConnection property will be set to Nothing. Also, the Command object's parameter set will be cleared of any provider-defined parameters.

Using the Close method to close a Recordset, Record, or Stream object releases related data and any exclusive access that you may have had to data through this particular object. You can later call the Public method to reopen the object with the same or changed attributes. While the Recordset object is closed, invoking any methods that require a live cursor will generate an error.

SQL INJECTION There is also sql injection , creating sql directly from user input.
This question ( prepared MS Access reports ) shows how to use a parameterized query - it might be worth a look.