Private Sub cmdRec_Click()
On Error GoTo ErrorHandler
Dim strsql As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rstTmp As DAO.Recordset
Dim blnTransBegin As Boolean
Dim strExpr As String
'以下是检测窗体的状态是否改为已接收
If Me!Status <> "已接收" Then
MsgBox "接收前要确认资料无误,并将态设为'已接收'!", vbCritical, "提示"
Me.Status.SetFocus
Exit Sub
End If
'以下是为检测是否已接收,防止两个人同时打开并接收
If DCount("*", "tbl_Wip_Wo_Dep_Master", "TrCode= '已接收'") > 0 Then
MsgBox "已接收,不能重复接收!", vbInformation, "提示"
Exit Sub
End If
'检查必填项未通过时退出
If Not CheckRequired(Me) Then Exit Sub
Me.cmdRec.Enabled = False
DoCmd.SetWarnings False
DoCmd.RunSQL " INSERT INTO tbl_Wip_Wo_Dep_Stock(WoCode, Dep)" _
& " SELECT Temp_tbl_Wip_Wo_Dep_Detail.WoCode, '" & Me.ToDep & "'" _
& " FROM Temp_tbl_Wip_Wo_Dep_Detail;"
DoCmd.SetWarnings True
Set cnn = GetADOConnection()
cnn.BeginTrans
blnTransBegin = True
cnn.Execute " UPDATE tbl_Wip_Wo_Dep_Master SET Status= '" & Me![Status] & "'" _
& " WHERE TrCode='" & TrCode & "'" ', lngCount
Set rstTmp = CurrentDb.OpenRecordset("Temp_tbl_Wip_Wo_Dep_Detail")
rstTmp.MoveFirst
Do Until rstTmp.EOF
cnn.Execute " UPDATE dbo.tbl_Wip_Wo_Dep_Stock" _
& " SET Quantity = IsNull(dbo.tbl_Wip_Wo_Dep_Stock.Quantity ,0)- " & Nz(rstTmp!Quantity, 0) _
& " ,GrossWeight = IsNull(dbo.tbl_Wip_Wo_Dep_Stock.GrossWeight ,0)- " & Nz(rstTmp!GrossWeight, 0) _
& " ,StoneWeight = IsNull(dbo.tbl_Wip_Wo_Dep_Stock.StoneWeight ,0)- " & Nz(rstTmp!StoneWeight, 0) _
& " ,NetWeight = IsNull(dbo.tbl_Wip_Wo_Dep_Stock.NetWeight ,0)- " & Nz(rstTmp!NetWeight, 0) _
& " WHERE Dep = '" & Me!FromDep & "' and WoCode='" & rstTmp!WoCode & "'"
cnn.Execute " UPDATE dbo.tbl_Wip_Wo_Dep_Stock" _
& " SET Quantity = IsNull(dbo.tbl_Wip_Wo_Dep_Stock.Quantity ,0)+ " & Nz(rstTmp!Quantity, 0) _
& " ,GrossWeight = IsNull(dbo.tbl_Wip_Wo_Dep_Stock.GrossWeight ,0)+ " & Nz(rstTmp!GrossWeight, 0) _
& " ,StoneWeight = IsNull(dbo.tbl_Wip_Wo_Dep_Stock.StoneWeight ,0)+" & Nz(rstTmp!StoneWeight, 0) _
& " ,NetWeight = IsNull(dbo.tbl_Wip_Wo_Dep_Stock.NetWeight ,0)+" & Nz(rstTmp!NetWeight, 0) _
& " WHERE Dep = '" & Me!ToDep & "' and WoCode='" & rstTmp!WoCode & "'"
cnn.Execute " UPDATE dbo.tbl_Wip_Wo_Dep_Stock SET DepDate = " & Format$(Me![UpdateTime], "\'yyyy-mm-dd hh:nn:ss\'") _
& " WHERE WoCode = '" & rstTmp!WoCode & "' AND Dep='" & Me![ToDep] & "'"
rstTmp.MoveNext
Loop
rstTmp.Close
cnn.CommitTrans '提交事务
blnTransBegin = False '事务提交或回滚后要立即恢复标志变量,防止后面的代码出错造成错误代码不能正常判断
MsgBox "保存成功!", vbInformation, "提示!"
Call ChildFormRequery(strExpr)
DoCmd.Close acForm, Me.name
ExitHere:
Set rstTmp = Nothing
Set rst = Nothing
Set cnn = Nothing
Exit Sub
ErrorHandler:
If blnTransBegin Then
cnn.RollbackTrans '出错时如果已启动事务,则回滚撤销
blnTransBegin = False
End If
MsgBox Err.Description, vbCritical, "Error #" & Err
Resume ExitHere
End Sub
各位大师能看得出以上的代码有什么问题 吗?有时偶尔出现重复运行计算库存!