产品表:
ProductName TEXT
ComponentName TEXT
ComponentQuantity CURRENCY(允许小数) 库存表:
ComponentName TEXT
ComponentQuantity CURRENCY(允许小数) 查库:set rs1 = cnn.execute("select * from 产品表 where ProductName='" & txtProduct & "'")if rs1.eof then msgbox "无此产品":exit subdo until rs1.eof
set rs2 = cnn.execute(("select * from 库存表 where ComponentName='" & rs1!ComponentName & "'")
if rs2.eof then
strMsg1 = strMsg1 & rs1!ComponentName & vbcrlf
else if rs2!ComponentQuantity < rs1!ComponentQuantity then
strMsg1 = strMsg2 & rs1!ComponentName & ": " & rs2!ComponentQuantity & vbcrlf
end if
rs1.movenext
loop
if len(strmsg1 & strmsg2) then
msgbox iif(len(strmsg1),"下列材料无库存" & vbcrlf & strmsg1 & vbcrlf, "") & _
iif(len(strmsg2),"下列材料数量不足" & vbcrlf & strmsg2 & , "")
ProductName TEXT
ComponentName TEXT
ComponentQuantity CURRENCY(允许小数) 库存表:
ComponentName TEXT
ComponentQuantity CURRENCY(允许小数) 查库:set rs1 = cnn.execute("select * from 产品表 where ProductName='" & txtProduct & "'")if rs1.eof then msgbox "无此产品":exit subdo until rs1.eof
set rs2 = cnn.execute(("select * from 库存表 where ComponentName='" & rs1!ComponentName & "'")
if rs2.eof then
strMsg1 = strMsg1 & rs1!ComponentName & vbcrlf
else if rs2!ComponentQuantity < rs1!ComponentQuantity then
strMsg1 = strMsg2 & rs1!ComponentName & ": " & rs2!ComponentQuantity & vbcrlf
end if
rs1.movenext
loop
if len(strmsg1 & strmsg2) then
msgbox iif(len(strmsg1),"下列材料无库存" & vbcrlf & strmsg1 & vbcrlf, "") & _
iif(len(strmsg2),"下列材料数量不足" & vbcrlf & strmsg2 & , "")
产品作为一个表,一个字段是所用材料的编号集合,用“+”连接,一个字段是每个材料的数量集合,也用用“+”连接;
rs.Open "select * from 成品 where 成品名称='" & cmbName & "' and 成品类别=" & TypeofGoods, cn
sName = rs("散件编号")
sCount = rs("散件数量")
rs.Close bEnd = False
lstConfig.Clear
lstCount.Clear
lstUse.Clear
lstLeft.Clear
lstConfig.AddItem "散件名称"
lstCount.AddItem "现存数量"
lstUse.AddItem "要用数量"
lstLeft.AddItem "剩余数量"
Do While Not bEnd
iPost = InStr(sName, "+")
If iPost = 0 Then
iName = CLng(sName)
iCount = CLng(sCount)
bEnd = True
Else
iName = CLng(Left(sName, iPost - 1))
sName = Right(sName, Len(sName) - iPost)
iPost = InStr(sCount, "+")
iCount = CLng(Left(sCount, iPost - 1))
sCount = Right(sCount, Len(sCount) - iPost)
End If
rs.Open "select * from 散件 where 散件编号 = " & iName, cn
lstConfig.AddItem rs("散件名称")
lstCount.AddItem rs("数量")
lstUse.AddItem iCount * CLng(txtCount)
If rs("数量") - iCount * CLng(txtCount) < 0 Then
lstLeft.AddItem "!!!" & rs("数量") - iCount * CLng(txtCount)
Else
lstLeft.AddItem rs("数量") - iCount * CLng(txtCount)
End If
rs.Close
Loop