循环哪里出了错?
----为什么我只能检查出一条记录已存在,并修改。而事实在有多条记录存在
Dim rs1 As New Recordset, rs2 As New Recordset
Dim strTem As String
Private Sub Command1_Click()
Command1.Caption = "数据处理中……"
Dim a, b As Integer
a = 0
b = 0
strTem = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DBQ=" & App.Path & "\前表.xls;DefaultDir=app.path\前表.x;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel 8.0;FILEDSN=app.path\Book1.dsn;MaxBufferSize=2048;MaxScanRows=16;PageTimeout=5;ReadOnly=0;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"""
rs1.Open "SELECT * FROM [预算$]", strTem, adOpenDynamic, adLockOptimistic
strTem = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DBQ=" & App.Path & "\后表.xls;DefaultDir=app.path\后表.x;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel 8.0;FILEDSN=app.path\Book2.dsn;MaxBufferSize=2048;MaxScanRows=16;PageTimeout=5;ReadOnly=0;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"""
rs2.Open "SELECT * FROM [替换物$]", strTem, adOpenDynamic, adLockOptimistic
rs2.MoveNext ''''移到第一条记录(因为该表有两行列标题)
Do Until rs2.EOF
If IsNull(rs2.Fields("商品编号")) Then GoTo Continue
rs1.Find "商品编号 = " & rs2.Fields("商品编号")
If rs1.EOF Then ''''如果EOF则说明没找到相同记录
''''Add
Else
b = b + 1
If a = 0 Then
If MsgBox("商品编号" & rs1.Fields("商品编号") & "已经存在,是否把上月销售数量替换?", vbExclamation + vbYesNo + vbSystemModal, "数量更新") = vbYes Then
Replace
If MsgBox("以后直接替换,不提示", vbExclamation + vbYesNo + vbSystemModal, "提示") = vbYes Then
a = 1
End If
End If
ElseIf a = 1 Then
Replace
End If
End If
Continue:
rs2.MoveNext
Loop
Label1.Caption = b
Label2.Visible = True
If b = 0 Then
MsgBox "商品代码均没重复!没有做任何操作。", vbOKOnly, "OK"
End If
rs2.Close
Command1.Caption = "处理完毕,请关闭"
Command1.Enabled = False
End Sub
Private Sub Replace()
''''替换的提示
For i = 0 To rs2.Fields.Count - 1
rs1.Fields(8) = rs2.Fields(3)
Next i
End Sub
Private Sub Form_Load()
Command1.Caption = "Click me!"
Label1.Caption = ""
Label2.Caption = "个已存在商品已经处理"
Label2.Visible = False
End Sub
----为什么我只能检查出一条记录已存在,并修改。而事实在有多条记录存在。why