方法 'Open'作用于对象'_Recordset'时失败

al　发表于：2013-06-18 00:34:32 　

Dim rs As New ADODB.Recordset
Dim str As String
    str = "SELECT DISTINCT CUSQUO.UNIT FROM CUSQUO WHERE CUSQUO.QUONO IN " _
                   & "(SELECT CUSQUOSTATICS.ID FROM CUSQUOSTATICS WHERE CUSQUOSTATICS.CUSTOMER IN " _
                   & "(SELECT CUSTOMER FROM COPOSTATICS WHERE COPOSTATICS.ASNO = '2011121306') ) " _
                   & "AND CUSQUO.MOLDNO ='" & Me.MOLDNO.Column(0) & "' " _
                   & "AND CUSQUO.SIZE ='" & Me.SIZE.Column(0) & "' " _
                   & "AND CUSQUO.COLOR ='" & Me.COLOR.Column(0) & "' " _
                   & "AND CUSQUO.MARK ='" & Me.MARK.Column(0) & "' "

rs.Open str, CurrentProject.Connection, 1, 1
Do Until rs.EOF
Me.COUNIT.DefaultValue = rs(0)
rs.movenext
Loop
rs.Close

End Sub

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

结果显示

运行时刻错误 '-2147467259 (80004005)'
方法 'Open'作用于对象'_Recordset'时失败

请教：什么问题？怎么修改？

Top

殷小宝发表于：2013-06-18 10:41:07

str太复杂了吧，IN嵌套过多。

Top

al 发表于：2013-06-18 12:24:47

那请问怎么修改呢？

Top

cspa 发表于：2013-06-18 22:12:03

Str语句错误，起码语句结尾无＂；＂。一个调试SQL语句的办法是把你写的SQL拷贝到查询中（查询设为SQL模式），把其中的变量先用常量替代，在查询中把语句调通后再拷贝回来改写为含变量形式。

Top

总记录：3篇页次：1/1 9 1 :

Access交流中心

方法 'Open'作用于对象'_Recordset'时失败