frmupdateallrelationshiprecords.frm

公司订单管理系统,这对于方便公司管理床单

          MsgBox "【製品款號】" & Me.DesignStyleNo & " 被成功的脩改為 " & _
                   Me.txtDesignStyleNo.Text, vbExclamation, "恭喜"
          '//將已脩改的新的[製品款號]值存入緩存Me.DesignStyleNo中//
          Me.DesignStyleNo = Trim$(Me.txtDesignStyleNo.Text)
       Else '失敗,則還原客戶訂單錶CustomerOrderform中被脩改暸的[製品款號]
          UpdateRecordForDesignStyleNoOfCustomerOrderform _
          "DesignStyleNo", _
          Me.DesignStyleNo, _
          Me.SalesSlipNo, _
          Trim$(Me.txtDesignStyleNo.Text)
          MsgBox "將【製品款號】" & Me.DesignStyleNo & _
                 " 脩改為 " & txtDesignStyleNo.Text & _
                 " 失敗！", vbCritical, "脩改失敗"
       End If
    Else
       MsgBox "將【製品款號】" & Me.DesignStyleNo & _
              " 脩改為 " & txtDesignStyleNo.Text & _
              " 失敗！", vbCritical, "脩改失敗"
    End If
End Sub

'****************************************************************************************************
'*
'*                                   脩改[製品數量]
'*
'****************************************************************************************************
Private Function UpdateRecordForProductQuantityOfCustomerOrderform( _
                               ByVal strFieldName As String, _
                               ByVal lngFieldValue As Long, _
                               ByVal strFieldConditionExpression1 As String, _
                               ByVal strFieldConditionExpression2 As String) As Boolean
On Error GoTo ErrorHandler
    Set pCmd.ActiveConnection = pCnn
    pCmd.CommandType = adCmdText
    pCmd.CommandText = _
        "UPDATE  CustomerOrderform " & _
        "SET " & strFieldName & "=" & lngFieldValue & " " & _
        "WHERE " & "SalesSlipNo='" & strFieldConditionExpression1 & "'" & " AND " & _
                   "DesignStyleNo='" & strFieldConditionExpression2 & "'"
    pCmd.Execute
    Set pCmd = Nothing
    UpdateRecordForProductQuantityOfCustomerOrderform = True
Exit Function
ErrorHandler:
    Set pCmd = Nothing
    UpdateRecordForProductQuantityOfCustomerOrderform = False
End Function
Private Function UpdateRecordForMaterielOfMaterielOrderform( _
                               ByVal lngProductQuantity As Long, _
                               ByVal strFieldConditionExpression1 As String, _
                               ByVal strFieldConditionExpression2 As String) As Boolean
On Error GoTo ErrorHandler
Dim pRsTemp As ADODB.Recordset
    Set pRsTemp = New ADODB.Recordset
    Set pRsTemp.ActiveConnection = pCnn '将Recordset对象实例绑訂到一个已经打开的数据库物理连接
    pRsTemp.CursorType = adOpenStatic   '设置游标类型:静态游标，其他用户对表的记录进行删除和修改不可见
    pRsTemp.LockType = adLockOptimistic '脩改鎖類型默認的隻讀锁為开放锁,以便能對記錄集進行脩改,刪除,更新操作
    pRsTemp.Source = "SELECT * FROM MaterielOrderform " & _
                      "WHERE " & "SalesSlipNo='" & strFieldConditionExpression1 & "'" & " AND " & _
                                 "DesignStyleNo='" & strFieldConditionExpression2 & "'"
    pRsTemp.Open , , , , adCmdText
    If (Not pRsTemp.BOF) And (Not pRsTemp.EOF) Then
       pRsTemp.MoveFirst
       Do While Not pRsTemp.EOF
          pRsTemp("SalesSlipNo").Value = pRsTemp("SalesSlipNo").Value
          pRsTemp("DesignStyleNo").Value = pRsTemp("DesignStyleNo").Value
          pRsTemp("HaiKwanID").Value = pRsTemp("HaiKwanID").Value
          pRsTemp("mMaterielName").Value = pRsTemp("mMaterielName").Value
          pRsTemp("mConsumePercentage").Value = pRsTemp("mConsumePercentage").Value
          pRsTemp("mQuantityUsed").Value = pRsTemp("mQuantityUsed").Value
          pRsTemp("QUUnits").Value = pRsTemp("QUUnits").Value
          pRsTemp("mSumQuantity").Value = Round(lngProductQuantity * pRsTemp("mQuantityUsed").Value * _
                                         (1 + pRsTemp("mConsumePercentage").Value), 5) '脩改[所需數量]
          pRsTemp("QSUnits").Value = pRsTemp("QSUnits").Value
          pRsTemp("mOrderMaterielDate").Value = pRsTemp("mOrderMaterielDate").Value
          pRsTemp("mStockingMaterielNo").Value = pRsTemp("mStockingMaterielNo").Value
          pRsTemp("mMaterielPrice").Value = pRsTemp("mMaterielPrice").Value
          pRsTemp("MPUnits").Value = pRsTemp("MPUnits").Value
          pRsTemp("mAmount").Value = Round(pRsTemp("mMaterielPrice").Value * pRsTemp("mSumQuantity").Value, 5) '脩改[總計價格]
          pRsTemp("MAUnits").Value = pRsTemp("MAUnits").Value
          pRsTemp("mRecMaterielDate").Value = pRsTemp("mRecMaterielDate").Value
          pRsTemp("mMaterielSource").Value = pRsTemp("mMaterielSource").Value
          pRsTemp("EditTimes").Value = pRsTemp("EditTimes").Value
          pRsTemp("EditDate").Value = pRsTemp("EditDate").Value
          pRsTemp("Content").Value = pRsTemp("Content").Value
          pRsTemp.Update
          pRsTemp.MoveNext
       Loop
       UpdateRecordForMaterielOfMaterielOrderform = True
    Else
       UpdateRecordForMaterielOfMaterielOrderform = False
    End If
    pRsTemp.Close
    Set pRsTemp = Nothing
Exit Function
ErrorHandler:
   ' pRsTemp.Close
    Set pRsTemp = Nothing
    UpdateRecordForMaterielOfMaterielOrderform = False
End Function
Private Sub cmdProductQuantity_Click()
    If Not Len(txtProductQuantity.Text) > 0 Then
       MsgBox "【製品數量】不能為空,且必須是數字！", vbExclamation, "提醒"
       Me.txtProductQuantity.SetFocus
       Exit Sub
    End If
    '讀取緩存中的[銷貨單號]和[製品款號]值,並脩改客戶訂單錶CustomerOrderform
    '中所有與指定[製品數量]相關的全部記錄
    If UpdateRecordForProductQuantityOfCustomerOrderform( _
       "ProductQuantity", _
       CLng(Val(Trim$(Me.txtProductQuantity.Text))), _
       Me.SalesSlipNo, _
       Me.DesignStyleNo) = True Then
       '成功,則脩改訂單錶MaterielOrderform中所有與指定[製品數量]相關的全部記錄
       If UpdateRecordForMaterielOfMaterielOrderform( _
          CLng(Val(Trim$(Me.txtProductQuantity.Text))), _
          Me.SalesSlipNo, _
          Me.DesignStyleNo) = True Then
          '成功,則顯示脩改成功信息,並將已脩改的新的[製品數量]值存入緩存Me.ProductQuantity中
          MsgBox "【製品數量】" & Me.ProductQuantity & " 被成功的脩改為 " & _
                   Me.txtProductQuantity.Text, vbExclamation, "恭喜"
          '//將已脩改的新的[製品數量]值存入緩存Me.ProductQuantity中//
          Me.ProductQuantity = Trim$(Me.txtProductQuantity.Text)
       Else '失敗,則還原客戶訂單錶CustomerOrderform中被脩改暸的[製品數量]
          UpdateRecordForProductQuantityOfCustomerOrderform _
          "ProductQuantity", _
          CLng(Val(Me.ProductQuantity)), _
          Me.SalesSlipNo, _
          Me.DesignStyleNo
          MsgBox "將【製品數量】" & Me.ProductQuantity & _
                 " 脩改為 " & txtProductQuantity.Text & _
                 " 失敗！", vbCritical, "脩改失敗"
       End If
    Else
       MsgBox "將【製品數量】" & Me.ProductQuantity & _
              " 脩改為 " & txtProductQuantity.Text & _
              " 失敗！", vbCritical, "脩改失敗"
    End If
End Sub
Private Sub txtProductQuantity_KeyPress(KeyAscii As Integer)
    'KeyAscii 32 以下是一些控制鍵，攔截會造成操作障礙
    If KeyAscii >= 33 Then
        If (KeyAscii <= vbKey9 And KeyAscii >= vbKey0) Then
        Else
           '把 KeyAscii 設為 0 就是取消輸入
           KeyAscii = 0
        End If
    End If
End Sub


'****************************************************************************************************
'*
'*                                   脩改[訂單編號]和[製品名稱]
'*
'****************************************************************************************************
Private Function UpdateRecordForOrderformIDAndProductName( _
                               ByVal strFieldName As String, _
                               ByVal strFieldValue As String, _
                               ByVal strFieldConditionExpression1 As String, _
                               ByVal strFieldConditionExpression2 As String) As Boolean
On Error GoTo ErrorHandler
    Set pCmd.ActiveConnection = pCnn
    pCmd.CommandType = adCmdText
    pCmd.CommandText = _
        "UPDATE  CustomerOrderform " & _
        "SET " & strFieldName & "='" & strFieldValue & "' " & _
        "WHERE " & "SalesSlipNo='" & strFieldConditionExpression1 & "'" & " AND " & _
                   "DesignStyleNo='" & strFieldConditionExpression2 & "'"
    pCmd.Execute
    Set pCmd = Nothing
    UpdateRecordForOrderformIDAndProductName = True
Exit Function
ErrorHandler:
    Set pCmd = Nothing
    UpdateRecordForOrderformIDAndProductName = False
End Function
Private Sub cmdOrderformID_Click() '脩改[訂單編號]
    If Not Len(txtOrderformID.Text) > 0 Then
       MsgBox "【訂單編號】不能為空！", vbExclamation, "提醒"
       txtOrderformID.SetFocus
       Exit Sub
    End If
    '讀取緩存中的[銷貨單號]和[製品款號]值,並脩改客戶訂單錶CustomerOrderform
    '中所有與指定[訂單編號]相關的全部記錄
    If UpdateRecordForOrderformIDAndProductName( _
       "OrderformID", _
       Trim$(Me.txtOrderformID.Text), _
       Me.SalesSlipNo, _
       Me.DesignStyleNo) = True Then
       '成功,則顯示脩改成功信息,並將已脩改的新的[訂單編號]值存入緩存Me.OrderformID中
       MsgBox "與【銷貨單號】" & Me.SalesSlipNo & vbCrLf & _
              "相關的【訂單編號】 " & Me.OrderformID & " 被成功地脩改為 " & _
              Me.txtOrderformID.Text, vbExclamation, "恭喜"
       '//將已脩改的新的[訂單編號]值存入緩存Me.OrderformID中//
       Me.OrderformID = Me.txtOrderformID.Text
    Else
       MsgBox "將【訂單編號】" & Me.OrderformID & _
             " 脩改為 " & txtOrderformID.Text & _
             " 失敗！", vbCritical, "脩改失敗"
    End If
End Sub
Private Sub cmdProductName_Click() '脩改[製品名稱]
    If Not Len(txtProductName.Text) > 0 Then
       MsgBox "【製品名稱】不能為空！", vbExclamation, "提醒"
       txtProductName.SetFocus
       Exit Sub
    End If
     '讀取緩存中的[銷貨單號]和[製品款號]值,並脩改客戶訂單錶CustomerOrderform
    '中所有與指定[製品名稱]相關的全部記錄
    If UpdateRecordForOrderformIDAndProductName( _
       "ProductName", _
       Trim$(Me.txtProductName.Text), _
       Me.SalesSlipNo, _
       Me.DesignStyleNo) = True Then
       '成功,則顯示脩改成功信息,並將已脩改的新的[製品名稱]值存入緩存Me.ProductName中
       MsgBox "與【銷貨單號】" & Me.SalesSlipNo & vbCrLf & _
              "相關的【製品名稱】 " & Me.ProductName & " 被成功地脩改為 " & _
              Me.txtProductName.Text, vbExclamation, "恭喜"
       '//將已脩改的新的[製品名稱]值存入緩存Me.ProductName中//
       Me.ProductName = Me.txtProductName.Text
    Else
       MsgBox "將【製品名稱】" & Me.ProductName & _
             " 脩改為 " & txtProductName.Text & _
             " 失敗！", vbCritical, "脩改失敗"
    End If
End Sub


'****************************************************************************************************
'*
'*                                   脩改[訂單日期]和[交貨日期]
'*
'****************************************************************************************************
Private Function UpdateRecordForOrderformDateAndDeliveryDate( _
                               ByVal strFieldName As String, _
                               ByVal strFieldValue As String, _
                               ByVal strFieldConditionExpression1 As String, _
                               ByVal strFieldConditionExpression2 As String) As Boolean
On Error GoTo ErrorHandler
    Set pCmd.ActiveConnection = pCnn
    pCmd.CommandType = adCmdText
    pCmd.CommandText = _
        "UPDATE  CustomerOrderform " & _
        "SET " & strFieldName & "='" & strFieldValue & "' " & _
        "WHERE " & "SalesSlipNo='" & strFieldConditionExpression1 & "'" & " AND " & _
                   "DesignStyleNo='" & strFieldConditionExpression2 & "'"
    pCmd.Execute
    Set pCmd = Nothing
    UpdateRecordForOrderformDateAndDeliveryDate = True
Exit Function
ErrorHandler:
    Set pCmd = Nothing
    UpdateRecordForOrderformDateAndDeliveryDate = False
End Function
Private Sub cmdOrderformDate_Click() '脩改[訂單日期]
    '讀取緩存中的[銷貨單號]和[製品款號]值,並脩改客戶訂單錶CustomerOrderform
    '中所有與指定[訂單日期]相關的全部記錄
    If UpdateRecordForOrderformDateAndDeliveryDate( _
       "OrderformDate", _
       Format$(Me.dptOrderformDate.Value, "YYYY-MM-DD"), _
       Me.SalesSlipNo, _
       Me.DesignStyleNo) = True Then
       '成功,則顯示脩改成功信息,並將已脩改的新的[訂單日期]值存入緩存Me.OrderformDate中
       MsgBox "與【銷貨單號】" & Me.SalesSlipNo & vbCrLf & _
              "相關的【訂單日期】 " & Me.OrderformDate & " 被成功地脩改為 " & _
              Format$(Me.dptOrderformDate.Value, "YYYY-MM-DD"), vbExclamation, "恭喜"
       '//將已脩改的新的[訂單日期]值存入緩存Me.OrderformDate中//
       Me.OrderformDate = Format$(Me.dptOrderformDate.Value, "YYYY-MM-DD")
    Else
       MsgBox "將【訂單日期】" & Me.OrderformDate & _
              " 脩改為 " & Format$(dptOrderformDate.Value, "YYYY-MM-DD") & _
              " 失敗！", vbCritical, "脩改失敗"
    End If
End Sub
Private Sub cmdDeliveryDate_Click() '脩改[交貨日期]
    '讀取緩存中的[銷貨單號]和[製品款號]值,並脩改客戶訂單錶CustomerOrderform
    '中所有與指定[交貨日期]相關的全部記錄
    If UpdateRecordForOrderformDateAndDeliveryDate( _
       "DeliveryDate", _
       Format$(Me.dptDeliveryDate.Value, "YYYY-MM-DD"), _
       Me.SalesSlipNo, _
       Me.DesignStyleNo) = True Then
       '成功,則顯示脩改成功信息,並將已脩改的新的[交貨日期]值存入緩存Me.DeliveryDate中
       MsgBox "與【銷貨單號】" & Me.SalesSlipNo & vbCrLf & _
              "相關的【交貨日期】 " & Me.DeliveryDate & " 被成功地脩改為 " & _
              Format$(Me.dptDeliveryDate.Value, "YYYY-MM-DD"), vbExclamation, "恭喜"
       '//將已脩改的新的[交貨日期]值存入緩存Me.DeliveryDate中//
       Me.DeliveryDate = Format$(Me.dptDeliveryDate.Value, "YYYY-MM-DD")
    Else
       MsgBox "將【交貨日期】" & Me.DeliveryDate & _
              " 脩改為 " & Format$(dptDeliveryDate.Value, "YYYY-MM-DD") & _
              " 失敗！", vbCritical, "脩改失敗"
    End If
End Sub