盟危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 Sub
Private 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 Sub
ErrorHandler:
RDPErrorHandler Me.Name & ": Sub Form_Load()"
Resume ExitHere
End Sub
Private 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 If
ExitHere:
Set rst = Nothing
Set cnn = Nothing
Set rstTmp = Nothing
Exit Sub
ErrorHandler:
If blnTransBegin Then
cnn.RollbackTrans
blnTransBegin = False
End If
RDPErrorHandler Me.Name & ": Sub btnSave_Click()"
Resume ExitHere
End Sub
Private Sub btnCancel_Click()
On Error Resume Next
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
uj5u.com熱心網友回復：
出錯的時候按Ctrl+Break,找到出錯的位置;uj5u.com熱心網友回復：
樓上你好，視窗運行正常，只有點擊審核時出錯，按你的方法按Ctrl+break鍵，無任何反應，謝謝uj5u.com熱心網友回復：
并不是在除錯時報錯uj5u.com熱心網友回復：
從資料庫里取出的字串內容需要用一個IsNull函式判斷內容是否為空，不是空的時候才能把里邊的內容當字串處理，不然就報這個錯誤了。
uj5u.com熱心網友回復：
樓上，能不能具體點，新人，不是很懂，謝謝uj5u.com熱心網友回復：
比如這行 rst![經辦人] = Me![經辦人]
你要寫成 if isnull(rst![經辦人]) =False then rst![經辦人] = Me![經辦人] ,你需要一個非空判斷加在前面
uj5u.com熱心網友回復：
我試試看，非常感謝
uj5u.com熱心網友回復：
還是報錯，訂單，商品ID等地方都加了判斷，還是報錯uj5u.com熱心網友回復：
報錯時候程式應該會停在出錯那行上的，看一下那行是什么代碼嘛uj5u.com熱心網友回復：
我是點擊表單按鈕報錯，代碼除錯沒問題，所以根本就看不出來哪里錯了
uj5u.com熱心網友回復：
怎么聽起來像控制元件問題，改下資料，把里邊的空值填點東西上去算了
uj5u.com熱心網友回復：
空值全部都填了，不知道怎么還是報錯轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/23408.html
標籤：VBA
上一篇：哪位大神幫忙寫個宏代碼Excel指定時間自動銷毀，更改系統時間也無效，謝了
下一篇：VB 模糊洗掉sheet