如何填充一个ListBox用ADODB.Recordset（错误91）要在Access做自动完成 [英] How to populate a ListBox with a ADODB.Recordset (Error 91) To Do Autocompletion in Access

问题描述

我在一个Access数据库工作，我不得不使用一个数据源连接到SQL Server。

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

要做到这一点我使用的ADODB对象：

To do that I use the ADODB object with :

-ADODB.Connection

-ADODB.Connection

-ADODB.Recordset

-ADODB.Recordset

code截至到最新的，下面伊恩·肯尼的

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

（这code（在code的一部分）是一种在一个文本框和一个列表框的访问做一个自动完成）

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

和我有一个错误91，当我运行这个code：错误91：对象变量或With没有设置块变量

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.

先谢谢了。

推荐答案

您告诉我们，code抛出错误91，对象未设置变量或With块变量的。不幸的是，你没有指明哪行触发错误。这迫使我们猜测问题出在哪里。

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.

一个问题是在这里：

Me.Liste_choix.Recordset = rs

这尝试一个对象的另一个的分配。在 = 迹象足以满足简单数据类型分配...即 MyVariable的= 2 。然而，你必须包括分配对象的设置关键字。

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

虽然应该作出这样的改变，我不能肯定这是错误91的原因;我早就猜到访问会抱怨的无效使用的财产的代替。

的 SELECT 语句是另一个问题，但我又不能肯定它是否有助于你报告的错误。在 WHERE 子句使用与具有 * 如同比较C>作为通配符。该查询可能会返回当您从DAO运行它，你所期望的。但是，你正在使用ADO它把 * 刚才没有任何特殊含义星号字符。所以这大概查询不返回行，当您从ADO运行。替换 * 与％。

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 %.

作为一般性的建议，如果你的code模块已经不包含选项显式在其声明部分，添加它。然后运行调试 - >从VB编辑器的主菜单编译。解决任何编译器抱怨。确保你之前的任何进一步的故障排除所做的那些事情。

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.

这篇关于如何填充一个ListBox用ADODB.Recordset（错误91）要在Access做自动完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！