
'----------------------------------------------------------------
' Hands-On 24-1  - Code for the Products form
'----------------------------------------------------------------

Private Sub Form_Current()
    If Discontinued = True Then
        Me.Section(1).BackColor = 255
        Me.Picture = ""
    Else
        Me.Picture = "C:\Program Files\" & _
            "Microsoft Office\Office12\" & _
            "Bitmaps\Styles\Stone.bmp"
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-2  - Code for the New Customers form
'----------------------------------------------------------------

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.Country = "USA"
End Sub


'----------------------------------------------------------------
' Hands-On 24-3  - Code for the New Customers form
'----------------------------------------------------------------

Private Sub Form_AfterInsert()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Customers")
    
    MsgBox "Added " & rst.RecordCount & _
        "th customer."

    rst.Close
    Set rst = Nothing
    Set db = Nothing
End Sub


'----------------------------------------------------------------
' Hands-On 24-4  - Code for the New Customers form
'----------------------------------------------------------------

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not IsNull(Me.CompanyName) Then
        Me.CustomerID = Left(CompanyName, 3) & _
            Right(CompanyName, 2)
        MsgBox "You just added Customer ID: " & _
            Me.CustomerID
    Else
        MsgBox "Please enter Company Name.", _
            vbOKOnly, "Missing Data"
        Me.CompanyName.SetFocus
        Cancel = True
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-5  - Code for the New Customers form
'----------------------------------------------------------------

Private Sub Form_AfterUpdate()
    Dim fso As FileSystemObject
    Dim objFile As Object
    Dim strFileName As String
    
    On Error Resume Next

    strFileName = "C:\ACC07_ByExample\MyCust.txt"
    Set fso = New FileSystemObject
    Set objFile = fso.GetFile(strFileName)

    If Err.Number = 0 Then
        ' open text file
        Set objFile = fso.OpenTextFile(strFileName, 8)
    Else
        ' create a text file
        Set objFile = fso.CreateTextFile(strFileName)
    End If
    
    objFile.writeLine UCase(Me.CustomerID) & _
        " Created on: " & Date & " " & Time
    objFile.Close
    Set fso = Nothing
    MsgBox "This record was logged in:" & strFileName
End Sub


'----------------------------------------------------------------
' Hands-On 24-6  - Code for the Product Categories form
'----------------------------------------------------------------

Private Sub Form_Dirty(Cancel As Integer)
    If CategoryID <= 4 Then
        MsgBox "You cannot make changes in this record."
        Cancel = True
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-7  - Code for the Product Categories form
'----------------------------------------------------------------

Private Sub Form_Delete(Cancel As Integer)
    If CategoryID <= 8 Then
        MsgBox "You can't delete the original categories."
        Cancel = True
    Else
        If MsgBox("Do you really want to delete " & _
            "this record?", vbOKCancel, _
            "Delete Verification") = vbCancel Then
                Cancel = True
        End If
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-8  - Code for the Product Categories form
'----------------------------------------------------------------

Private Sub Form_BeforeDelConfirm(Cancel _
        As Integer, Response As Integer)
    Response = acDataErrContinue
End Sub


'----------------------------------------------------------------
' Code on page 573
'----------------------------------------------------------------

Private Sub Form_BeforeDelConfirm(Cancel As Integer, _
    Response As Integer)
    
    ' remove the default Access message box that prompts to confirm deletion
    Response = acDataErrContinue
    
    If MsgBox("Do you really want to delete this record?", _
        vbOKCancel) = vbCancel Then
        Cancel = True
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-9  - Code for the Product Categories form
'----------------------------------------------------------------

Private Sub Form_AfterDelConfirm(Status As Integer)
    MsgBox "The selected record was deleted."
    Debug.Print "Status = " & Status
End Sub


'----------------------------------------------------------------
' Hands-On 24-10  - Code for the Employees form
'----------------------------------------------------------------

Private Sub Form_Activate()
    Me.TabCtl0.Pages(1).Visible = False
End Sub


'----------------------------------------------------------------
' Hands-On 24-11  - Code for the Employees form
'----------------------------------------------------------------

Private Sub Form_Deactivate()
    MsgBox "You are leaving the " & Me.Name & _
            " form."
    If Me.Dirty Then
        DoCmd.Save acForm, Me.Name
        MsgBox "Your changes have been saved."
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-12  - Code for the Mouse Test form
'----------------------------------------------------------------

Private Sub Form_Click()
    MsgBox "Form Click Event Occurred."
    Me.Text0.Left = Text0.Left + 1440
End Sub


'----------------------------------------------------------------
' Hands-On 24-13  - Code for the Products Test form
'----------------------------------------------------------------

Private Sub Form_MouseDown(Button As Integer, _
    Shift As Integer, _
    X As Single, _
    Y As Single)
    
    Debug.Print "Mouse Down"
    
    Select Case Shift
        Case 0
            MsgBox "You did not press a key."
        Case 1 ' or acShiftMask
            MsgBox "You pressed SHIFT."
        Case 2 ' or acCtrlMask
            MsgBox "You pressed CTRL."
        Case 3
            MsgBox "You pressed CTRL and SHIFT."
        Case 4 ' or acAltMask
            MsgBox "You pressed ALT."
        Case 5
            MsgBox "You pressed ALT and SHIFT."
        Case 6
            MsgBox "You pressed CTRL and ALT."
        Case 7
            MsgBox "You pressed CTRL, ALT, and SHIFT."
    End Select
    
    If Button = 1 Then ' acLeftButton
        MsgBox "You pressed the left button."
    ElseIf Button = 2 Then 'acRightButton
        MsgBox "You pressed the right button."
    ElseIf Button = 4 Then 'acMiddleButton
        MsgBox "You pressed the middle button."
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-14  - Code for the Products form
'----------------------------------------------------------------

Private Sub Form_KeyDown _
    (KeyCode As Integer, Shift As Integer)
    
    Select Case KeyCode
        Case vbKeyF1
            MsgBox "You pressed the F1 key."
        Case vbKeyHome
            MsgBox "You pressed the Home key."
        Case vbKeyTab
            MsgBox "You pressed the Tab key."
    End Select
    
    Select Case Shift
        Case acShiftMask
            MsgBox "You pressed the SHIFT key."
        Case acCtrlMask
            MsgBox "You pressed the CTRL key."
        Case acAltMask
            MsgBox "You pressed the ALT key."
    End Select
    
    If KeyCode = vbKeyDelete Then
        MsgBox "Delete Key is not allowed."
        KeyCode = 0
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-15  - Code for the Suppliers form
'----------------------------------------------------------------

Private Sub Form_KeyPress(KeyAscii As Integer)
    Debug.Print "Key Press: KeyAscii = " & KeyAscii & _
        Space(1) & "= " & Chr(KeyAscii)
    
    If KeyAscii = 27 Then
      If MsgBox("Save changes to this form?", _
         vbYesNo) = vbYes Then
         DoCmd.Close acForm, Me.Name, acSaveYes
      Else
         KeyAscii = 0
      End If
    Else
         KeyAscii = 0
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-16  - Code for the Suppliers form
'----------------------------------------------------------------

Private Sub Form_KeyUp(KeyCode As Integer, _
    Shift As Integer)
    
    Debug.Print "Key up : " & Chr(KeyCode) & _
        "(" & KeyCode & ") " & _
        Shift
    Me.Caption = Me.Name
    Me.Caption = Me.Caption & ": KeyCode=" & _
    KeyCode & " " & "Shift=" & Shift
End Sub


'----------------------------------------------------------------
' Hands-On 24-17  - Code for the Customers Data Entry form
'----------------------------------------------------------------

Private Sub Form_Error(DataErr As Integer, _
    Response As Integer)
    
    Dim strMsg As String
    Dim custId As String
    Const conDuplicateKey = 3022
    
    custId = Me.CustomerID
    
    If DataErr = conDuplicateKey Then
    
        ' Don't show built-in error messages
        Response = acDataErrContinue
        strMsg = "Customer " & custId & " already exists."
        
        ' Show a custom error message
        MsgBox strMsg, vbCritical, "Duplicate Value"
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-18  - Code for the Product Categories form
'----------------------------------------------------------------

Private Sub Form_Filter(Cancel As Integer, _
    FilterType As Integer)
    
    Select Case FilterType
        Case acFilterByForm
            MsgBox "You selected to filter records " & _
                "by form.", vbOKOnly + vbInformation, _
                "Filter By Form"
            Me.CategoryName.SetFocus
            Me.CategoryID.Enabled = False
        Case acFilterAdvanced
            MsgBox "You are not authorized to use " & _
                " Advanced Filter/Sort.", _
                vbOKOnly + vbInformation, _
                "Advanced Filter By Form"
            Cancel = True
    End Select
End Sub


'----------------------------------------------------------------
' Hands-On 24-19  - Code for the Product Categories form
'----------------------------------------------------------------

Private Sub Form_ApplyFilter(Cancel As Integer, _
    ApplyType As Integer)
    
    Dim Response As Integer
    
    If ApplyType = acApplyFilter Then
        If Me.Filter = "" Then
            MsgBox "You did not select any criteria.", _
            vbOKOnly + vbCritical, "No Selection"
            GoTo ExitHere
        End If
        Response = MsgBox("The selected criteria " & _
            "is as follows:" & vbCrLf & _
            Me.Filter, vbOKCancel + vbQuestion, _
            "Filter Criteria")
    End If
    
    If Response = vbCancel Then
        Cancel = True
    End If
    
    If ApplyType = acShowAllRecords Then
        Me.Filter = ""
        MsgBox "Filter was removed."
    End If
    
    If ApplyType = acCloseFilterWindow Then
        Response = MsgBox("Are you sure you " & _
            "want to close the Filter window?", vbYesNo)
        If Response = vbNo Then
            Cancel = True
        End If
    End If
    
ExitHere:
    With Me.CategoryID
        .Enabled = True
        .SetFocus
    End With
End Sub


'----------------------------------------------------------------
' Hands-On 24-20  - Code for the Products form
'----------------------------------------------------------------

Private Sub Form_Timer()
    Static OnOff As Integer
    
    If OnOff Then
        Me.PreviewReport.Caption = "Preview Product List"
        ' Me.PreviewReport.Visible = True
    Else
        Me.PreviewReport.Caption = ""
        ' Me.PreviewReport.Visible = False
    End If
    
    OnOff = Not OnOff
End Sub


'----------------------------------------------------------------
' Hands-On 24-21 - Code for the Employee Sales Pivot form
'----------------------------------------------------------------

' Employee Sales by Date query - SQL statement (for step 2 of this Hands-On)

SELECT Employees.LastName, Employees.FirstName, Employees.Country,
Orders.ShippedDate, Orders.OrderID,
Sum(CCur(([UnitPrice]*[Quantity])-[Discount])) AS SaleAmount
FROM Employees INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID=[Order Details].OrderID) ON
Employees.EmployeeID=Orders.EmployeeID
GROUP BY Employees.LastName, Employees.FirstName, Employees.Country,
Orders.ShippedDate, Orders.OrderID;


Private Sub Form_OnConnect()
     MsgBox "You have connected to the data source." & vbCrLf _
       & "This PivotTable is based on: " & Me.RecordSource
End Sub


'----------------------------------------------------------------
' Hands-On 24-22  - Code for the Employee Sales Pivot form
'----------------------------------------------------------------

Private Sub Form_BeforeQuery()
    Dim strColor As String
    
    strColor = InputBox("Type 1 for Red, 2 for Yellow, " & _
        "or 3 for Green", "Specify Background Color for " & _
        "Detail Rows")
    
    Select Case strColor
        Case 1
            strColor = "Red"
        Case 2
            strColor = "Yellow"
        Case 3
            strColor = "Green"
        Case Else
            strColor = "White"
    End Select
    
    DoCmd.Hourglass True
    Me.PivotTable.ActiveView.DataAxis.FieldSets(0). _
        Fields(0).DetailBackColor = strColor
        
    Debug.Print "Executing the BeforeQuery event"
    Debug.Print "(Changed detail color to: " & strColor & ")"
End Sub

Private Sub Form_Query()
   Debug.Print "Executing the Query event"
   DoCmd.Hourglass False
   Debug.Print "(Turned off the hourglass mouse pointer)"
End Sub


'----------------------------------------------------------------
' Hands-On 24-23  - Code for the Employee Sales Pivot form
'----------------------------------------------------------------

Private Sub Form_AfterLayout(ByVal drawObject As Object)
   Dim oSeriesDropZ As Object

   Me.ChartSpace.ChartSpaceLegend.Top = 50
   Set oSeriesDropZ = Me.ChartSpace.DropZones(chDropZoneSeries)
   oSeriesDropZ.Top = 30
End Sub


'----------------------------------------------------------------
' Hands-On 24-24  - Code for the Employee Sales Pivot form
'----------------------------------------------------------------

Private Sub Form_BeforeRender(ByVal drawObject As Object, _
    ByVal chartObject As Object, _
    ByVal Cancel As Object)
    
    If TypeName(chartObject) = " ChLegendEntry " Then
        drawObject.Border.Color = "red"
        drawObject.DrawEllipse chartObject.Left, _
            chartObject.Bottom, _
            chartObject.Right, _
            chartObject.Top
    End If
    
    If TypeName(chartObject) = "ChGridlines" Then
        Cancel.Value = True
    End If
End Sub

'----------------------------------------------------------------
' Hands-On 24-25  - Code for the Employee Sales Pivot form
'----------------------------------------------------------------

Private Sub Form_AfterRender(ByVal drawObject As Object, _
    ByVal chartObject As Object)
    
    Dim oChart As Object
    
    Set oChart = Me.ChartSpace.Charts.Item(0)
    
    If TypeName(chartObject) = "ChLegend" Then
        drawObject.DrawText "Chart legend has been rendered", _
            oChart.PlotArea.Left + 15, _
            oChart.PlotArea.Top
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-26  - Code for the Employee Sales Pivot form
'----------------------------------------------------------------

Private Sub Form_AfterFinalRender(ByVal drawObject As Object)
    Dim oChart As Object
    Dim s As Object
    Dim p As Object
    Dim strText As String
    Dim num As Integer
    
    Set oChart = Me.ChartSpace.Charts(0)
    
    For Each s In oChart.SeriesCollection
        For Each p In s.Points
            num = num + 1
            Debug.Print num
        Next
    Next
    
    strText = "This chart contains " & num & " data points."
    
    With drawObject.Font
        .Size = 9
        .Color = "blue"
        .Italic = True
    End With
    
    drawObject.DrawText strText, 200, 20
End Sub


'----------------------------------------------------------------
' Hands-On 24-27  - Code for the Employee Sales Pivot form
'----------------------------------------------------------------

Private Sub Form_DataChange(ByVal Reason As Long)
    Select Case Reason
        Case OWC11.plDataReasonAllIncludeExcludeChange
            Debug.Print "Change in the included/excluded members."
        Case OWC11.plDataReasonIncludedMembersChange
            Debug.Print "Included Members Change"
        Case OWC11.plDataReasonExcludedMembersChange
            Debug.Print "Excluded Members Change"
        Case Else
            Debug.Print "Reason code: " & Reason
    End Select
End Sub


'----------------------------------------------------------------
' Hands-On 24-28  - Code for the Employee Sales Pivot form
'----------------------------------------------------------------

Private Sub Form_PivotTableChange(ByVal Reason As Long)
    If Reason = OWC11.plPivotTableReasonTotalAdded Then
	MsgBox "Just added a Total."
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-29  - Code for the Employee Sales Pivot form
'----------------------------------------------------------------

Private Sub Form_SelectionChange()
   Debug.Print "Selection Type: " & Me.PivotTable.SelectionType
End Sub

'----------------------------------------------------------------
' Hands-On 24-30  - Code for the Employee Sales Pivot form
'----------------------------------------------------------------

Private Sub Form_ViewChange(ByVal Reason As Long)
    Select Case Reason
        Case OWC11.plViewReasonHideDetails
            MsgBox "You selected to hide the details."
        Case OWC11.plViewReasonShowDetails
            MsgBox "You selected to show the details."
    End Select
End Sub


'----------------------------------------------------------------
' Hands-On 24-31  - Code for the Employee Sales Pivot form
'----------------------------------------------------------------

Private Sub Form_CommandEnabled(ByVal Command As Variant, _
    ByVal Enabled As Object)
    
    If Command = OWC11.plCommandCopy Then
        If Enabled.Value = True Then
            Debug.Print "Copy command enabled."
        Else
            Debug.Print "Copy command disabled."
        End If
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-32  - Code for the Employee Sales Pivot form
'----------------------------------------------------------------

Private Sub Form_CommandChecked(ByVal Command As Variant, _
    ByVal Checked As Object)
    
    If Command = OWC11.plCommandSortAsc Then
        If Checked.Value = True Then
            Debug.Print "Sort Ascending command is checked."
        Else
            Debug.Print "Sort Ascending command unchecked."
        End If
    End If
End Sub

'----------------------------------------------------------------
' Hands-On 24-33  - Code for the Empoyee Sales Pivot form
'----------------------------------------------------------------

Private Sub Form_CommandExecute(ByVal Command As Variant)
    If Command = OWC11.plCommandCopy Then
        MsgBox "You just copied some data to the clipboard."
    End If
End Sub


'----------------------------------------------------------------
' Hands-On 24-34  - Code for the Product Categories form
'----------------------------------------------------------------

Private Sub Detail_DblClick(Cancel As Integer)
    With Me
        .Section(acHeader).BackColor = _
            RGB(Rnd * 128, _
            Rnd * 256, _
            Rnd * 255)
        .Section(acDetail).BackColor = _
            RGB(Rnd * 128, _
            Rnd * 256, _
            Rnd * 255)
        .Section(acFooter).BackColor = _
            RGB(Rnd * 128, _
            Rnd * 256, _
            Rnd * 255)
    End With
End Sub

