frmupdateallrelationshiprecords.frm

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

'存儲[製品名稱]到緩存中
Public Property Let ProductName(ByVal strProductName As String)
    pProductName = strProductName
End Property

'6.獲取存儲到緩存的[訂單日期]
Public Property Get OrderformDate() As String
    OrderformDate = pOrderformDate
End Property
'存儲[訂單日期]到緩存中
Public Property Let OrderformDate(ByVal strOrderformDate As String)
    pOrderformDate = strOrderformDate
End Property

'獲取存儲到緩存的[交貨日期]
Public Property Get DeliveryDate() As String
    DeliveryDate = pDeliveryDate
End Property
'存儲[交貨日期]到緩存中
Public Property Let DeliveryDate(ByVal strDeliveryDate As String)
    pDeliveryDate = strDeliveryDate
End Property


'設置控件顯示狀態
Private Function SetControlsShowStatus(ByVal Index As Integer) As Boolean
On Error GoTo ErrorHandler
    txtSalesSlipNo.Enabled = False
    txtDesignStyleNo.Enabled = False
    txtOrderformID.Enabled = False
    txtProductQuantity.Enabled = False
    txtProductName.Enabled = False
    dptOrderformDate.Enabled = False
    dptDeliveryDate.Enabled = False
    cmdSalesSlipNo.Enabled = False
    cmdDesignStyleNo.Enabled = False
    cmdOrderformID.Enabled = False
    cmdProductQuantity.Enabled = False
    cmdProductName.Enabled = False
    cmdOrderformDate.Enabled = False
    cmdDeliveryDate.Enabled = False
    txtSalesSlipNo.BackColor = vbButtonFace
    txtDesignStyleNo.BackColor = vbButtonFace
    txtOrderformID.BackColor = vbButtonFace
    txtProductQuantity.BackColor = vbButtonFace
    txtProductName.BackColor = vbButtonFace
    Select Case Index
        Case 0  '
        Case 1  '1.[銷貨單號]
            txtSalesSlipNo.Enabled = True
            cmdSalesSlipNo.Enabled = True
            txtSalesSlipNo.BackColor = vbWindowBackground
        Case 2  '2.[製品款號]
            txtDesignStyleNo.Enabled = True
            cmdDesignStyleNo.Enabled = True
            txtDesignStyleNo.BackColor = vbWindowBackground
        Case 3 '3.[訂單編號]
            txtOrderformID.Enabled = True
            cmdOrderformID.Enabled = True
            txtOrderformID.BackColor = vbWindowBackground
        Case 4 '4.[製品數量]
            txtProductQuantity.Enabled = True
            cmdProductQuantity.Enabled = True
            txtProductQuantity.BackColor = vbWindowBackground
        Case 5 '5.[製品名稱]
            txtProductName.Enabled = True
            cmdProductName.Enabled = True
            txtProductName.BackColor = vbWindowBackground
        Case 6 '6.[訂單日期]
            dptOrderformDate.Enabled = True
            cmdOrderformDate.Enabled = True
        Case 7 '7.[交貨日期]
            dptDeliveryDate.Enabled = True
            cmdDeliveryDate.Enabled = True
    End Select
    SetControlsShowStatus = True
Exit Function
ErrorHandler:
    SetControlsShowStatus = False
End Function

Private Sub Form_Load()
    '裝載字體設置
    frmUpdateAllRelationshipRecords_CharacterShow
    '設置控件顯示狀態
    SetControlsShowStatus 0
End Sub

'1.[銷貨單號]
Private Sub optSalesSlipNo_Click()
    SetControlsShowStatus 1
End Sub
'2.[製品款號]
Private Sub optDesignStyleNo_Click()
    SetControlsShowStatus 2
End Sub
'3.[訂單編號]
Private Sub optOrderformID_Click()
    SetControlsShowStatus 3
End Sub
'4.[製品數量]
Private Sub optProductQuantity_Click()
    SetControlsShowStatus 4
End Sub
'5.[製品名稱]
Private Sub optProductName_Click()
    SetControlsShowStatus 5
End Sub
'6.[訂單日期]
Private Sub optOrderformDate_Click()
    SetControlsShowStatus 6
End Sub
'7.[交貨日期]
Private Sub optDeliveryDate_Click()
    SetControlsShowStatus 7
End Sub
'退齣繫統
Private Sub cmdComplete_Click()
    Unload Me
End Sub
Private Sub Form_Terminate()
    Set frmUpdateAllRelationshipRecords = Nothing
End Sub

'****************************************************************************************************
'*
'*                                   脩改[銷貨單號]
'*
'****************************************************************************************************
'判斷客戶訂單錶CustomerOrderform中是否已存在相同的記錄
Private Function CheckDifferntRecordByTableCustomerOrderform(ByVal strSalesSlipNo As String) As Boolean
On Error GoTo ErrorHandler
    Set pCmd.ActiveConnection = pCnn
    pCmd.CommandType = adCmdText
    pCmd.CommandText = _
    "SELECT  * FROM   CustomerOrderform " & _
    "WHERE " & "SalesSlipNo = '" & strSalesSlipNo & "'"
    Set pRs = pCmd.Execute
    If (Not pRs.BOF) And (Not pRs.EOF) Then '存在相同的記錄
       CheckDifferntRecordByTableCustomerOrderform = True
    Else                                    '沒有相同的記錄
       CheckDifferntRecordByTableCustomerOrderform = False
    End If
    Set pCmd = Nothing
Exit Function
ErrorHandler:
    Set pCmd = Nothing
    CheckDifferntRecordByTableCustomerOrderform = False
End Function
Private Function UpdateRecordForSalesSlipNoOfCustomerOrderform( _
                               ByVal strFieldName As String, _
                               ByVal strFieldValue As String, _
                               ByVal strFieldConditionExpression As String) As Boolean
On Error GoTo ErrorHandler
    Set pCmd.ActiveConnection = pCnn
    pCmd.CommandType = adCmdText
    pCmd.CommandText = _
        "UPDATE  CustomerOrderform " & _
        "SET " & strFieldName & "='" & strFieldValue & "' " & _
        "WHERE " & "SalesSlipNo='" & strFieldConditionExpression & "'"
    pCmd.Execute
    Set pCmd = Nothing
    UpdateRecordForSalesSlipNoOfCustomerOrderform = True
Exit Function
ErrorHandler:
    Set pCmd = Nothing
    UpdateRecordForSalesSlipNoOfCustomerOrderform = False
End Function
Private Function UpdateRecordForSalesSlipNoOfMaterielOrderform( _
                               ByVal strFieldName As String, _
                               ByVal strFieldValue As String, _
                               ByVal strFieldConditionExpression As String) As Boolean
On Error GoTo ErrorHandler
    Set pCmd.ActiveConnection = pCnn
    pCmd.CommandType = adCmdText
    pCmd.CommandText = _
        "UPDATE  MaterielOrderform " & _
        "SET " & strFieldName & "='" & strFieldValue & "' " & _
        "WHERE " & "SalesSlipNo='" & strFieldConditionExpression & "'"
    pCmd.Execute
    Set pCmd = Nothing
    UpdateRecordForSalesSlipNoOfMaterielOrderform = True
Exit Function
ErrorHandler:
    Set pCmd = Nothing
    UpdateRecordForSalesSlipNoOfMaterielOrderform = False
End Function
Private Sub cmdSalesSlipNo_Click()
    If Not Len(txtSalesSlipNo.Text) > 0 Then
       MsgBox "【銷貨單號】不能為空,且必須唯一！", vbExclamation, "提醒"
       txtSalesSlipNo.SetFocus
       Exit Sub
    End If
    '判斷客戶訂單錶CustomerOrderform中是否已存在相同的記錄
    If CheckDifferntRecordByTableCustomerOrderform(Trim$(txtSalesSlipNo.Text)) = True Then
       MsgBox "【銷貨單號】" & txtSalesSlipNo.Text & " 已經存在,請指定其它名稱！", vbCritical, "錯誤"
       Exit Sub
    Else
      '讀取緩存中的[銷貨單號]值,並脩改客戶訂單錶CustomerOrderform
      '中所有與指定[銷貨單號]相關的全部記錄
      If UpdateRecordForSalesSlipNoOfCustomerOrderform( _
         "SalesSlipNo", _
         Trim$(Me.txtSalesSlipNo.Text), _
         Me.SalesSlipNo) = True Then
         '成功,則脩改訂單錶MaterielOrderform中所有與指定[銷貨單號]相關的全部記錄
         If UpdateRecordForSalesSlipNoOfMaterielOrderform( _
            "SalesSlipNo", _
            Trim$(Me.txtSalesSlipNo.Text), _
            Me.SalesSlipNo) = True Then
            '成功,則顯示脩改成功信息,並將已脩改的新的[銷貨單號]值存入[銷貨單號]緩存Me.SalesSlipNo中
            MsgBox "【銷貨單號】" & Me.SalesSlipNo & " 被成功的脩改為 " & _
                     Me.txtSalesSlipNo.Text, vbExclamation, "恭喜"
            '//將已脩改的新的[銷貨單號]值存入[銷貨單號]緩存Me.SalesSlipNo中//
            Me.SalesSlipNo = Me.txtSalesSlipNo.Text
         Else '失敗,則還原客戶訂單錶CustomerOrderform中被脩改暸的[銷貨單號]
            UpdateRecordForSalesSlipNoOfCustomerOrderform _
            "SalesSlipNo", _
            Me.SalesSlipNo, _
            Trim$(Me.txtSalesSlipNo.Text)
            MsgBox "將【銷貨單號】" & Me.SalesSlipNo & _
                   " 脩改為 " & txtSalesSlipNo.Text & _
                   " 失敗！", vbCritical, "脩改失敗"
         End If
      Else
         MsgBox "將【銷貨單號】" & Me.SalesSlipNo & _
                " 脩改為 " & txtSalesSlipNo.Text & _
                " 失敗！", vbCritical, "脩改失敗"
      End If
    End If
End Sub

'****************************************************************************************************
'*
'*                                   脩改[製品款號]
'*
'****************************************************************************************************
Private Function UpdateRecordForDesignStyleNoOfCustomerOrderform( _
                               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
    UpdateRecordForDesignStyleNoOfCustomerOrderform = True
Exit Function
ErrorHandler:
    Set pCmd = Nothing
    UpdateRecordForDesignStyleNoOfCustomerOrderform = False
End Function
Private Function UpdateRecordForDesignStyleNoOfMaterielOrderform( _
                               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  MaterielOrderform " & _
        "SET " & strFieldName & "='" & strFieldValue & "' " & _
        "WHERE " & "SalesSlipNo='" & strFieldConditionExpression1 & "'" & " AND " & _
                   "DesignStyleNo='" & strFieldConditionExpression2 & "'"
    pCmd.Execute
    Set pCmd = Nothing
    UpdateRecordForDesignStyleNoOfMaterielOrderform = True
Exit Function
ErrorHandler:
    Set pCmd = Nothing
    UpdateRecordForDesignStyleNoOfMaterielOrderform = False
End Function
Private Sub cmdDesignStyleNo_Click()
    If Not Len(txtDesignStyleNo.Text) > 0 Then
       MsgBox "【製品款號】不能為空！", vbExclamation, "提醒"
       txtDesignStyleNo.SetFocus
       Exit Sub
    End If
    '讀取緩存中的[銷貨單號]和[製品款號]值,並脩改客戶訂單錶CustomerOrderform
    '中所有與指定[製品款號]相關的全部記錄
    If UpdateRecordForDesignStyleNoOfCustomerOrderform( _
       "DesignStyleNo", _
       Trim$(Me.txtDesignStyleNo.Text), _
       Me.SalesSlipNo, _
       Me.DesignStyleNo) = True Then
       '成功,則脩改訂單錶MaterielOrderform中所有與指定[製品款號]相關的全部記錄
       If UpdateRecordForDesignStyleNoOfMaterielOrderform( _
          "DesignStyleNo", _
          Trim$(Me.txtDesignStyleNo.Text), _
          Me.SalesSlipNo, _
          Me.DesignStyleNo) = True Then
          '成功,則顯示脩改成功信息,並將已脩改的新的[製品款號]值存入緩存Me.DesignStyleNo中