为了反映出库存,在入库时将数据同时写了入库表和库存表,库存表中只有用品名称,数量,单位三个字段,当数据存放到库存表时如果库存表中已存在用品名称,则只将数量与原库存中的数量相加,如果没有相关的用品名称,则同时写入用品名称,数量,单位。我已编写了以下代码:Dim rsKC As ADODB.Recordset
Dim i As Integer
Dim StrKC As String
StrKC = "select 办公用品名称,数量 from thingkc where 办公用品名称 like "
StrKC = StrKC & "'%" & Trim(MaskEdBox1(1).Text) & "%'"
Set rsKC = New Recordset
With rsKC
.ActiveConnection = cn
.Open StrKC, , adOpenDynamic, adLockOptimistic, adCmdText
End With
If Trim(MaskEdBox1(1).Text) = Trim(rsKC![办公用品名称]) Then
i = rsKC![数量]
i = i + Val(MaskEdBox1(4).Text)
rsKC![数量] = i
Else
InserKC
End If
Sub InserKC()
Dim Sql As String
Sql = "insert into thingkc (办公用品名称,数量,) values ("
Sql = Sql & "'" & MaskEdBox1(1).Text & "',"
Sql = Sql & "'" & MaskEdBox1(4).Text & "',"
Sql = Sql & "'" & MaskEdBox1(5).Text & "')"
cn.Execute (Sql)
End Sub但是这段程序运行时几个问题:
1.如果库存表中没有相对应的用品名称,程序运行到If Trim(MaskEdBox1(1).Text) = Trim(rsKC![办公用品名称]) 时出错,出错提示是:
BOF或EOF中有一个是真,或当前的记录已被删除,所需的操作要求一个当前记录
2.如果库存表中已存在用品名称,但新入库的数量并没有与原库存中的数量相加。还是保持原库存量。请各位高手看看,该怎样写才能实现我的目的。
Dim i As Integer
Dim StrKC As String
StrKC = "select 办公用品名称,数量 from thingkc where 办公用品名称 like "
StrKC = StrKC & "'%" & Trim(MaskEdBox1(1).Text) & "%'"
Set rsKC = New Recordset
With rsKC
.ActiveConnection = cn
.Open StrKC, , adOpenDynamic, adLockOptimistic, adCmdText
End With
If Trim(MaskEdBox1(1).Text) = Trim(rsKC![办公用品名称]) Then
i = rsKC![数量]
i = i + Val(MaskEdBox1(4).Text)
rsKC![数量] = i
Else
InserKC
End If
Sub InserKC()
Dim Sql As String
Sql = "insert into thingkc (办公用品名称,数量,) values ("
Sql = Sql & "'" & MaskEdBox1(1).Text & "',"
Sql = Sql & "'" & MaskEdBox1(4).Text & "',"
Sql = Sql & "'" & MaskEdBox1(5).Text & "')"
cn.Execute (Sql)
End Sub但是这段程序运行时几个问题:
1.如果库存表中没有相对应的用品名称,程序运行到If Trim(MaskEdBox1(1).Text) = Trim(rsKC![办公用品名称]) 时出错,出错提示是:
BOF或EOF中有一个是真,或当前的记录已被删除,所需的操作要求一个当前记录
2.如果库存表中已存在用品名称,但新入库的数量并没有与原库存中的数量相加。还是保持原库存量。请各位高手看看,该怎样写才能实现我的目的。
Dim i As Integer
Dim StrKC As String
StrKC = "select 办公用品名称,数量 from thingkc where 办公用品名称 = "
StrKC = StrKC & "'" & Trim(MaskEdBox1(1).Text) & "'"
Set rsKC = New Recordset
With rsKC
.ActiveConnection = cn
.Open StrKC, , adOpenDynamic, adLockOptimistic, adCmdText
End With
If(rsKc.EOF() and rskc.BOF()) ThenIf Trim(MaskEdBox1(1).Text) = Trim(rsKC![办公用品名称]) Then
i = rsKC![数量]
i = i + Val(MaskEdBox1(4).Text)
rsKC![数量] = i
Else
InserKC
End If
Sub InserKC()
Dim Sql As String
Sql = "insert into thingkc (办公用品名称,数量,) values ("
Sql = Sql & "'" & MaskEdBox1(1).Text & "',"
Sql = Sql & "'" & MaskEdBox1(4).Text & "',"
Sql = Sql & "'" & MaskEdBox1(5).Text & "')"
cn.Execute (Sql)
End Sub
Dim i As Integer
Dim StrKC As String
StrKC = "select 办公用品名称,数量 from thingkc where 办公用品名称 = "
StrKC = StrKC & "'" & Trim(MaskEdBox1(1).Text) & "'"
Set rsKC = New Recordset
With rsKC
.ActiveConnection = cn
.Open StrKC, , adOpenDynamic, adLockOptimistic, adCmdText
End With
If(rsKc.EOF() and rskc.BOF()) Then
InserKc
else
i = rsKC![数量]
i = i + Val(MaskEdBox1(4).Text)
rsKC![数量] = i
rsKc.Update()
end if
Sub InserKC()
Dim Sql As String
Sql = "insert into thingkc (办公用品名称,数量,) values ("
Sql = Sql & "'" & MaskEdBox1(1).Text & "',"
Sql = Sql & "'" & MaskEdBox1(4).Text & "',"
Sql = Sql & "'" & MaskEdBox1(5).Text & "')"
cn.Execute (Sql)
End Sub
Dim i As Integer
Dim StrKC As String
StrKC = "select 办公用品名称,数量 from thingkc where 办公用品名称 like "
StrKC = StrKC & "'" & Trim(MaskEdBox1(1).Text) & "'"
Set rsKC = New Recordset
With rsKC
.ActiveConnection = cn
.Open StrKC, , adOpenDynamic, adLockOptimistic, adCmdText
if not rskc.eof then
i = rsKC![数量]
i = i + Val(MaskEdBox1(4).Text)
rsKC![数量] = i
rskc.update
Else
InserKC
End If
End With
i = i + Val(MaskEdBox1(4).Text)
rsKC![数量] = i
这段代码写成:
rsKC![数量] =rsKC![数量] +Val(MaskEdBox1(4).Text)
为什么还是空值,数据并没有写入,高手帮忙呀
2、val("" & rskc![数量])
若是字符 "" & rskc![字符型字段]
搞定一切Null值