盟危ACCESS通用平台做的出入库,点审核的时候出现#94无效使用Null,但是代码调试没有报错,我把代码全部贴出来请高手看看怎么回事,谢谢
Option Compare Database
Option Explicit
Private mclsSC供应商ID As New SearchComboBox '声明一个组合框动态筛选类并将其实例化Private Sub btnAudit_Click()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim strMsg As String
Dim strBillNo As String
Dim strUsername As String
strBillNo = "采购订单 No." & Me!采购订单号
strMsg = "审核之后将不能再编辑订单,并同时更新库存及供应商应付款,确定要完成" & strBillNo & " 的审核码?"
If MsgBox(strMsg, vbExclamation + vbOKCancel, "审核确认") = vbCancel Then
Exit Sub
End If
strUsername = Forms!sysfrmmain!Nickname
strSQL = " UPDATE 采购订单表 SET 状态='已审核',审核时间=now()" _
& ", 审核人= " & SQLtext(strUsername) _
& " WHERE [采购订单号]=" & SQLtext(Me!采购订单号)
If DAORunSQL(strSQL) Then
Me!状态 = "已审核"
Me!审核人 = strUsername
Me!审核时间 = Now()
Me.sfrDetail.SetFocus
Me.btnAudit.Enabled = False
Me.AllowEdits = False
Me.sfrDetail.Form.AllowEdits = False
Me.sfrDetail.Form.AllowAdditions = False
Me.sfrDetail!btnDelete.Enabled = False
Form_frm采购订单.Refresh
Me.btnSave.Enabled = False
Me.sfrDetail!btnDeleteAll.Enabled = False
Me.sfrDetail!btnAddProduct.Enabled = False
End If
DoCmd.SetWarnings False
'审核时对供应商信息表中的最近采购日期进行更新
DoCmd.RunSQL " UPDATE 供应商信息表 SET 最近采购日期 = #" & Me.采购日期 & " # WHERE 供应商ID=" & Me.供应商ID
'更新供应商应付款
'Dim je As Long
'je = Me.sfrDetail.Form!金额合计
'DoCmd.RunSQL " INSERT INTO 供应商应收付信息表 VALUES (" & Me.供应商ID & ",'" & Me.采购订单号 & "'," & je & ",#" & Me.审核时间 & "#,-1,false,'采购入库')"
'更新商品库存
Dim strCalculateSQL As String
strCalculateSQL = "UPDATE TMP_采购订单明细表 INNER JOIN 商品信息表 ON TMP_采购订单明细表.商品ID = 商品信息表.商品ID " _
& "SET 商品信息表.库存数量 = 商品信息表.库存数量+TMP_采购订单明细表.数量;"
DoCmd.RunSQL strCalculateSQL '用更新查询对商品信息表中的相关商品的库存数量进行增减
'更新商品最新进价
strCalculateSQL = "UPDATE TMP_采购订单明细表 INNER JOIN 商品信息表 ON TMP_采购订单明细表.商品ID = 商品信息表.商品ID " _
& "SET 商品信息表.最新进价 = TMP_采购订单明细表.单价;"
DoCmd.RunSQL strCalculateSQL
DoCmd.SetWarnings True
ExitHere:
Exit Sub
ErrorHandler:
MsgBoxEx Err.Description, vbCritical
Resume ExitHere
End SubPrivate Sub Form_Load()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim cnn As Object 'ADODB.Connection
Dim rst As Object 'ADODB.Recordset
Dim rstTmp As Object 'DAO.Recordset ApplyTheme Me
CurrentDb.Execute "DELETE FROM [TMP_采购订单明细表]"
If IsNull(Me.OpenArgs) Then
Me.DataEntry = True
End If
'------要添加的代码 开始----
Dim rstUser As Object
Set rstUser = OpenADORecordset("SELECT Sys_Users.Nickname FROM Sys_Users", adLockReadOnly)
Set Me.经办人.Recordset = rstUser
Set Me.制单人.Recordset = rstUser
'------要添加的代码 结束----
mclsSC供应商ID.Init Combo:=Me.供应商ID, _
SearchField:="供应商名称 & 拼音码", _
SQLSELECT:="供应商ID, 供应商名称, 拼音码, 供应商类别", _
SQLFROM:="供应商信息表", _
SQLWHERE:="已停用=0", _
SQLORDERBY:="拼音码"
If Me.DataEntry Then
Me.采购日期.SetFocus
' Me.采购订单号.Visible = False
' Me.状态.Visible = False
Me.审核人.Visible = False
Me.审核人_Label.Visible = False
Me.审核时间.Visible = False
Me.审核时间_Label.Visible = False
Me.btnAudit.Enabled = False
'------要添加的代码 开始----
'If IsLoaded("SysFrmMain") = True Then
If IsLoaded(acForm, "SysFrmMain") = True Then
Me.制单人 = Forms!sysfrmmain.Nickname '该昵称在SysFrmMain窗体,所以必须让SysFrmMain窗体处于打开状态
Me.经办人 = Forms!sysfrmmain.Nickname
End If
Me.采购日期 = Date '也可以直接在控件属性中设置
Me.制单时间 = Date '也可以直接在控件属性中设置
Me.供应商ID.SetFocus '这样一打开,就直接定位到选择供应商
'------要添加的代码 结束----
Me.sfrDetail.Requery
Exit Sub
End If
Me.btnSave.Enabled = Me.AllowEdits Set cnn = CurrentProject.Connection strSQL = "SELECT * FROM [采购订单表] WHERE [采购订单号]=" & SQLtext(Me.OpenArgs)
Set rst = OpenADORecordset(strSQL, , cnn)
Me![状态] = rst![状态]
Me![采购订单号] = rst![采购订单号]
Me![采购日期] = rst![采购日期]
Me![供应商ID] = rst![供应商ID]
Me![经办人] = rst![经办人]
Me![制单人] = rst![制单人]
Me![制单时间] = rst![制单时间]
Me![审核人] = rst![审核人]
Me![审核时间] = rst![审核时间]
Me![备注] = rst![备注]
rst.Close strSQL = "SELECT * FROM [采购订单查询_明细] WHERE [采购订单号]=" & SQLtext(Me![采购订单号])
Set rst = OpenADORecordset(strSQL, , cnn)
Set rstTmp = CurrentDb.OpenRecordset("TMP_采购订单明细表")
Do Until rst.EOF
rstTmp.AddNew
rstTmp![采购订单号] = rst![采购订单号]
rstTmp![商品ID] = rst![商品ID]
rstTmp![品名规格] = rst![品名规格]
rstTmp![单位] = rst![单位]
rstTmp![数量] = rst![数量]
rstTmp![单价] = rst![单价]
rstTmp.Update
rst.MoveNext
Loop
rst.Close
rstTmp.Close
Me.sfrDetail.Requery'----------------要添加的代码 开始------
Dim strStatus As String
strStatus = Me.状态
Me.sfrDetail!商品ID.SetFocus
Select Case strStatus
Case "待审核"
Me.审核人.Visible = True
Me.审核人_Label.Visible = True
Me.审核时间.Visible = True
Me.审核时间_Label.Visible = True
Case "已审核"
Me.AllowEdits = False
Me.btnAudit.Enabled = False
Me.btnSave.Enabled = False
Me.sfrDetail!商品ID.Locked = True
Me.sfrDetail!品名规格.Locked = True
Me.sfrDetail!单位.Locked = True
Me.sfrDetail!单价.Locked = True
Me.审核人.Visible = True
Me.审核人_Label.Visible = True
Me.审核人.Locked = True
Me.审核时间.Visible = True
Me.审核时间_Label.Visible = True
Me.审核时间.Locked = True
Me.sfrDetail.Form.AllowAdditions = False
Me.sfrDetail.Form.btnDelete.Enabled = False
Me.sfrDetail.Form.btnAddProduct.Enabled = False
Me.sfrDetail.Form.btnDeleteAll.Enabled = False
End Select
Me.状态.Locked = True '锁定该文本框
'----------------要添加的代码 结束------ExitHere:
Set rst = Nothing
Set cnn = Nothing
Set rstTmp = Nothing
Exit SubErrorHandler:
RDPErrorHandler Me.Name & ": Sub Form_Load()"
Resume ExitHere
End SubPrivate Sub btnSave_Click()
On Error GoTo ErrorHandler
Dim strWhere As String
Dim strSQL As String
Dim cnn As Object 'ADODB.Connection
Dim rst As Object 'ADODB.Recordset
Dim rstTmp As Object 'DAO.Recordset
Dim blnTransBegin As Boolean If Not CheckRequired(Me) Then Exit Sub
If Not CheckTextLength(Me) Then Exit Sub
If Not CheckRequired(Me.sfrDetail) Then Exit Sub Set cnn = CurrentProject.Connection cnn.BeginTrans
blnTransBegin = True strSQL = "SELECT * FROM [采购订单表] WHERE [采购订单号]=" & SQLtext(Me![采购订单号])
Set rst = OpenADORecordset(strSQL, adLockOptimistic, cnn)
If rst.EOF Then
rst.AddNew
rst![采购订单号] = GetAutoNumber("采购订单号")
'-----下面的移上来的-----------------
rst![状态] = "待审核"
rst![制单人] = Me![制单人]
rst![制单时间] = Now()
End If
rst![采购日期] = Me![采购日期]
rst![供应商ID] = Me![供应商ID]
rst![经办人] = Me![经办人]
' rst![审核人] = Me![审核人]
' rst![审核时间] = Me![审核时间]
rst![备注] = Me![备注]
rst.Update
Me![采购订单号] = rst![采购订单号]
rst.Close cnn.Execute "DELETE FROM [采购订单明细表] WHERE [采购订单号]=" & SQLtext(Me![采购订单号])
strSQL = "SELECT * FROM [采购订单明细表] WHERE [采购订单号]=" & SQLtext(Me![采购订单号])
Set rst = OpenADORecordset(strSQL, adLockOptimistic, cnn)
Set rstTmp = CurrentDb.OpenRecordset("TMP_采购订单明细表")
Do Until rstTmp.EOF
rst.AddNew
rst![采购订单号] = Me![采购订单号]
rst![商品ID] = rstTmp![商品ID]
rst![数量] = rstTmp![数量]
rst![单价] = rstTmp![单价]
rst.Update
rstTmp.MoveNext
Loop
rst.Close
rstTmp.Close
cnn.CommitTrans Form_frm采购订单.Refresh
MsgBoxEx "保存成功!", vbInformation' If Me.DataEntry Then
' ClearControlValues Me
' CurrentDb.Execute "DELETE FROM [TMP_采购订单明细表]"
' Me.sfrDetail.Requery
' Else
' DoCmd.Close acForm, Me.Name, acSaveNo
' End IfExitHere:
Set rst = Nothing
Set cnn = Nothing
Set rstTmp = Nothing
Exit SubErrorHandler:
If blnTransBegin Then
cnn.RollbackTrans
blnTransBegin = False
End If
RDPErrorHandler Me.Name & ": Sub btnSave_Click()"
Resume ExitHere
End SubPrivate Sub btnCancel_Click()
On Error Resume Next
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
Option Compare Database
Option Explicit
Private mclsSC供应商ID As New SearchComboBox '声明一个组合框动态筛选类并将其实例化Private Sub btnAudit_Click()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim strMsg As String
Dim strBillNo As String
Dim strUsername As String
strBillNo = "采购订单 No." & Me!采购订单号
strMsg = "审核之后将不能再编辑订单,并同时更新库存及供应商应付款,确定要完成" & strBillNo & " 的审核码?"
If MsgBox(strMsg, vbExclamation + vbOKCancel, "审核确认") = vbCancel Then
Exit Sub
End If
strUsername = Forms!sysfrmmain!Nickname
strSQL = " UPDATE 采购订单表 SET 状态='已审核',审核时间=now()" _
& ", 审核人= " & SQLtext(strUsername) _
& " WHERE [采购订单号]=" & SQLtext(Me!采购订单号)
If DAORunSQL(strSQL) Then
Me!状态 = "已审核"
Me!审核人 = strUsername
Me!审核时间 = Now()
Me.sfrDetail.SetFocus
Me.btnAudit.Enabled = False
Me.AllowEdits = False
Me.sfrDetail.Form.AllowEdits = False
Me.sfrDetail.Form.AllowAdditions = False
Me.sfrDetail!btnDelete.Enabled = False
Form_frm采购订单.Refresh
Me.btnSave.Enabled = False
Me.sfrDetail!btnDeleteAll.Enabled = False
Me.sfrDetail!btnAddProduct.Enabled = False
End If
DoCmd.SetWarnings False
'审核时对供应商信息表中的最近采购日期进行更新
DoCmd.RunSQL " UPDATE 供应商信息表 SET 最近采购日期 = #" & Me.采购日期 & " # WHERE 供应商ID=" & Me.供应商ID
'更新供应商应付款
'Dim je As Long
'je = Me.sfrDetail.Form!金额合计
'DoCmd.RunSQL " INSERT INTO 供应商应收付信息表 VALUES (" & Me.供应商ID & ",'" & Me.采购订单号 & "'," & je & ",#" & Me.审核时间 & "#,-1,false,'采购入库')"
'更新商品库存
Dim strCalculateSQL As String
strCalculateSQL = "UPDATE TMP_采购订单明细表 INNER JOIN 商品信息表 ON TMP_采购订单明细表.商品ID = 商品信息表.商品ID " _
& "SET 商品信息表.库存数量 = 商品信息表.库存数量+TMP_采购订单明细表.数量;"
DoCmd.RunSQL strCalculateSQL '用更新查询对商品信息表中的相关商品的库存数量进行增减
'更新商品最新进价
strCalculateSQL = "UPDATE TMP_采购订单明细表 INNER JOIN 商品信息表 ON TMP_采购订单明细表.商品ID = 商品信息表.商品ID " _
& "SET 商品信息表.最新进价 = TMP_采购订单明细表.单价;"
DoCmd.RunSQL strCalculateSQL
DoCmd.SetWarnings True
ExitHere:
Exit Sub
ErrorHandler:
MsgBoxEx Err.Description, vbCritical
Resume ExitHere
End SubPrivate Sub Form_Load()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim cnn As Object 'ADODB.Connection
Dim rst As Object 'ADODB.Recordset
Dim rstTmp As Object 'DAO.Recordset ApplyTheme Me
CurrentDb.Execute "DELETE FROM [TMP_采购订单明细表]"
If IsNull(Me.OpenArgs) Then
Me.DataEntry = True
End If
'------要添加的代码 开始----
Dim rstUser As Object
Set rstUser = OpenADORecordset("SELECT Sys_Users.Nickname FROM Sys_Users", adLockReadOnly)
Set Me.经办人.Recordset = rstUser
Set Me.制单人.Recordset = rstUser
'------要添加的代码 结束----
mclsSC供应商ID.Init Combo:=Me.供应商ID, _
SearchField:="供应商名称 & 拼音码", _
SQLSELECT:="供应商ID, 供应商名称, 拼音码, 供应商类别", _
SQLFROM:="供应商信息表", _
SQLWHERE:="已停用=0", _
SQLORDERBY:="拼音码"
If Me.DataEntry Then
Me.采购日期.SetFocus
' Me.采购订单号.Visible = False
' Me.状态.Visible = False
Me.审核人.Visible = False
Me.审核人_Label.Visible = False
Me.审核时间.Visible = False
Me.审核时间_Label.Visible = False
Me.btnAudit.Enabled = False
'------要添加的代码 开始----
'If IsLoaded("SysFrmMain") = True Then
If IsLoaded(acForm, "SysFrmMain") = True Then
Me.制单人 = Forms!sysfrmmain.Nickname '该昵称在SysFrmMain窗体,所以必须让SysFrmMain窗体处于打开状态
Me.经办人 = Forms!sysfrmmain.Nickname
End If
Me.采购日期 = Date '也可以直接在控件属性中设置
Me.制单时间 = Date '也可以直接在控件属性中设置
Me.供应商ID.SetFocus '这样一打开,就直接定位到选择供应商
'------要添加的代码 结束----
Me.sfrDetail.Requery
Exit Sub
End If
Me.btnSave.Enabled = Me.AllowEdits Set cnn = CurrentProject.Connection strSQL = "SELECT * FROM [采购订单表] WHERE [采购订单号]=" & SQLtext(Me.OpenArgs)
Set rst = OpenADORecordset(strSQL, , cnn)
Me![状态] = rst![状态]
Me![采购订单号] = rst![采购订单号]
Me![采购日期] = rst![采购日期]
Me![供应商ID] = rst![供应商ID]
Me![经办人] = rst![经办人]
Me![制单人] = rst![制单人]
Me![制单时间] = rst![制单时间]
Me![审核人] = rst![审核人]
Me![审核时间] = rst![审核时间]
Me![备注] = rst![备注]
rst.Close strSQL = "SELECT * FROM [采购订单查询_明细] WHERE [采购订单号]=" & SQLtext(Me![采购订单号])
Set rst = OpenADORecordset(strSQL, , cnn)
Set rstTmp = CurrentDb.OpenRecordset("TMP_采购订单明细表")
Do Until rst.EOF
rstTmp.AddNew
rstTmp![采购订单号] = rst![采购订单号]
rstTmp![商品ID] = rst![商品ID]
rstTmp![品名规格] = rst![品名规格]
rstTmp![单位] = rst![单位]
rstTmp![数量] = rst![数量]
rstTmp![单价] = rst![单价]
rstTmp.Update
rst.MoveNext
Loop
rst.Close
rstTmp.Close
Me.sfrDetail.Requery'----------------要添加的代码 开始------
Dim strStatus As String
strStatus = Me.状态
Me.sfrDetail!商品ID.SetFocus
Select Case strStatus
Case "待审核"
Me.审核人.Visible = True
Me.审核人_Label.Visible = True
Me.审核时间.Visible = True
Me.审核时间_Label.Visible = True
Case "已审核"
Me.AllowEdits = False
Me.btnAudit.Enabled = False
Me.btnSave.Enabled = False
Me.sfrDetail!商品ID.Locked = True
Me.sfrDetail!品名规格.Locked = True
Me.sfrDetail!单位.Locked = True
Me.sfrDetail!单价.Locked = True
Me.审核人.Visible = True
Me.审核人_Label.Visible = True
Me.审核人.Locked = True
Me.审核时间.Visible = True
Me.审核时间_Label.Visible = True
Me.审核时间.Locked = True
Me.sfrDetail.Form.AllowAdditions = False
Me.sfrDetail.Form.btnDelete.Enabled = False
Me.sfrDetail.Form.btnAddProduct.Enabled = False
Me.sfrDetail.Form.btnDeleteAll.Enabled = False
End Select
Me.状态.Locked = True '锁定该文本框
'----------------要添加的代码 结束------ExitHere:
Set rst = Nothing
Set cnn = Nothing
Set rstTmp = Nothing
Exit SubErrorHandler:
RDPErrorHandler Me.Name & ": Sub Form_Load()"
Resume ExitHere
End SubPrivate Sub btnSave_Click()
On Error GoTo ErrorHandler
Dim strWhere As String
Dim strSQL As String
Dim cnn As Object 'ADODB.Connection
Dim rst As Object 'ADODB.Recordset
Dim rstTmp As Object 'DAO.Recordset
Dim blnTransBegin As Boolean If Not CheckRequired(Me) Then Exit Sub
If Not CheckTextLength(Me) Then Exit Sub
If Not CheckRequired(Me.sfrDetail) Then Exit Sub Set cnn = CurrentProject.Connection cnn.BeginTrans
blnTransBegin = True strSQL = "SELECT * FROM [采购订单表] WHERE [采购订单号]=" & SQLtext(Me![采购订单号])
Set rst = OpenADORecordset(strSQL, adLockOptimistic, cnn)
If rst.EOF Then
rst.AddNew
rst![采购订单号] = GetAutoNumber("采购订单号")
'-----下面的移上来的-----------------
rst![状态] = "待审核"
rst![制单人] = Me![制单人]
rst![制单时间] = Now()
End If
rst![采购日期] = Me![采购日期]
rst![供应商ID] = Me![供应商ID]
rst![经办人] = Me![经办人]
' rst![审核人] = Me![审核人]
' rst![审核时间] = Me![审核时间]
rst![备注] = Me![备注]
rst.Update
Me![采购订单号] = rst![采购订单号]
rst.Close cnn.Execute "DELETE FROM [采购订单明细表] WHERE [采购订单号]=" & SQLtext(Me![采购订单号])
strSQL = "SELECT * FROM [采购订单明细表] WHERE [采购订单号]=" & SQLtext(Me![采购订单号])
Set rst = OpenADORecordset(strSQL, adLockOptimistic, cnn)
Set rstTmp = CurrentDb.OpenRecordset("TMP_采购订单明细表")
Do Until rstTmp.EOF
rst.AddNew
rst![采购订单号] = Me![采购订单号]
rst![商品ID] = rstTmp![商品ID]
rst![数量] = rstTmp![数量]
rst![单价] = rstTmp![单价]
rst.Update
rstTmp.MoveNext
Loop
rst.Close
rstTmp.Close
cnn.CommitTrans Form_frm采购订单.Refresh
MsgBoxEx "保存成功!", vbInformation' If Me.DataEntry Then
' ClearControlValues Me
' CurrentDb.Execute "DELETE FROM [TMP_采购订单明细表]"
' Me.sfrDetail.Requery
' Else
' DoCmd.Close acForm, Me.Name, acSaveNo
' End IfExitHere:
Set rst = Nothing
Set cnn = Nothing
Set rstTmp = Nothing
Exit SubErrorHandler:
If blnTransBegin Then
cnn.RollbackTrans
blnTransBegin = False
End If
RDPErrorHandler Me.Name & ": Sub btnSave_Click()"
Resume ExitHere
End SubPrivate Sub btnCancel_Click()
On Error Resume Next
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
解决方案 »
- VB读txt文件怎么读
- 在VB6中已经可以使用picturebox控件播放GIF。进一步的问题,如何在同一个picturebox中重叠播放多个GIF?哪位高手帮帮忙?
- vb 中如何操作 mdb的主子表
- 如何做出停靠工具栏和窗口
- 请问如何读取特定(C:\readme.txt)文件,并显示
- 如何在VB程序中显示中文,英文和日文等文字
- 一个简单的问题,急!!!
- 哪位大哥有财务方面的源码?做毕业设计用,谢了
- 请问这个函数怎么用 GetGlyphOutline
- VB不能正确调用ACCESS的数据文件
- 如何在一个word按1-2的顺序插入其他word的内容并在内容下面插入word的名字
- 求助:VBA中正则表达式提取末端文字
不是空的时候才能把里边的内容当字符串处理,不然就报这个错误了。
你要写成 if isnull(rst![经办人]) =False then rst![经办人] = Me![经办人] ,你需要一个非空判断加在前面
你要写成 if isnull(rst![经办人]) =False then rst![经办人] = Me![经办人] ,你需要一个非空判断加在前面
我试试看,非常感谢