发货单中的订单明细是订单子表中的明细序号字段，是订单子表主键，是唯一不重复的。
如何在发货执行添加或修改记录时，通过订单明细（即明细序号）来加载订单子表记录中的订单状态和完成日期到发货临时表中。
从而可以在发货单上直接修改订单子表上的订单状态和完成日期。
Private Sub Form_Load()
On Error GoTo ErrorHandler
Dim rst As Object
Dim rstTmp As Object
Dim strSQL As String
Dim currentID As String
CurrentDb.Execute "DELETE FROM TMP_发货_Detail"
If IsNull(Me.OpenArgs) Then
Me.DataEntry = True
End If
If Me.DataEntry Then
Exit Sub
End If
urrentID = Form_Frm_发货!Frm_发货_List_Child.Form.发货序号
strSQL = "select * from Tbl_发货 where 发货序号 ='" & currentID & "'"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
Me.发货序号 = currentID
Me.明细主号 = "F" & Mid([currentID], 4, 6)
………………
Me.备注 = rst!备注
rst.Close
strSQL = "select * from Tbl_发货_Detail where 发货序号 ='" & Me.发货序号 & "'"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set rstTmp = CurrentDb.OpenRecordset("TMP_发货_Detail")
Do Until rst.EOF
rstTmp.AddNew
rstTmp![发货序号] = rst![发货序号]
rstTmp![发货单号] = rst![发货单号]
rstTmp![订单明细] = rst![订单明细]
………………
rstTmp![金额] = rst![金额]
rstTmp![结算日期] = rst![结算日期]
rstTmp.Update
rst.MoveNext
Loop
rst.Close
rstTmp.Close
Me.Frm_发货_Edit_Detail_Child.Requery
ExitHere:
Set rst = Nothing
Set rstTmp = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbCritical
Resume ExitHere
End Sub
Private Sub Cmd保存_Click()
If Me.DataEntry Then
Call TJ
Me.Frm_发货_Edit_Detail_Child.Requery
Else
Call XG
DoCmd.Close acForm, Me.Name, acSaveNo
DoCmd.Restore
End If
End Sub
Public Sub TJ()
Dim rst As Object
Dim rstTmp As Object
Dim strSQL As String
Dim currentID As String
Dim currentID1 As String
currentID = AutoNumStr("Tbl_发货", "发货序号", 6, "No:", "") '用编号模块输入发货序号。
strSQL = "select * from Tbl_发货 "
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rst.AddNew
rst!发货序号 = currentID
…………
rst![备注] = Me![备注]
rst.Update
rst.Close
strSQL = "select * from Tbl_发货_Detail WHERE [发货序号]='" & Me![发货序号] & "'"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set rstTmp = CurrentDb.OpenRecordset("TMP_发货_Detail")
Do Until rstTmp.EOF
rst.AddNew
rst![发货序号] = currentID
rst![订单明细] = rstTmp![订单明细]
…………
rst![数量] = rstTmp![数量]
rst![单价] = rstTmp![单价]
rst![金额] = Round(rstTmp![数量] * rstTmp![单价], 2)
rst.Update
rstTmp.MoveNext
Loop
rst.Close
rstTmp.Close
'将输入框清空
Dim ctrl As Control
For Each ctrl In Me.Form.Controls
If (TypeOf ctrl Is TextBox And InStr(1, ctrl.Name, "合计") = 0) Or TypeOf ctrl Is ComboBox Then
ctrl = Null
End If
Next ctrl
CurrentDb.Execute "DELETE FROM TMP_发货_Detail"
Form_Frm_发货.Frm_发货_List_Child.Form.Requery
MsgBox "新增的记录保存成功！", vbInformation, "提示"
ExitHere:
Set rst = Nothing
Set rstTmp = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbCritical
Resume ExitHere
End Sub
Public Sub XG()
Dim rst As Object
Dim rstTmp As Object
Dim strSQL As String
Dim currentID As String
strSQL = "select * from Tbl_发货 where 发货序号 ='" & Me.发货序号 & "'"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
rst.Edit
rst![审核] = Me.[审核]
…………
rst![备注] = Me![备注]
rst.Update
rst.Close
CurrentDb.Execute "Delete from Tbl_发货_Detail WHERE [发货序号]='" & Me![发货序号] & "'"
strSQL = "select * from Tbl_发货_Detail WHERE [发货序号]='" & Me![发货序号] & "'"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set rstTmp = CurrentDb.OpenRecordset("TMP_发货_Detail")
Do Until rstTmp.EOF
rst.AddNew
rst![发货序号] = Me![发货序号]
rst![订单明细] = rstTmp![订单明细]
………………
rst![数量] = rstTmp![数量]
rst![单价] = rstTmp![单价]
rst![金额] = Round(rstTmp![数量] * rstTmp![单价], 2)
rst.Update
rstTmp.MoveNext
Loop
rst.Close
rstTmp.Close
MsgBox "修改后的记录保存成功！", vbInformation, "提示"
ExitHere:
Set rst = Nothing
Set rstTmp = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbCritical
Resume ExitHere
End Sub
发货子窗体代码：
Option Compare Database
Option Explicit
Private mclsSC订单明细 As New SearchComboBox
Private Sub Form_Load()
mclsSC订单明细.Init Combo:=Me.订单明细, _
SearchField:="物品编号 & 物品名称 & 规格型号 & 材质", _
SQLSELECT:="明细序号,订单编号,物品编号,物品名称,规格型号,材质,物品种类,物品类别,客户采购单号,数量,单位,单价,含税,交货日期,客户名称,客户编号,订单状态", _
SQLFROM:="Qry_订单", _
SQLWHERE:="订单状态='生产'and Qry_订单.客户编号=[Forms]![Frm_发货_Edit]![客户编号]", _
SQLORDERBY:="物品名称 & 订单编号"
End Sub
Private Sub 订单明细_AfterUpdate()
Me.订单明细 = Me.订单明细.Column(0)
Me.订单编号 = Me.订单明细.Column(1)
Me.物品编号 = Me.订单明细.Column(2)
Me.物品名称 = Me.订单明细.Column(3)
Me.规格型号 = Me.订单明细.Column(4)
Me.材质 = Me.订单明细.Column(5)
Me.物品种类 = Me.订单明细.Column(6)
Me.物品类别 = Me.订单明细.Column(7)
Me.客户采购单号 = Me.订单明细.Column(8)
Me.数量 = Me.订单明细.Column(9)
Me.单位 = Me.订单明细.Column(10)
Me.单价 = Me.订单明细.Column(11)
Me.含税 = Me.订单明细.Column(12)
Me.数量.SetFocus
Me.发货单号 = Me.Parent.发货单号
End Sub