Office中国论坛/Access中国论坛

标题: 那位高手帮帮忙条件更新表和添加代码错误问题 [打印本页]

作者: 文棣 时间: 2011-6-27 14:50
标题: 那位高手帮帮忙条件更新表和添加代码错误问题
Dim i, j As Integer
Dim stemp As String
Dim Rs As ADODB.Recordset
Dim Rs1 As ADODB.Recordset
Set Rs = New ADODB.Recordset
Set Rs1 = New ADODB.Recordset

stemp = "Select * From 库存明细"
Rs.Open stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

stemp = "Select * From 库存"
Rs1.Open stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If Rs.RecordCount < 1 Or Rs1.RecordCount < 1 Then

   Exit Sub
End If
For i = 1 To Rs1.RecordCount
   Rs1.MoveFirst

   Rs.MoveFirst
   For j = 1 To Rs.RecordCount
   If Rs1("条码号") = Rs("条码号") And Rs1("编号") = Rs("商品编号") Then
   Rs("数量") = Rs("数量") + Rs1("数量")
   Rs("金额") = Rs("数量") * Rs("单价")
   Rs.Update
   Rs1.Delete 1
   Rs1.Update
   Exit For
   Me.库存_子窗体2.Requery

   Else
      Rs.MoveNext
   End If
   Next j

   If j = Rs.RecordCount Then
      Rs("产品名称") = Rs1("产品名称")
      Rs("日期") = Rs1("日期")
      Rs("型号规格") = Rs1("型号规格")
      Rs("数量") = Rs1("数量")
      Rs("单价") = Rs1("零售价")
      Rs("金额") = Rs1("零售金额")
      Rs("仓库号") = Rs1("仓库号")
      Rs("类别") = Rs1("类别")
      Rs("商品编号") = Rs1("编号")
      Rs("毛利率") = Rs1("毛利率")
      Rs("条码号") = Rs1("条码号")
      Rs.Update
      Rs1.Delete 1
      Rs1.Update
      Exit For
      Exit For
      Me.库存_子窗体2.Requery

      Rs1.MoveNext
      End If
      Next i
      Rs.Close

Set Rs = Nothing
Set Rs1 = Nothing

作者: todaynew 时间: 2011-6-27 15:08

文棣发表于 2011-6-27 14:50
Dim i, j As Integer
Dim stemp As String
Dim Rs As ADODB.Recordset

看不懂想干点什么

作者: 文棣 时间: 2011-6-27 15:12
就是通个一个按键从库存表更新库存明细表如果库存明细表有对应记录则执行更新没有则追加该条记录更新可以但追加就不晓得如何做了

作者: 文棣 时间: 2011-6-27 15:14
回复 todaynew 的帖子

其实就是如何达到从库存表更新库存明细表有相同的记录更新数量无记录则执行追加不管是更新还是追加执行后删除该条记录

作者: 文棣 时间: 2011-6-27 15:19
回复 todaynew 的帖子

对for循环使用较少

作者: todaynew 时间: 2011-6-27 15:35
本帖最后由 todaynew 于 2011-6-27 17:49 编辑

文棣发表于 2011-6-27 15:19
回复 todaynew 的帖子

对for循环使用较少

Dim i As Integer
Dim stemp As String
Dim Rs As New ADODB.Recordset
Dim Rs1 As New ADODB.Recordset
stemp = "Select * From 库存"
Rs1.Open stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Rs1.RecordCount = 0 Then Exit Sub
'更新库存明细表
For i = 1 To Rs1.RecordCount
   stemp = "Select * From 库存明细 where 条码号='" & Rs1("条码号").Value & "' and 商品编号='" & Rs1("商品编号").Value & "'"
   Rs.Open stemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   If Rs.RecordCount = 0 Then
         Rs.AddNew  '无对应记录则追加
   End If
   Rs("产品名称").Value = Rs1("产品名称").Value
   Rs("日期").Value = Rs1("日期").Value
   Rs("型号规格").Value = Rs1("型号规格").Value
   Rs("数量").Value = Nz(Rs("数量").Value, 0) + Rs1("数量").Value
   Rs("单价").Value = Rs1("零售价").Value
   Rs("金额").Value = Rs("数量").Value * Rs("单价").Value
   Rs("仓库号").Value = Rs1("仓库号").Value
   Rs("类别").Value = Rs1("类别").Value
   Rs("商品编号").Value = Rs1("编号").Value
   Rs("毛利率").Value = Rs1("毛利率").Value
   Rs("条码号").Value = Rs1("条码号").Value
   Rs.Update
   Rs1.MoveNext
   Rs.Close
Next

'删除库存表中记录
for i=1 to Rs1.RecordCount
rs1.delete
rs1.update
rs1.movenext
next

Me.库存_子窗体2.Requery
Rs1.Close
Set Rs = Nothing
Set Rs1 = Nothing

作者: 文棣 时间: 2011-6-27 18:13
回复 todaynew 的帖子

删除不行提求EOF有一个为真

作者: 文棣 时间: 2011-6-27 18:15
回复 todaynew 的帖子

BOF或EOF有一个是真，或当前记录已被删除，所需操作要求一个当前记录

作者: todaynew 时间: 2011-6-28 08:19

文棣发表于 2011-6-27 18:15
回复 todaynew 的帖子

BOF或EOF有一个是真，或当前记录已被删除，所需操作要求一个当前记录

呵呵，漏了一条语句。在删除之前加一句
rs1.movefirst

作者: 文棣 时间: 2011-6-28 08:52
回复 todaynew 的帖子

感谢了我找了好久原因是我还是没有指向第一条记录呵呵谢谢你

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)