Option Compare Database
Option Explicit
Private McLsSC vendor ID As the New dynamic filtering SearchComboBox 'declare a combo box class to instantiate
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="purchase order No." & amp; Me! Purchase order number
StrMsg="will not be able to edit the order after review, and at the same time update payables, inventory and suppliers determined to complete the" & amp; StrBillNo & amp; "Audit code?"
If MsgBox (strMsg vbExclamation + vbOKCancel, "verification")=vbCancel Then
The Exit Sub
End the If
StrUsername=Forms! Sysfrmmain! Nickname
StrSQL="UPDATE purchase order table SET status='has audit, the audit time=now ()" _
& ", a reviewer="& amp; SQLtext (strUsername) _
& "WHERE [the purchase order number]=" & amp; SQLtext (Me! Purchase order number)
If DAORunSQL (strSQL) Then
Me! Status="approved"
Me! The reviewer=strUsername
Me! Audit time=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 purchase orders. Refresh
Me. BtnSave. Enabled=False
Me sfrDetail! BtnDeleteAll. Enabled=False
Me sfrDetail! BtnAddProduct. Enabled=False
End the If
DoCmd. SetWarnings False
'audit of the supplier information in the table when purchasing date recently updated
DoCmd. RunSQL "UPDATE supplier information table SET recently purchase date=#" & amp; Me. Purchase date & amp; "# WHERE vendor ID=" & amp; Me. The supplier ID
'update supplier payables
'Dim je As Long
'je=Me. SfrDetail. Form! Aggregate amount
'DoCmd. RunSQL "INSERT INTO suppliers should cash information table VALUES (" & amp; Me. The vendor ID & amp; ", '" & amp; Me. Purchase order number & amp; "'," & amp; Je & amp; "#" & amp; Me. The audit time & amp; "# 1, false, 'procurement warehousing)"
'update inventory
Dim strCalculateSQL As String
StrCalculateSQL="UPDATE TMP_ purchase order schedule of INNER JOIN commodity information table ON TMP_ schedule of the purchase order. ID=commodity information of goods. Goods ID" _
& "SET commodity information tables. The inventory quantity=commodity information table. The inventory quantity + TMP_ purchase order sheet. Number;"
DoCmd. RunSQL strCalculateSQL 'update query for commodity information in the table increase or decrease the quantity of inventory goods related to
'update latest goods purchase price,StrCalculateSQL="UPDATE TMP_ purchase order schedule of INNER JOIN commodity information table ON TMP_ schedule of the purchase order. ID=commodity information of goods. Goods ID" _
& "SET commodity information tables. The latest purchase price=TMP_ schedule of the purchase order. The unit price;"
DoCmd. RunSQL strCalculateSQL
DoCmd. SetWarnings True
ExitHere:
The 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. You
Dim rstTmp As Object 'DAO. You
ApplyTheme Me
CurrentDb. Execute "DELETE FROM [TMP_ purchase order list]"
If IsNull (Me. OpenArgs) Then
Me. DataEntry=True
End the If
'-- -- -- -- -- - to add the code to start -
Dim rstUser As Object
The Set rstUser=OpenADORecordset (" SELECT Sys_Users. Nickname FROM Sys_Users ", adLockReadOnly)
Set Me. Agent. You=rstUser
Set Me. Making people. You=rstUser
'-- -- -- -- -- - to add the code ends -
McLsSC vendor ID. The Init Combo:=Me. Vendor ID, _
SearchField:="supplier name & amp; Pinyin code ", _
SQLSELECT:="vendor ID, supplier name, pinyin code, supplier category", _
SQLFROM:="supplier information list", _
SQLWHERE:="has stopped=0", _
SQLORDERBY:="pinyin code"
If Me. DataEntry Then
Me. The date of purchase. SetFocus
'Me. Purchase order number. The Visible=False
'Me state. The Visible=False
Me. The reviewer. The Visible=False
Me. Reviewer _Label. Visible=False
Me. The audit time. Visible=False
Me. The audit time _Label. Visible=False
Me. BtnAudit. Enabled=False
'-- -- -- -- -- - to add the code to start -
'If IsLoaded (" SysFrmMain ")=True Then
If IsLoaded (acForm, "SysFrmMain")=True Then
Me. Make people=Forms! Sysfrmmain. The Nickname "the Nickname on sysfrmmain form, so must let sysfrmmain form is open
Me. Agent=Forms! Sysfrmmain. The Nickname
End the If
Me.=Date 'purchase Date may also directly in the control properties Settings
Me. Make time=Date 'also can be set directly in the control properties
Me. The supplier ID. SetFocus' such a open,
the location directly to supplier selection'-- -- -- -- -- - to add the code ends -
Me. SfrDetail. Requery
The Exit Sub
End the If
Me. BtnSave. Enabled=Me. AllowEdits
Set the CNN=CurrentProject. Connection
StrSQL="SELECT * FROM/purchase order table WHERE/purchase order number=" & amp; SQLtext (Me. OpenArgs)
The Set of RST=OpenADORecordset (strSQL, CNN)
Me! [state]=RST! [state]
Me! [the purchase order number]=RST! [the purchase order number]
Me! [purchase date]=RST! [purchase date]
Me! [vendor ID]=RST! [vendor ID]
Me! [agent]=RST! (agent)
Me! [make]=RST! [make]
Me! Time [make]=RST! [make] time
Me! [reviewer]=RST! [reviewer]
Me! Review [time]=RST! Review [time]
Me! [note]=RST! [note]
RST. Close
StrSQL="SELECT * FROM [the purchase order query _ subsidiary] WHERE/purchase order number=" & amp; SQLtext (Me! [the purchase order date])
The Set of RST=OpenADORecordset (strSQL, CNN)
The Set rstTmp=CurrentDb. OpenRecordset (" TMP_ purchase order list ")
EOF
Do Until RST.RstTmp. AddNew
RstTmp! [the purchase order number]=RST! [the purchase order number]
RstTmp! [commodity ID]=RST! [commodity ID]
RstTmp! [commodity specification]=RST! [commodity specification]
RstTmp! [unit]=RST! [unit]
RstTmp! [number]=RST! [number]
RstTmp! [price]=RST! [price]
RstTmp. Update
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull