查询库存的问题

ypkyh

用sql查询access数据库出现（BOF或EOF有一个是“真”，或当前记录已被删除）的问题。
Sub 出库查询()
' On Error GoTo 100
Dim sht As Worksheet
  Dim Arr, Y
  Dim X As Integer


  Set sht = Sheet1


  Application.EnableEvents = False




cnn.Open "provider=Microsoft.jet.OLEDB.4.0;data source=" & ThisWorkbook.Path & "/Database/" & 2019 & ".mdb"
sql = "select * from ChuKu where  出库单号码 ='" & [l4] & "'"



With rst
   .Open sql, cnn, 1, 1


    Sheet1.Range("e4").Value = IIf(IsNull(.Fields(4)), "", rst.Fields(4)) 'dz

    Sheet1.Range("e3").Value = IIf(IsNull(.Fields(3)), "", rst.Fields(3))  'kh
    Sheet1.Range("l3").Value = Format(.Fields(1), "yyyy-mm-dd") 'rq
    Sheet1.Range("l6").Value = IIf(IsNull(.Fields(6)), "", .Fields(6)) 'bz


    Sheet1.Range("h4").Value = .Fields(5) 'dh
    Sheet1.Range("l4").Value = .Fields(2) 'bh
    Sheet1.Range("i19").Value = .Fields(9) 'yf
    'Sheet1.Range("l19").Value = .Fields(8) 'yf
    Sheet1.Range("k19").Value = .Fields(7) 'fk
X = 6
    Do Until .EOF
        sht.Cells(X, 4) = .Fields(10)
        sht.Cells(X, 5) = .Fields(11)
        sht.Cells(X, 6) = IIf(IsNull(.Fields(12)), "", .Fields(12))
        sht.Cells(X, 7) = IIf(IsNull(.Fields(13)), "", .Fields(13))
        sht.Cells(X, 8) = .Fields(14)
        sht.Cells(X, 9) = .Fields(15)
        sht.Cells(X, 10) = Format(.Fields(16), "#,###0.00")
'        sht.Cells(X, 8) = Format(.Fields(18), "#,###0.00")
'        sht.Cells(X, 10) = .Fields(9)


        X = X + 1
        .MoveNext
    Loop
     
    .Close
'End If
End With


100:


Set cnn = Nothing
Set rst = Nothing
  


End Sub
那位老师帮忙看看问题出在哪

ypkyh

边成 发表于 2019-8-25 22:13
建议用vb稳定性高，vba稳定性太差的

不会VB，看看是不是这个代码有不对的地方