frmproduct_details.frm

英文版Access数据库编程

    End If
    txtDescription.Enabled = True
    cmbCategory.Enabled = True
    cmbBrand.Enabled = True
    txtUnitPrice.Enabled = True
    txtMinimum.Enabled = True
    txtReorder.Enabled = True
    txtLocation.Enabled = True
    lvHistory.Enabled = False
    
    cmdCancel.Visible = True
    cmdSave.Visible = True
    cmdEdit.Visible = False
    cmdClose.Visible = False
Else
    txtProductID.Locked = True
    txtQuantity.Locked = True
    txtDescription.Enabled = False
    cmbCategory.Enabled = False
    cmbBrand.Enabled = False
    txtUnitPrice.Enabled = False
    txtMinimum.Enabled = False
    txtReorder.Enabled = False
    txtLocation.Enabled = False
    lvHistory.Enabled = True
    cmdCancel.Visible = False
    cmdSave.Visible = False
    cmdEdit.Visible = True
    cmdClose.Visible = True
    
End If
End Sub

Private Sub setTabs()
With tb
    .Tabs.Clear
    .Tabs.add , "Sales", "Sales"
    .Tabs.add , "Purchases", "Purchases"
    .Tabs.add , "Transactions", "Transactions"
    If CurrentUser.prvlgAdmin = True Then
        .Tabs.add , "PricesOnly", "Unit Price"
        .Tabs.add , "CostOnly", "Unit Cost"
    End If
End With
End Sub

Private Sub showRecords(ByRef strRecordset As Recordset)
Dim i As Integer
With lvHistory
    .ColumnHeaders.Clear
    .ListItems.Clear
    For i = 0 To strRecordset.Fields.Count - 1
        If strRecordset.Fields(i).Name = "qty" Then
            .ColumnHeaders.add , , "Quantity", 970
        ElseIf strRecordset.Fields(i).Name = "isIn" Then
            .ColumnHeaders.add , , "Type", 900
        ElseIf strRecordset.Fields(i).Name = "ProductID" Then
            .ColumnHeaders.add , , "ProductID", 0
        Else
            .ColumnHeaders.add , , strRecordset.Fields(i).Name
        End If
    Next i
    While Not strRecordset.EOF
        For i = 0 To strRecordset.Fields.Count - 1
            If i = 0 Then
                .ListItems.add , , IIf(IsNull(strRecordset.Fields(i).Value), "", strRecordset.Fields(i).Value)
            Else
                If strRecordset.Fields(i).Name = "SalePrice" Then
                    .ListItems(.ListItems.Count).SubItems(i) = Format$(IIf(IsNull(strRecordset.Fields(i).Value), "0.00", strRecordset.Fields(i).Value), "#,##0.00")
                ElseIf strRecordset.Fields(i).Name = "isIn" Then
                    If strRecordset("isIn") = True Then
                        .ListItems(.ListItems.Count).SubItems(i) = "In"
                    Else
                        .ListItems(.ListItems.Count).SubItems(i) = "Out"
                    End If
                Else
                    .ListItems(.ListItems.Count).SubItems(i) = IIf(IsNull(strRecordset.Fields(i).Value), "", strRecordset.Fields(i).Value)
                End If
            End If
        Next i
        strRecordset.MoveNext
    Wend
End With
End Sub

Public Sub getProductDetails(ByVal strProductID As String)
Dim getRS As Recordset, getSQL As String
Me.Caption = strProductID
getSQL = "SELECT * FROM Products WHERE Products.ProductID='" & strProductID & "';"
RSOpen getRS, getSQL, dbOpenSnapshot
If Not getRS.EOF Then
    txtProductID.Text = getRS("ProductID")
    cmbCategory.Text = getRS("CategoryID")
    cmbBrand.Text = getRS("Brand")
    txtDescription.Text = getRS("Description")
    txtQuantity.Text = getRS("Quantity")
    txtMinimum.Text = getRS("MinLevel")
    txtReorder.Text = IIf(IsNull(getRS("ReorderLevel")), "0", getRS("ReorderLevel"))
    txtUnitPrice.Text = Format$(getRS("UnitPrice"), "#,##0.00")
    txtLocation.Text = IIf(IsNull(getRS("Location")), "", getRS("Location"))
End If
getRS.Close
Set getRS = Nothing
End Sub

Private Sub Form_Load()
FillCombo cmbCategory, "SELECT Categories.CategoryID FROM Categories ORDER BY CategoryID ASC;", "CategoryID"
FillCombo cmbBrand, "SELECT DISTINCT Products.Brand FROM Products ORDER BY Brand ASC;", "Brand"
setTabs
FormMode Viewing
End Sub

Private Sub tb_Click()
Dim tempSQL As String
tempSQL = ""
With tb
    Select Case .SelectedItem.Key
        Case "Sales"
            tempSQL = "SELECT Delivery.Date, Delivery.DOnumber, Customers.Name, D_Details.Quantity, D_Details.UnitLabel, D_Details.SalePrice " & _
                    "FROM Customers INNER JOIN (Delivery INNER JOIN D_Details ON Delivery.DOnumber = D_Details.DOnumber) ON Customers.CustomerID = Delivery.CustomerID " & _
                    "WHERE (((D_Details.ProductID)='" & Me.Caption & "') AND ((D_Details.isInvoiced)=True));"
        Case "Purchases"
        
        Case "Transactions"
            tempSQL = "SELECT * FROM internal_transaction WHERE ProductID='" & Me.Caption & "';"
        Case "PricesOnly"
            tempSQL = "SELECT DISTINCT Delivery.Date, D_Details.SalePrice " & _
                        "FROM Delivery INNER JOIN D_Details ON Delivery.DOnumber = D_Details.DOnumber " & _
                        "WHERE (((D_Details.ProductID)='" & Me.Caption & "'));"
        Case "CostOnly"
            tempSQL = "SELECT DISTINCT Purchase.Date, P_Details.UnitPrice " & _
                        "FROM Purchase INNER JOIN P_Details ON Purchase.poNumber = P_Details.poNumber " & _
                        "WHERE (((P_Details.ProductID)='" & Me.Caption & "'));"
    End Select
    
    If tempSQL <> "" Then
        Dim reportRS As Recordset
        RSOpen reportRS, tempSQL, dbOpenSnapshot
        showRecords reportRS
        reportRS.Close
        Set reportRS = Nothing
    End If
End With
End Sub

Private Sub cmbBrand_LostFocus()
CapCon cmbBrand
End Sub

Private Sub cmbCategory_LostFocus()
CapCon cmbCategory
End Sub

Private Sub cmdCancel_Click()
txtProductID = txtProductID.Tag
txtDescription.Text = txtDescription.Tag
cmbBrand.Text = cmbBrand.Tag
cmbCategory.Text = cmbCategory.Tag
txtMinimum.Text = txtMinimum.Tag
txtReorder.Text = txtReorder.Tag
txtLocation.Text = txtLocation.Tag
txtUnitPrice.Text = txtUnitPrice.Tag

FormMode Viewing
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdEdit_Click()
txtProductID.Tag = txtProductID
txtDescription.Tag = txtDescription.Text
cmbBrand.Tag = cmbBrand.Text
cmbCategory.Tag = cmbCategory.Text
txtMinimum.Tag = txtMinimum.Text
txtReorder.Tag = txtReorder.Text
txtLocation.Tag = txtLocation.Text
txtUnitPrice.Tag = txtUnitPrice.Text

FormMode Editing
End Sub

Private Sub cmdSave_Click()
If txtDescription.Text = "" Then
    ValidMsg "Please provide a description for this product.", "Missing description"
    txtDescription.SetFocus
ElseIf cmbCategory.Text = "" Then
    ValidMsg "Please select a category.", "Missing category"
    cmbCategory.SetFocus
ElseIf txtMinimum.Text = "" Then
    ValidMsg "Please enter a minimum level for this product.", "Missing minimum level"
    txtMinimum.SetFocus
ElseIf Val(txtMinimum.Text) > 30000 Then
    ValidMsg "Please enter a value between 0 to 30000 for the minimum level.", "Invalid value"
    txtMinimum.SetFocus
ElseIf Val(txtReorder.Text) > 30000 Then
    ValidMsg "Please enter a value between 0 to 30000 for the reorder level.", "Invalid value"
    txtReorder.SetFocus
Else
    Dim pRS As Recordset, pSQL As String
    pSQL = "SELECT * FROM Products WHERE ProductID='" & Me.Caption & "';"
    RSOpen pRS, pSQL, dbOpenDynaset
    If Not pRS.EOF Then
        pRS.Edit
        pRS("ProductID") = txtProductID.Text
        pRS("Description") = txtDescription.Text
        pRS("Brand") = cmbBrand.Text
        pRS("CategoryID") = cmbCategory.Text
        pRS("MinLevel") = txtMinimum.Text
        pRS("ReorderLevel") = txtReorder.Text
        pRS("Location") = txtLocation.Text
        pRS("UnitPrice") = txtUnitPrice.Text
        pRS.Update
        insertLog "Product ID: " & Me.Caption & " has been updated."
        InfoMsg "The product details have been successfully updated.", "Record saved"
    End If
    pRS.Close
    Set pRS = Nothing
    FormMode Viewing
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set frmProduct_Details = Nothing
End Sub

Private Sub txtDescription_GotFocus()
SelText txtDescription
End Sub

Private Sub txtDescription_LostFocus()
CapCon txtDescription
End Sub

Private Sub txtLocation_GotFocus()
SelText txtLocation
End Sub

Private Sub txtMinimum_GotFocus()
SelText txtMinimum
End Sub

Private Sub txtMinimum_KeyPress(KeyAscii As Integer)
OnlyNum KeyAscii
End Sub

Private Sub txtMinimum_LostFocus()
If txtMinimum.Text = "" Then
    txtMinimum.Text = "0"
End If
End Sub

Private Sub txtProductID_GotFocus()
SelText txtProductID
End Sub

Private Sub txtQuantity_GotFocus()
SelText txtQuantity
End Sub

Private Sub txtQuantity_KeyPress(KeyAscii As Integer)
OnlyNum KeyAscii
End Sub

Private Sub txtReorder_GotFocus()
SelText txtReorder
End Sub

Private Sub txtReorder_KeyPress(KeyAscii As Integer)
OnlyNum KeyAscii
End Sub

Private Sub txtReorder_LostFocus()
If txtReorder.Text = "" Then
    txtReorder.Text = "0"
End If
End Sub

Private Sub txtUnitPrice_GotFocus()
SelText txtUnitPrice
End Sub

Private Sub txtUnitPrice_KeyPress(KeyAscii As Integer)
If KeyAscii <> Asc(".") Then
    OnlyNum KeyAscii
End If
End Sub

Private Sub txtUnitPrice_LostFocus()
If txtUnitPrice.Text = "" Then
    txtUnitPrice.Text = "0"
End If
txtUnitPrice.Text = Format$(txtUnitPrice.Text, "#,##0.00")
End Sub