求助，vb access 递归程序报错

[slysmart]

执行当执行循环时，即第二次执行sql时报错，不知道该如何处理这个问题，各位帮忙，TKS

Function rank(P_No As String)
 Dim sql As String
 Dim LvInct As Integer
 Dim RowInct As Integer
 Dim Inct As Integer
 Dim RInct As Integer
 Dim i As Integer
 Dim Parent_No As String

 LvInct = 0
 RowInct = 0
 
 Parent_No = Format_DrawingNo(P_No) '格式化
 If IsNumeric(Left(P_No, 1)) Or Left(P_No, 1) = ">" Or Left(Right(P_No, 4), 1) = "-" Then '排除没有子节点的情况
 Else
    sql = "SELECT * from atinfor where Drawing_No like '%" & Parent_No & "%' and Row_No='1'and Line_No<>'1'"
    'MsgBox sql
    rs.Open sql, conn, adOpenKeyset, adLockPessimistic '报错处 ："打开对象时，不允许操作"
    If P_No = "" And rs.EOF = True Then '无记录退出e
       Screen.MousePointer = 0
       MsgBox "无记录，请输入正确的DWG_No!!!"
       Exit Function
    End If
    i = 1
    Do While Not rs.EOF
       If P_No = rs.Fields("Value") Or Left(Right(rs.Fields("Value"), 4), 1) = "-" Then  '与父节点同或类似DL0287CBM01-101，不输出
       Else
           MySheet.Cells(RowInct + 1, 1) = LvInct + 1
           MySheet.Cells(RowInct + 1, 2) = rs.Fields("Value")
           'MsgBox rs.Fields("Value")
       End If
    RowInct = RowInct + 1
    rank (rs.Fields("Value"))
    rs.MoveNext
    i = i + 1
    Loop
    LvInct = LvInct + 1
    rs.Close
    Set rs = Nothing
End If
End Function

[worldy]

rs.Open sql, conn, adOpenKeyset, adLockPessimistic 之前加入： if rs.state=adstateopen then rs.close

[slysmart]

引用 1 楼 worldy 的回复:

rs.Open sql, conn, adOpenKeyset, adLockPessimistic 之前加入： if rs.state=adstateopen then rs.close

这个程序主要是父节点可能对应多个子节点，需要执行循环，如果加上这句依然有问题

[worldy]

递归，你rs就不能定义为全局，定义为全局，就不能重复打开rs

[of123]

Function rank(P_No As String)
 Dim sql As String
 Dim LvInct As Integer
 Dim RowInct As Integer
 Dim Inct As Integer
 Dim RInct As Integer
 Dim i As Integer
 Dim Parent_No As String
 Dim rs As ADODB.Recordset '***********************

 LvInct = 0
 RowInct = 0
 
 Parent_No = Format_DrawingNo(P_No) '格式化
 If IsNumeric(Left(P_No, 1)) Or Left(P_No, 1) = ">" Or Left(Right(P_No, 4), 1) = "-" Then '排除没有子节点的情况
 Else
    sql = "SELECT * from atinfor where Drawing_No like '%" & Parent_No & "%' and Row_No='1'and Line_No<>'1'"
    'MsgBox sql
    Set rs = New ADODB.Recordset '***********************
    rs.Open sql, conn, adOpenKeyset, adLockPessimistic '报错处 ："打开对象时，不允许操作"
    If P_No = "" And rs.EOF = True Then '无记录退出e
       Screen.MousePointer = 0
       MsgBox "无记录，请输入正确的DWG_No!!!"
       Exit Function
    End If
    i = 1
    Do While Not rs.EOF
       If P_No = rs.Fields("Value") Or Left(Right(rs.Fields("Value"), 4), 1) = "-" Then  '与父节点同或类似DL0287CBM01-101，不输出
       Else
           MySheet.Cells(RowInct + 1, 1) = LvInct + 1
           MySheet.Cells(RowInct + 1, 2) = rs.Fields("Value")
           'MsgBox rs.Fields("Value")
       End If
    RowInct = RowInct + 1
    rank (rs.Fields("Value"))
    rs.MoveNext
    i = i + 1
    Loop
    LvInct = LvInct + 1
    rs.Close
    Set rs = Nothing
End If
End Function

即使你有公共变量 rs，也被你的函数过程 Set rs = Nothing。与其如此，不如在函数中定义和实例化新的 rs 对象变量。