|
- Sub 修改()
- Dim i As Long
- Dim j As Long
- Dim x As Long
- Dim y As Long
- Dim T, t1
- Dim db1 As Database '声明数据库变量
- Dim RS1 As Recordset '声明指针
- T = Timer
- Set db1 = OpenDatabase(ThisWorkbook.Path & "\产品出入库数据库.mdb")
- Set RS1 = db1.OpenRecordset(Name:="出入库数据表", Type:=dbOpenDynaset)
- For x = 6 To 12
- If Sheet9.Cells(x, 2) <> "" Then
- y = y + 1
- End If
- Next x '新记录条数
- Do
- RS1.FindNext "编号='" & Sheet9.Range("K4").Value & "'"
- i = i + 1
- Loop Until RS1.NoMatch = True
- If y <> i Then
- MsgBox "表单的数据行数与原数据库内的行数不一致不能修改!"
- RS1.Close '结束
- db1.Close
- Exit Sub
- Else
- With RS1
- .MoveFirst '将记录指针移至第一条
- .Filter = "编号='" & Sheet9.Range("K4") & "'" 'MXG825 加这一句,先找到第一条记录
- For i = 6 To 12
- If Sheet9.Cells(i, 2) <> "" Then
-
- .Edit '编辑修改
- .Fields("客户部门编码") = Sheet9.Range("B4").Value
- .Fields("客户部门名称") = Sheet9.Range("C4").Value
- .Fields("日期") = Sheet9.Range("I4").Value
- .Fields("编号") = Sheet9.Range("K4").Value
- .Fields("出入库单号") = Sheet9.Range("M4").Value
- .Fields("出入库类型编码") = Sheet9.Range("N4").Value
- .Fields("出入库类型名称") = Sheet9.Range("O4").Value
- .Fields("单据类型") = Sheet9.Range("E2").Value
- .Fields("地址") = Sheet9.Range("D4").Value
- .Fields("企业性质") = Sheet9.Range("E4").Value
- .Fields("大区负责人") = Sheet9.Range("F4").Value
- .Fields("分区负责人") = Sheet9.Range("G4").Value
- .Fields("业务员") = Sheet9.Range("H4").Value
- .Fields("录入员姓名") = Sheet9.Range("F13").Value
- .Fields("复核员姓名") = Sheet9.Range("D13").Value
- .Fields("产品编码") = Sheet9.Cells(i, 2)
- .Fields("产品名称") = Sheet9.Cells(i, 3)
- .Fields("规格") = Sheet9.Cells(i, 4)
- .Fields("计量单位") = Sheet9.Cells(i, 5)
- .Fields("单价") = Sheet9.Cells(i, 6)
- .Fields("成本单价") = Sheet9.Cells(i, 7)
- .Fields("入库包数") = Sheet9.Cells(i, 8)
- .Fields("入库数量") = Sheet9.Cells(i, 9)
- .Fields("入库成本") = Sheet9.Cells(i, 10)
- .Fields("出库包数") = Sheet9.Cells(i, 11)
- .Fields("其中送包数") = Sheet9.Cells(i, 12)
- .Fields("出库数量") = Sheet9.Cells(i, 13)
- .Fields("销售金额") = Sheet9.Cells(i, 14)
- .Fields("出库成本") = Sheet9.Cells(i, 15)
- .Update '把记录保存到数据库
- .FindNext "编号='" & Sheet9.Range("K4") & "'" '这句换到后面
- End If
- Next i
- End With
- MsgBox "数据修改成功"
- End If
- t1 = Timer - T ' 计算总时间
- RS1.Close
- db1.Close
- End Sub
复制代码 |
|