你的MDB不是2000格式,示例
update table_product set product_number="原来的值加上输入的新增值",product_total="原来的值加上输入的新增值" where product_id="输入的产品编号值"->
update table_product set product_number=product_number+你的控件值(me.text2)
,product_total=product_total+你的控件值(me.text3) where product_id=你的控件值(me.text4)
下面的代码不知能否满足你的要求
(注:我将你的窗体上的Text1改为了下拉组合框控件,以便从基础表中选择编号,此组合框的行来源设为:SELECT DISTINCT table_product.product_id, FROM table_product;)
代码的意思为:当你输入的 product_id 是基础表中有的,则直接按你的意思更新数据,如果是基础表中没有的,则会针求你的意见,是否需要新增此编号并将你输入的新增值作为数量及总计值清添加到基础表,如果你同意,则在基础表中新增记录,如果不同意,则什么也不做。
Private Sub Command6_Click()
Dim strSql As String
Dim isAdd As Integer '用来判断是否需要新增新编号
Dim ID As String
If Nz(Me.Text1, "") = "" Then
MsgBox "产品编号未输入,请输入产品编号!"
Exit Sub
End If
If Nz(Me.Text4, "") = "" Then
MsgBox "未输入新增数量,请输入新增数量!"
Exit Sub
End If
ID = Nz(DLookup("[product_id]", "[table_product]", "[product_id]='" & Me.Text1.Value & "'"), "")
If ID <> "" Then
strSql = "UPDATE table_product SET table_product.product_number = [product_number] + " & Me.Text4.Value & " ," & _
"table_product.product_total = [product_total]+" & Me.Text4.Value & _
" where [product_id]='" & Me.Text1.Value & "'"
Else
isAdd = MsgBox("你所输入的 产品编号 在表中不存在,是否新增此编号", vbOKCancel)
If isAdd = vbCancel Then Exit Sub
strSql = "INSERT INTO table_product (product_id, product_number, product_total ) SELECT DISTINCT '" & _
Me.Text1.Value & "' as product_id," & Me.Text4.Value & " as product_number ," _
& Me.Text4.Value & " as product_total From table_product"
End If
Stop
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
Me.Text1.Requery
End Sub