现在有一数据库,是软件公司提供的商业化程序,但是程序不够完善,所以我在他的数据库基础上直接挂了一个开单的小程序,我在挂接后其他都好,但是保存时会和他的ID重复,我想了都N天了,都不知道该如何是好!他的表中所有单据类型都是保存在同一张表中,如销售出库单用1表示,入库单用2表示,等等,一共有6种表单.在商业程序的销售出库单我是废弃不用的,因此销售出库单会和其他单据类型重复,造成单据中会有其他类型的数据,搞得我很难看,请各位大侠帮帮我,解决一下这个问题!
他的ID不是自动编号的,是采用在另外一张上保存,保存时自动加1这种方式.因为是在局域网内用的人比较多,难免会在相同的时间内保存,所以就出现了上述的问题.我的代码如下:
Sub mylink()连接数据库
Set CNN = CreateObject("ADODB.Connection")
Set RST = CreateObject("ADODB.Recordset")
CNN.Open "Provider=sqloledb;Server=" & Sserver & ";Database=Jr2007080814;Uid=sa;Pwd=;"
CNN.CursorLocation = adUseClient
End Sub
Sub Pzsale() '主表 Call mylink
sql1 = "select fmaxnum as billNOmax from tblmaxNum where ftablename='ICStockBill'"
RST.Open sql1, CNN, adOpenKeyset, adLockOptimistic
ff = RST.Fields("billNOmax")
SQL = "insert into icstockbill(finterid,fstatus,fcancellation,fStockid1,fcustomerid,fStyleId,fDeptId,fEmperId,fFManager,fSmanager,fdate,fOperator,ftrantype,fyear,fperiod,frob,fbillno)"
SQL = SQL & " values('" & ff & "','" & 0 & "','" & 0 & "','" & 11 & "',"
SQL = SQL & "'" & Range("E7").Value & "',"
SQL = SQL & "'" & 3 & "',"
SQL = SQL & "'" & Trim(Left(Range("K18").Value, 4)) & "',"
SQL = SQL & "'" & Range("k7").Value & "',"
SQL = SQL & "'" & 0 & "',"
SQL = SQL & "'" & 0 & "',"
SQL = SQL & "'" & Format(Range("I7").Value, "yyyy-mm-dd") & "',"
SQL = SQL & "'" & userID & "',"
SQL = SQL & "'" & 4 & "',"
SQL = SQL & "'" & 2007 & "',"
SQL = SQL & "'" & ffperiod & "',"
SQL = SQL & "'" & 0 & "',"
SQL = SQL & "'" & Range("K6").Value & "')"
CNN.Execute SQL
Set RST = Nothing: CNN.Close
End SubSub Pzsaleqq() '附表
Call mylink
Dim p As Integer, pp As Integer
For p = 9 To 14
pp = p - 8
If Range("E" & p).Value <> "" Then
With RST
SQL = "insert into icstockbillentry(finterid,forderid,fentryid,fitemid,fUnitId,fBatchno,fQty,fPrice,fAmount,fTax,fPriceTax,fAmountTax,fMemory,fdorderid,fnoticeid)"
SQL = SQL & " values('" & ff & "','" & pp & "','" & pp & "',"
SQL = SQL & "'" & IIf(Range("E" & p).Value = "", Null, Range("E" & p).Value) & "',"
SQL = SQL & "'" & IIf(Range("H" & p).Value = "", Null, Range("H" & p).Value) & "',"
SQL = SQL & "'" & "" & "'," '批次
SQL = SQL & "'" & IIf(Range("j" & p).Value = "", Null, Range("j" & p).Value) & "'," '数量
SQL = SQL & "'" & IIf(Range("k" & p).Value = "", Null, Range("k" & p).Value) & "'," '单价
SQL = SQL & "'" & IIf(Range("l" & p).Value = "", Null, Range("l" & p).Value) & "'," '金额
SQL = SQL & "'" & 0 & "'," '税率
SQL = SQL & "'" & IIf(Range("k" & p).Value = "", Null, Range("k" & p).Value) & "'," '单价
SQL = SQL & "'" & IIf(Range("l" & p).Value = "", Null, Range("l" & p).Value) & "'," '金额
SQL = SQL & "'" & "" & "'," '备注
SQL = SQL & "'" & 0 & "'," '销售订单
SQL = SQL & "'" & 0 & "')" '发货通知单
CNN.Execute SQL
End With
End If
Next p
MsgBox "销售出库单保存成功!", , "DOVRO"
Set RST = Nothing: CNN.Close
End Sub
Sub dovromax()
Call mylink
SQL = "update tblmaxNum set fmaxnum=fmaxnum+1 where ftablename='icNoticebill'"
CNN.Execute SQL
Set RST = Nothing: CNN.Close
End Sub
Sub commbarsave()
If Range("E7").Value = "" Or Range("J9").Value = "" Or Range("K7").Value = "" Then
MsgBox "请将发货通知单内容录入完整!", 1 + 64, "DOVRO"
Exit Sub
End If
Call mylink
SQL = "select fBillNo from ICNoticeBill "
RST.Open SQL, CNN, adOpenKeyset, adLockPessimistic
RST.Find "fBillNo = '" & Range("K6").Value & "'"
If Not RST.EOF() Then
MsgBox "该单据已保存,请勿再次点击保存!", vbOKOnly, "DOVRO"
Exit Sub
End If
Call Pzsale '销售出库单主表
Call Pzsaleqq '销售出库单附表
Call dovronomax '更新销售出库单最大单号
End Sub大概情况也就是这样了,请大家帮我想想办法!谢谢!
他的ID不是自动编号的,是采用在另外一张上保存,保存时自动加1这种方式.因为是在局域网内用的人比较多,难免会在相同的时间内保存,所以就出现了上述的问题.我的代码如下:
Sub mylink()连接数据库
Set CNN = CreateObject("ADODB.Connection")
Set RST = CreateObject("ADODB.Recordset")
CNN.Open "Provider=sqloledb;Server=" & Sserver & ";Database=Jr2007080814;Uid=sa;Pwd=;"
CNN.CursorLocation = adUseClient
End Sub
Sub Pzsale() '主表 Call mylink
sql1 = "select fmaxnum as billNOmax from tblmaxNum where ftablename='ICStockBill'"
RST.Open sql1, CNN, adOpenKeyset, adLockOptimistic
ff = RST.Fields("billNOmax")
SQL = "insert into icstockbill(finterid,fstatus,fcancellation,fStockid1,fcustomerid,fStyleId,fDeptId,fEmperId,fFManager,fSmanager,fdate,fOperator,ftrantype,fyear,fperiod,frob,fbillno)"
SQL = SQL & " values('" & ff & "','" & 0 & "','" & 0 & "','" & 11 & "',"
SQL = SQL & "'" & Range("E7").Value & "',"
SQL = SQL & "'" & 3 & "',"
SQL = SQL & "'" & Trim(Left(Range("K18").Value, 4)) & "',"
SQL = SQL & "'" & Range("k7").Value & "',"
SQL = SQL & "'" & 0 & "',"
SQL = SQL & "'" & 0 & "',"
SQL = SQL & "'" & Format(Range("I7").Value, "yyyy-mm-dd") & "',"
SQL = SQL & "'" & userID & "',"
SQL = SQL & "'" & 4 & "',"
SQL = SQL & "'" & 2007 & "',"
SQL = SQL & "'" & ffperiod & "',"
SQL = SQL & "'" & 0 & "',"
SQL = SQL & "'" & Range("K6").Value & "')"
CNN.Execute SQL
Set RST = Nothing: CNN.Close
End SubSub Pzsaleqq() '附表
Call mylink
Dim p As Integer, pp As Integer
For p = 9 To 14
pp = p - 8
If Range("E" & p).Value <> "" Then
With RST
SQL = "insert into icstockbillentry(finterid,forderid,fentryid,fitemid,fUnitId,fBatchno,fQty,fPrice,fAmount,fTax,fPriceTax,fAmountTax,fMemory,fdorderid,fnoticeid)"
SQL = SQL & " values('" & ff & "','" & pp & "','" & pp & "',"
SQL = SQL & "'" & IIf(Range("E" & p).Value = "", Null, Range("E" & p).Value) & "',"
SQL = SQL & "'" & IIf(Range("H" & p).Value = "", Null, Range("H" & p).Value) & "',"
SQL = SQL & "'" & "" & "'," '批次
SQL = SQL & "'" & IIf(Range("j" & p).Value = "", Null, Range("j" & p).Value) & "'," '数量
SQL = SQL & "'" & IIf(Range("k" & p).Value = "", Null, Range("k" & p).Value) & "'," '单价
SQL = SQL & "'" & IIf(Range("l" & p).Value = "", Null, Range("l" & p).Value) & "'," '金额
SQL = SQL & "'" & 0 & "'," '税率
SQL = SQL & "'" & IIf(Range("k" & p).Value = "", Null, Range("k" & p).Value) & "'," '单价
SQL = SQL & "'" & IIf(Range("l" & p).Value = "", Null, Range("l" & p).Value) & "'," '金额
SQL = SQL & "'" & "" & "'," '备注
SQL = SQL & "'" & 0 & "'," '销售订单
SQL = SQL & "'" & 0 & "')" '发货通知单
CNN.Execute SQL
End With
End If
Next p
MsgBox "销售出库单保存成功!", , "DOVRO"
Set RST = Nothing: CNN.Close
End Sub
Sub dovromax()
Call mylink
SQL = "update tblmaxNum set fmaxnum=fmaxnum+1 where ftablename='icNoticebill'"
CNN.Execute SQL
Set RST = Nothing: CNN.Close
End Sub
Sub commbarsave()
If Range("E7").Value = "" Or Range("J9").Value = "" Or Range("K7").Value = "" Then
MsgBox "请将发货通知单内容录入完整!", 1 + 64, "DOVRO"
Exit Sub
End If
Call mylink
SQL = "select fBillNo from ICNoticeBill "
RST.Open SQL, CNN, adOpenKeyset, adLockPessimistic
RST.Find "fBillNo = '" & Range("K6").Value & "'"
If Not RST.EOF() Then
MsgBox "该单据已保存,请勿再次点击保存!", vbOKOnly, "DOVRO"
Exit Sub
End If
Call Pzsale '销售出库单主表
Call Pzsaleqq '销售出库单附表
Call dovronomax '更新销售出库单最大单号
End Sub大概情况也就是这样了,请大家帮我想想办法!谢谢!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货