Home

Data and Relationships

 

Updating a Combo Box

 

Refreshing a Combo Box

As we saw in previous sections, a combo box is a prime candidate for dealing with records in relationship-based objects. Usually, when using a combo box, if you change the values on the table or query that holds its data, and then get back to the form, the combo box would not have the new value. To solve this problem, you have many options.

To update the contents of a combo box, you can call the Requery() method.

Practical LearningPractical Learning: Refreshing a Combo Box

  1. Start Microsoft Access
  2. From the resources that accompany these lessons, open the Ceil Inn1 database
  3. In the Navigation Pane, right-click Occupancies and click Design View
  4. In the Controls section of the Ribbon, click the Button and click on the right side of the EmployeeID combo box.
    If the Command Button Wizard starts, click Cancel
  5. In the Properties window, change the following values:
    Name: cmdAddEmployee
    Caption: Add Employee
  6. Right-click the Add Employee button and click Build Event...
  7. In the Choose Builder dialog box, click Code Builder and click OK
  8. Implement the event as follows:
     
    Private Sub cmdAddEmployee_Click()
    On Error GoTo cmdAddEmployee_Error
        ' This variable will be used to represent the foreign key
        Dim NbrEmployeeID As Long
        
        ' If the foreign key currently has no value ...
        If IsNull(EmployeeID) Then
            ' Set the value of the combo box to empty
            EmployeeID = ""
        Else
            ' If the foreign key currently has a value,
            ' assign that value to the declared value
            NbrEmployeeID = EmployeeID
            ' Set the foreign key to null
            EmployeeID = Null
        End If
        
        ' The combo box is ready to receive a new value
        ' To make it happen, display the Employees for as a dialog box
        ' Open it as a dialog box so the user would not use
        ' the Occupancies form while the Employees form is opened
        ' Also, when opening the Employees form, navigate to a new record
        DoCmd.OpenForm "Employees", , , , , AcWindowMode.acDialog, "GotoNew"
        'After using the Employees form, the user will close
        'When the user closes the Employees form, refresh the combo box
        EmployeeID.Requery
        
        ' If the user had created a new employee,
        ' assign its EmployeeID to the variable we had declared
        If NbrEmployeeID <> 0 Then
            EmployeeID = NbrEmployeeID
        End If
        
    cmdAddEmployee_Exit:
        Exit Sub
        
    cmdAddEmployee_Error:
        MsgBox "An error occured when trying to update the list." & vbCrLf & _
               "=- Report the error as follows -=" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Error Message: " & Err.Description
               Resume cmdAddEmployee_Exit
    End Sub
  9. Return to Microsoft Access
  10. In the Controls section of the Ribbon, click the Button and click on the right side of the CustomerID combo box.
    If the Command Button Wizard starts, click Cancel
  11. In the Properties window, change the following values:
    Name: cmdAddCustomer
    Caption: Add Customer
  12. Right-click the Add Customer button and click Build Event...
  13. In the Choose Builder dialog box, click Code Builder and click OK
  14. Implement the event as follows:
     
    Private Sub cmdAddCustomer_Click()
    On Error GoTo cmdAddCustomer_Error
        Dim NbrCustomerID As Long
        
        If IsNull(CustomerID) Then
            CustomerID = ""
        Else
            NbrCustomerID = CustomerID
            CustomerID = Null
        End If
        
        DoCmd.OpenForm "Customers", , , , , AcWindowMode.acDialog, "GotoNew"
        CustomerID.Requery
        
        If NbrCustomerID <> 0 Then
            CustomerID = NbrCustomerID
        End If
        
    cmdAddCustomer_Exit:
        Exit Sub
        
    cmdAddCustomer_Error:
        MsgBox "An error occured when trying to update the list." & vbCrLf & _
               "=- Report the error as follows -=" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Error Message: " & Err.Description
               Resume cmdAddCustomer_Exit
    End Sub
  15. Return to Microsoft Access

The Not In List Event

When using a combo box, a user may want to select a value that is not in the list. To do this, the user may click the text box part of the combo box, type a value, and press Enter or Tab. If the user does this, the database engine would produce an error and fire the Not In List event. You can implement this event to do what is necessary to assist the user. For example, you can get the value the user had typed and add it to the table that owns the records of the combo box.

Sub-Forms

After creating a relationship between two tables, you can take advantage of it during data viewing. Among the ways you can do it, you can create a sub-form and add it to a parent form.

Practical LearningPractical Learning: Using a Sub-Form

  1. From the Navigation Pane, double-click the Customers form
  2. Navigate to record 11 and notice that its sub-form displays empty records
  3. In the sub-form, enter the following values:

Domain Aggregate Functions

 

Introduction

We have seen what valuable role a relationship between two tables can play, allowing data from one to be directly available to another. In some cases, you will not want data from one table to be accessed just anyhow from another table. To manage the flow, you can set a condition that must be met. This also allows you to restrict a specific record instead of considering all records, simply because they happen to have a matching value in the foreign key of a child table. To set the condition that would be used to retrieve a specific record, you can use one of the functions referred to as domain aggregates.

Practical LearningPractical Learning: Introducing Domain Aggregate Functions

  1. Start Microsoft Access
  2. Open the Greenbelt Auto Parts1 database you created in Lesson 24
  3. On the Ribbon, click Create
  4. In the Forms section, click Form Design
  5. In the Controls section of the Ribbon, click the Button and click the Detail section of the form. If the Button Wizard starts, click Cancel
  6. On the form, double-click the button to access its Properties window.
    Change its Name to cmdCreateTables
  7. Change its Caption to Create Tables
  8. Right-click the Create Tables button and click Build Event...
  9. In the Choose builder dialog box, double-click Code Builder
  10. Implement the event as follows:
     
    Private Sub cmdCreateTables_Click()
        DoCmd.RunSQL "CREATE TABLE Employees(" & _
                     "EmployeeID AutoIncrement(1, 1) " & _
                     "           Primary Key Not Null, " & _
                     "EmployeeNumber varchar(10)," & _
                     "FirstName varchar(40), LastName varchar(40), " & _
                     "HourlySalary double);"
        MsgBox "A table named Employees has been created."
        
        DoCmd.RunSQL "INSERT INTO Employees(EmployeeNumber, " & _
                     "FirstName, LastName, HourlySalary) " & _
                     "VALUES('52446', 'Christine', 'Pitts', 22.85);"
        DoCmd.RunSQL "INSERT INTO Employees(EmployeeNumber, " & _
                     "FirstName, LastName, HourlySalary) " & _
                     "VALUES('24282', 'Jeannette', 'Simson', 18.90);"
        DoCmd.RunSQL "INSERT INTO Employees(EmployeeNumber, " & _
                     "FirstName, LastName, HourlySalary) " & _
                     "VALUES('28048', 'Harry', 'Phelmann', 24.70);"
        DoCmd.RunSQL "INSERT INTO Employees(EmployeeNumber, " & _
                     "FirstName, LastName, HourlySalary) " & _
                     "VALUES('63625', 'Jason', 'Gadd', 16.55);"
        DoCmd.RunSQL "INSERT INTO Employees(EmployeeNumber, " & _
                     "FirstName, LastName, HourlySalary) " & _
                     "VALUES('42957', 'Frank', 'Costas', 20.15);"
        
        DoCmd.RunSQL "CREATE TABLE TimeSheets(" & _
                     "TimeSheetID AutoIncrement(1, 1) " & _
                     "           primary key not null, " & _
                     "EmployeeNumber varchar(10)," & _
                     "StartDate varchar(40), EndDate varchar(40), " & _
                     "TimeSheetCode varchar(20), " & _
                     "Week1Monday double, Week1Tuesday double, " & _
                     "Week1Wednesday double, Week1Thursday double, " & _
                     "Week1Friday double, Week1Saturday double, " & _
                     "Week1Sunday double, Week2Monday double, " & _
                     "Week2Tuesday double, Week2Wednesday double, " & _
                     "Week2Thursday double, Week2Friday double, " & _
                     "Week2Saturday double, Week2Sunday double, " & _
                     "Notes Memo);"
        MsgBox "A table named TimeSheets has been created."
        
        DoCmd.RunSQL "CREATE TABLE Payrolls(" & _
                     "PayrollID AutoIncrement(1, 1) " & _
                     "           primary key not null, " & _
                     "StartDate varchar(40), EndDate varchar(40), " & _
                     "PayDate varchar(40), TimeSheetCode varchar(20), " & _
                     "EmployeeNumber varchar(10), " & _
                     "EmployeeName varchar(84), HourlySalary double, " & _
                     "RegularTime double, RegularPay double, " & _
                     "OvertimeTime double, OvertimePay double, " & _
                     "GrossPay double, " & _
                     "FederalTax double, SocSecurityTax double, " & _
                     "MedicareTax double, StateTax double, " & _
                     "NetPay double, Notes Memo);"
        MsgBox "A table named Payrolls has been created."
    End Sub
  11. Return to Microsoft Access
  12. Switch the form to Form View and click the button
  13. Click OK each time to create the table and create its records
  14. Close the form
  15. When asked whether you want to save it, click No
  16. On the Ribbon, click Create
  17. In the forms section, click Form Design
  18. Save the form as TimeSheet
  19. Design the form as follows (You don't have to follow the exact same design; you only need to have the same controls and names):
     
    Time Sheet
     
    Control Caption Name Format
    Text Box Employee #: txtEmployeeNumber  
    Text Box   txtEmployeeName  
    Text Box Start Date: txtStartDate Short Date
    Text Box End Date: txtEndDate Short Date
    Text Box   txtTimeSheetCode  
    Line      
    Label Time Recording    
    Label Mon    
    Label Tue    
    Label Wed    
    Label Thu    
    Label Fri    
    Label Sat    
    Label Sun    
    Label Week 1    
    Text Box   txtWeek1Monday Fixed
    Text Box   txtWeek1Tuesday Fixed
    Text Box   txtWeek1Wednesday Fixed
    Text Box   txtWeek1Thursday Fixed
    Text Box   txtWeek1Friday Fixed
    Text Box   txtWeek1Saturday Fixed
    Text Box   txtWeek1Sunday Fixed
    Text Box   txtWeek2Monday Fixed
    Text Box   txtWeek2Tuesday Fixed
    Text Box   txtWeek2Wednesday Fixed
    Text Box   txtWeek2Thursday Fixed
    Text Box   txtWeek2Friday Fixed
    Text Box   txtWeek2Saturday Fixed
    Text Box   txtWeek2Sunday Fixed
    Text Box   txtNotes  
    Button Reset cmdReset  
    Button Submit Time Sheet cmdSubmitTimeSheet  
    Button Close cmdClose  
  20. Right-click the reset button and click Build Event
  21. In the Choose Builder dialog box, double-click Code Builder
  22. Implement the event as follows:
     
    Private Sub cmdReset_Click()
        txtEmployeeNumber = ""
        txtEmployeeName = ""
        txtStartDate = Date
        txtEndDate = CDate(DateAdd("d", 13, Date))
        txtTimeSheetCode = ""
        
        txtWeek1Monday = "0.00"
        txtWeek1Tuesday = "0.00"
        txtWeek1Wednesday = "0.00"
        txtWeek1Thursday = "0.00"
        txtWeek1Friday = "0.00"
        txtWeek1Saturday = "0.00"
        txtWeek1Sunday = "0.00"
        
        txtWeek2Monday = "0.00"
        txtWeek2Tuesday = "0.00"
        txtWeek2Wednesday = "0.00"
        txtWeek2Thursday = "0.00"
        txtWeek2Friday = "0.00"
        txtWeek2Saturday = "0.00"
        txtWeek2Sunday = "0.00"
    End Sub
  23. In the Object combo box, select Form
  24. Implement the OnLoad event as follows:
     
    Private Sub Form_Load()
        cmdReset_Click
    End Sub
  25. Close Microsoft Visual Basic and return to Microsoft Access
  26. Save and close the form
  27. On the Ribbon, click Create
  28. In the forms section, click Form Design
  29. Save the form as PayrollProcessing
  30. Design the form as follows:
     
     
    Control Caption Name Format
    Line      
    Label Payroll Identification    
    Text Box Employee #: txtEmployeeNumber  
    Text Box   txtEmployeeName  
    Text Box Start Date: txtStartDate Short Date
    Text Box Hourly Salary: txtHourlySalary  
    Text Box End Date: txtEndDate Short Date
    Text Box   txtTimeSheetCode  
    Text Box Pay Date: txtPayDate  
    Line      
    Label Gross Pay Calculation    
    Label Time    
    Label Pay    
    Text Box Regular txtRegularTime Fixed
    Text Box   txtRegularPay Fixed
    Text Box Overtime txtOvertimeTime Fixed
    Text Box   txtOvertimePay Fixed
    Text Box Gross Pay txtGrossPay Fixed
    Line      
    Label Deductions    
    Text Box Federal Withholding Tax: txtFederalTax Fixed
    Text Box Social Security Tax: txtSocSecurityTax Fixed
    Text Box Medicare Tax: txtMedicareTax Fixed
    Text Box State Tax: txtStateTax Fixed
    Text Box Net Pay: txtNetPay Fixed
    Line      
    Text Box   txtNotes  
    Button Reset cmdReset  
    Button Approve/Submit Payroll cmdApproveSubmitPayroll  
    Button Close cmdClose  
  31. Right-click the reset button and click Build Event
  32. In the Choose Builder dialog box, double-click Code Builder
  33. Implement the event as follows:
     
    Private Sub cmdReset_Click()
        txtEmployeeNumber = ""
        txtEmployeeName = ""
        txtStartDate = CStr(Date)
        txtEndDate = CStr(DateAdd("d", 13, Date))
        txtHourlySalary = "0.00"
        txtTimeSheetCode = ""
        txtPayDate = CStr(Date)
        txtRegularTime = "0.00"
        txtRegularPay = "0.00"
        txtOvertimeTime = "0.00"
        txtOvertimePay = "0.00"
        txtGrossPay = "0.00"
        txtFederalTax = "0.00"
        txtSocSecurityTax = "0.00"
        txtMedicareTax = "0.00"
        txtStateTax = "0.00"
        txtNetPay = "0.00"
        txtNotes = ""
    End Sub
  34. In the Object combo box, select Form
  35. Implement the OnLoad event as follows:
     
    Private Sub Form_Load()
        cmdReset_Click
    End Sub
  36. In the Object combo box, select txtFederalTax
  37. In the Procedure combo box, select LostFocus
  38. Implement the event as follows:
     
    Private Sub txtFederalTax_LostFocus()
        Dim GrossPay As Double
        Dim FederalWithholding As Double
        Dim SocSecurity As Double
        Dim Medicare As Double
        Dim State As Double
        Dim NetPay As Double
        
        GrossPay = CDbl(txtGrossPay)
        FederalWithholding = CDbl(txtFederalTax)
        SocSecurity = CDbl(txtSocSecurityTax)
        Medicare = CDbl(txtMedicareTax)
        State = CDbl(txtStateTax)
        
        NetPay = GrossPay - FederalWithholding - SocSecurity - Medicare - State
        
        txtNetPay = NetPay
    End Sub
  39. Close Microsoft Visual Basic and return to Microsoft Access
  40. Close the form
  41. When asked whether you want to save it, click Yes

Using a Domain Aggregate Function

A domain aggregate function has the following syntax:

DFunctionName(Expression, Domain, Criteria)

The DFunctionName factor is the name of the function. The Expression argument can be the name of a column on which the function will act. It can also be a calculation-based expression. The Domain argument can be the name of a table or that of a query that doesn't require an external value. The Criteria argument is optional. If passed, it can specify the condition used to select a specific record.

Consider the following table:

Properties

Some of the domain aggregate functions resemble the SQL aggregate functions we reviewed in the previous lesson. There are additional others:

  • DCount: The DCount() function can be used to count the number of records of another table or query. When calling this function, the first argument should always be the name of a column. Here is an example:
     
    =DCount("Title","Videos")

    This code returns the total number of videos that have a value in the Title column. The DCount() function doesn't count the records where the Expression value is NULL. As mentioned in the syntax of the domain aggregate functions, you can use a criterion to select specific records. Here is an example:

    =DCount("Title","Videos","Rating = 'R'")

    In this case, the function will return the number of videos that are rated R from our table.
     

  • DSum: The DSum() function can calculate the total of (numeric) values of a column in a table (or query). Here is an example that will return the amount we spent buying the videos of the above table:
     
    =DSum("PriceBought","Videos")
  • DAvg: The DAvg() function is used to calculate the average of (numeric) values of a column of a table. Here is an example that produces the average price of the videos of the above table:
     
    =DAvg("PriceBought","Videos")
  • DMin and DMax: The DMin() (or the DMax()) function is used to calculate the minimum (or the maximum) of the values of a column in another table or query. If there is only one value in the group, both functions return the same value.
  • DFirst and DLast: The DFirst() (or the DLast()) function is used to get the first (or the last) value of a column in a table or query. If there is only one value in the group, both functions return the same value.
  • DStDev and DStDevP: The StDev() function is used to calculate the standard deviation of the numeric values of a the records of a table or query. The DStDevP() function is used to calculate the standard deviation of a population sample of the numeric values of a the records of a table or query. If there is no value or the same value in the considered group, this function returns NULL. This means that there should be at least two different values in the group.
  • DVar and DVarP: The DVar() function calculates the statistical variance of the (numeric) values of a table or query. The DVarP() function calculates the variance of a population. If there is no value or the same value in the considered group, these functions return NULL.
  • DLookup: The DLookup() function can be used to find one or more records that respond to a criterion from another table or query.

Practical LearningPractical Learning: Looking for a Record in a Domain

  1. In the Navigation Pane, right-click TimeSheet and click Design View
  2. On the form, double-click the Employee Number text box
  3. In the Properties window, click Event and double-click On Lost Focus
  4. Click its ellipsis button
  5. Implement the event as follows:
     
    Private Sub txtEmployeeNumber_LostFocus()
    On Error GoTo txtEmployeeNumber_Error
    
        ' Access the Employees table
        ' Locate an employee whose uses the number entered by the user
        If Not IsNull(DLookup("EmployeeNumber", "Employees", _
                     "EmployeeNumber = '" & txtEmployeeNumber & "'")) Then
            ' If you find it, retrieve the corresponding name
            ' (last name and first name)
            ' and display the full name
            txtEmployeeName = DLookup("LastName", "Employees", _
                                      "EmployeeNumber = '" & _
                                      txtEmployeeNumber & "'") & _
                                      ", " & _
                              DLookup("FirstName", "Employees", _
                                      "EmployeeNumber = '" & _
                                      txtEmployeeNumber & "'")
        Else
            ' If you didn't find any employee with that number,
            ' reset the current record
            cmdReset_Click
        End If
    
    txtEmployeeNumber_Exit:
        Exit Sub
        
    txtEmployeeNumber_Error:
        If Err.Number = -2147352567 Then
            Resume txtEmployeeNumber_Exit
        Else
            MsgBox "An error occured when retrieving the " & _
    	       "employee information." & vbCrLf & _
                   "Please call the program vendor and report " & _
    	       "the error as follows:" & vbCrLf & _
                   "Error #: " & Err.Number & vbCrLf & _
                   "Reason:  " & Err.Description
            Resume Next
        End If
    End Sub
  6. In the Object combo box, select txtStartDate
  7. In the Procedure combo box, select LostFocus
  8. Create the follow function and implement the OnLostFocus as follows:
     
    ' This function is used to create a number we will name TimeSheetCode
    ' This number is used to uniquely identify each timesheet record
    ' This number holds the employee number (5 digits)
    ' and the start date (yyyymmdd) of the time sheet
    ' It is useful as it allows us to find out whether the employee
    ' had previous filled out a time sheet or not
    Private Function CreateTimeSheetCode(ByVal EmplNbr As String, _
                                         ByVal DateStart As Date)
        Dim strMonth As String
        Dim strDay As String
        Dim iMonth As Integer
        Dim iDay As Integer
        Dim strTimeSheetCode As String
        
        iMonth = Month(DateStart)
        iDay = Day(DateStart)
        
        If iMonth < 10 Then
            strMonth = CStr(Year(DateStart)) & "0" & CStr(iMonth)
        Else
            strMonth = CStr(Year(DateStart)) & CStr(iMonth)
        End If
        
        If iDay < 10 Then
            strDay = strMonth & "0" & CStr(iDay)
        Else
            strDay = strMonth & CStr(iDay)
        End If
        
        CreateTimeSheetCode = (EmplNbr & strDay)
    End Function
    
    Private Sub txtStartDate_LostFocus()
        ' After the user has entered a start date,
        ' get that date
        If Not IsNull(txtStartDate) Then
            ' Add 14 days to the start date to get the end date
            txtEndDate = CStr(DateAdd("d", 13, CDate(txtStartDate)))
            ' Create a time sheet code
            txtTimeSheetCode = CreateTimeSheetCode(txtEmployeeNumber, _
                                                   CDate(txtStartDate))
        Else
            ' If the start date is empty, don't do anything
            Exit Sub
        End If
    End Sub
  9. In the Object combo box, select cmdSubmitTimeSheet
  10. Implement the event as follows:
     
    Private Sub cmdSubmitTimeSheet_Click()
    On Error GoTo cmdSubmitTimeSheet_Error
    
        Dim curDatabase As Object
        Dim rstTimeSheets As Object
        Dim fldTimeSheet As Object
        
        If IsNull(txtEmployeeNumber) Then
            MsgBox "You must enter the employee number to proceed."
            Exit Sub
        End If
        
        If IsNull(txtStartDate) Then
            MsgBox "You must enter the starting date to proceed."
            Exit Sub
        End If
        
        Set curDatabase = CurrentDb
        Set rstTimeSheets = curDatabase.OpenRecordset("TimeSheets")
        
        With rstTimeSheets
            .AddNew
            .Fields("EmployeeNumber").Value = txtEmployeeNumber
            .Fields("StartDate").Value = txtStartDate
            .Fields("EndDate").Value = txtEndDate
            .Fields("TimeSheetCode").Value = txtTimeSheetCode
            .Fields("Week1Monday").Value = txtWeek1Monday
            .Fields("Week1Tuesday").Value = txtWeek1Tuesday
            .Fields("Week1Wednesday").Value = txtWeek1Wednesday
            .Fields("Week1Thursday").Value = txtWeek1Thursday
            .Fields("Week1Friday").Value = txtWeek1Friday
            .Fields("Week1Saturday").Value = txtWeek1Saturday
            .Fields("Week1Sunday").Value = txtWeek1Sunday
            .Fields("Week2Monday").Value = txtWeek2Monday
            .Fields("Week2Tuesday").Value = txtWeek2Tuesday
            .Fields("Week2Wednesday").Value = txtWeek2Wednesday
            .Fields("Week2Thursday").Value = txtWeek2Thursday
            .Fields("Week2Friday").Value = txtWeek2Friday
            .Fields("Week2Saturday").Value = txtWeek2Saturday
            .Fields("Week2Sunday").Value = txtWeek2Sunday
            .Fields("Notes").Value = txtNotes
           .Update
        End With
    
        Set rstTimeSheets = Nothing
        Set curDatabase = Nothing
        
        MsgBox "The time sheet has been registered."
        cmdReset_Click
    
    cmdSubmitTimeSheet_Exit:
        Exit Sub
        
    cmdSubmitTimeSheet_Error:
        MsgBox "There was a problem when submitting the time sheet."
        Resume cmdSubmitTimeSheet_Exit
    End Sub
  11. In the Object combo box, select cmdClose
  12. Implement the event as follows:
     
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  13. Close Microsoft Visual Basic to return to Microsoft Access
  14. Save the form and switch it to Form View
  15. Create some time sheets as follows:
     
    Employee # 52446 Start Date 04-Jan-2009      
      Mon Tue Wed Thu Fri Sat Sun
    Week 1 8 8.5 8 9.5 8.5 0 0
    Week 2 8 8 6.5 6 6 0 0

     
    Employee # 24282   Start Date 04-Jan-2009      
      Mon Tue Wed Thu Fri Sat Sun
    Week 1 8 7.5 7 7.5 6 0 0
    Week 2 6 6.5 6 7.5 6 0 0

     
    Employee # 28048   Start Date 04-Jan-2009      
      Mon Tue Wed Thu Fri Sat Sun
    Week 1 0 0 0 0 0 8 8
    Week 2 0 0 0 0 0 8 8

     
    Employee # 24282   Start Date 18-Jan-2009      
      Mon Tue Wed Thu Fri Sat Sun
    Week 1 8 6.5 8.5 8 8.5 0 0
    Week 2 8 7 7.5 8 8.5 0 0

     
    Employee # 52446   Start Date 18-Jan-2009      
      Mon Tue Wed Thu Fri Sat Sun
    Week 1 10 9.5 8.5 9 9.5 0 0
    Week 2 9 8.5 8.5 9.5 10.5 0 0

     
    Employee # 63625   Start Date 18-Jan-2009      
      Mon Tue Wed Thu Fri Sat Sun
    Week 1 8 8 8 8 8 0 0
    Week 2 8 8 8 8 8 0 0

     
    Employee # 52446   Start Date 01-Feb-2009      
      Mon Tue Wed Thu Fri Sat Sun
    Week 1 8.5 9.5 8.5 9 8 6 6
    Week 2 8 9 9.5 10 8.5 6 5.5
  16. Close the form
  17. In the Navigation Pane, right-click PayrollProcessing and click Design View
  18. On the form, double-click the Time Sheet Code text box
  19. In the Properties window, click Event and double-click On Lost Focus
  20. Click its ellipsis button
  21. Implement the event as follows:
     
    Private Sub cmdFindTimeSheet_Click()
    On Error GoTo cmdFindTimeSheet_Error
    
        Dim dWeek1Monday As Double, dWeek1Tuesday As Double
        Dim dWeek1Wednesday As Double, dWeek1Thursday As Double
        Dim dWeek1Friday As Double, dWeek1Saturday As Double
        Dim dWeek1Sunday As Double
        Dim dWeek2Monday As Double, dWeek2Tuesday As Double
        Dim dWeek2Wednesday As Double, dWeek2Thursday As Double
        Dim dWeek2Friday As Double, dWeek2Saturday As Double
        Dim dWeek2Sunday As Double
        
        Dim TotalWeek1Time As Double
        Dim TotalWeek2Time As Double
    
        Dim Week1RegularTime As Double
        Dim Week2RegularTime As Double
        Dim Week1OvertimeTime As Double
        Dim Week2OvertimeTime As Double
        Dim Week1RegularPay As Currency
        Dim Week2RegularPay As Currency
        Dim Week1OvertimePay As Currency
        Dim Week2OvertimePay As Currency
        
        Dim dRegularTime As Double
        Dim dOvertimeTime As Double
        Dim RegularPay As Double
        Dim OvertimePay As Double
        Dim TotalEarnings As Double
        Dim NetEarnings As Double
    
        Dim dHourlySalary As Double
        Dim OvertimeSalary As Double
        
        Dim FederalTax As Double
        Dim SocSecurityTax As Double
        Dim MedTax As Double
        Dim StTax As Double
        
        If IsNull(txtTimeSheetCode) Then
            MsgBox "You must enter a time sheet code."
            Exit Sub
        End If
    
        If Not IsNull(DLookup("TimeSheetCode", "TimeSheets", _
                      "TimeSheetCode = '" & txtTimeSheetCode & "'")) Then
            txtEmployeeNumber = DLookup("EmployeeNumber", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'")
            txtEmployeeName = DLookup("LastName", "Employees", _
                                      "EmployeeNumber = '" & _
                                      txtEmployeeNumber & "'") & _
                                      ", " & _
                              DLookup("FirstName", "Employees", _
                                      "EmployeeNumber = '" & _
                                      txtEmployeeNumber & "'")
            txtHourlySalary = DLookup("HourlySalary", "Employees", _
                                      "EmployeeNumber = '" & _
                                      txtEmployeeNumber & "'")
                                      
            txtStartDate = DLookup("StartDate", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'")
            txtEndDate = DLookup("EndDate", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'")
                                      
            ' Retrieve the hourly salary
            dHourlySalary = CDbl(txtHourlySalary)
            ' Retrieve the time for each day
            ' First Week
            dWeek1Monday = CDbl(DLookup("Week1Monday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
            dWeek1Tuesday = CDbl(DLookup("Week1Tuesday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
            dWeek1Wednesday = CDbl(DLookup("Week1Wednesday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
            dWeek1Thursday = CDbl(DLookup("Week1Thursday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
            dWeek1Friday = CDbl(DLookup("Week1Friday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
            dWeek1Saturday = CDbl(DLookup("Week1Saturday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
            dWeek1Sunday = CDbl(DLookup("Week1Sunday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
                        
            ' Second Week
            dWeek2Monday = CDbl(DLookup("Week2Monday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
            dWeek2Tuesday = CDbl(DLookup("Week2Tuesday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
            dWeek2Wednesday = CDbl(DLookup("Week2Wednesday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
            dWeek2Thursday = CDbl(DLookup("Week2Thursday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
            dWeek2Friday = CDbl(DLookup("Week2Friday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
            dWeek2Saturday = CDbl(DLookup("Week2Saturday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
            dWeek2Sunday = CDbl(DLookup("Week2Sunday", "TimeSheets", _
                                      "TimeSheetCode = '" & _
                                      txtTimeSheetCode & "'"))
        
            ' Calculate the total time for first week
            TotalWeek1Time = dWeek1Monday + dWeek1Tuesday + _
                              dWeek1Wednesday + dWeek1Thursday + _
                              dWeek1Friday + dWeek1Saturday + dWeek1Sunday
            ' Calculate the total time for second week
            TotalWeek2Time = dWeek2Monday + dWeek2Tuesday + _
                              dWeek2Wednesday + dWeek2Thursday + _
                              dWeek2Friday + dWeek2Saturday + dWeek2Sunday
    
            ' The overtime is paid time and half
            OvertimeSalary = dHourlySalary * 1.5
    
            ' If the employee worked under 40 hours, there is no overtime
            If TotalWeek1Time < 40 Then
                Week1RegularTime = TotalWeek1Time
                Week1RegularPay = dHourlySalary * Week1RegularTime
                Week1OvertimeTime = 0
                Week1OvertimePay = 0
            ' If the employee worked over 40 hours, calculate the overtime
            ElseIf TotalWeek1Time >= 40 Then
                Week1RegularTime = 40
                Week1RegularPay = dHourlySalary * 40
                Week1OvertimeTime = TotalWeek1Time - 40
                Week1OvertimePay = Week1OvertimeTime * OvertimeSalary
            End If
        
            If TotalWeek2Time < 40 Then
                Week2RegularTime = TotalWeek2Time
                Week2RegularPay = dHourlySalary * Week2RegularTime
                Week2OvertimeTime = 0
                Week2OvertimePay = 0
            ElseIf TotalWeek2Time >= 40 Then
                Week2RegularTime = 40
               Week2RegularPay = dHourlySalary * 40
                Week2OvertimeTime = TotalWeek2Time - 40
                Week2OvertimePay = Week2OvertimeTime * OvertimeSalary
            End If
        
            dRegularTime = Week1RegularTime + Week2RegularTime
            dOvertimeTime = Week1OvertimeTime + Week2OvertimeTime
            RegularPay = Week1RegularPay + Week2RegularPay
            OvertimePay = Week1OvertimePay + Week2OvertimePay
            TotalEarnings = RegularPay + OvertimePay
    
            ' The following calculations are for demonstration purpose only
            ' Consult the brochure for federal tax table
     	' FederalTax = ???
            SocSecurityTax = TotalEarnings * 6.2 / 100
            MedTax = TotalEarnings * 1.45 / 100
            StTax = TotalEarnings * 5.5 / 100
            NetEarnings = TotalEarnings - SocSecurityTax - MedTax - StTax
        
            txtRegularTime = dRegularTime
            txtOvertimeTime = dOvertimeTime
            txtRegularPay = CCur(RegularPay)
            txtOvertimePay = CCur(OvertimePay)
    
            txtGrossPay = CDbl(TotalEarnings)
            txtSocSecurityTax = CDbl(SocSecurityTax)
            txtMedicareTax = CDbl(MedTax)
            txtStateTax = CDbl(StTax)
            txtNetPay = CDbl(NetEarnings)
        Else
            MsgBox "No time sheet was found in that time " & _
    	       "frame for the indicated employee."
            cmdReset_Click
        End If
    
    cmdFindTimeSheet_Exit:
        Exit Sub
        
    cmdFindTimeSheet_Error:
        MsgBox "An error occured when retrieving the time " & _
    	       "sheet information" & vbCrLf & _
               "Please call the program vendor and " & _
    	       "report the error as follows:" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Reason:  " & Err.Description
        Resume Next
    End Sub
  22. In the Object combo box, select cmdApproveSubmitPayroll
  23. Implement the event as follows:
     
    Private Sub cmdApproveSubmitPayroll_Click()
    On Error GoTo cmdApproveSubmitPayroll_Error
    
        Dim curDatabase As Object
        Dim rstPayrolls As Object
        Dim fldPayroll As Object
        
        If IsNull(txtTimeSheetCode) Then
            MsgBox "You must enter the time sheet code."
            Exit Sub
        End If
        
        If IsNull(txtFederalTax) Or (txtFederalTax = "0.00") Then
            MsgBox "You must specify the value of the federal taxes."
            Exit Sub
        End If
        
        Set curDatabase = CurrentDb
        Set rstPayrolls = curDatabase.OpenRecordset("Payrolls")
        
        With rstPayrolls
            .AddNew
            .Fields("StartDate").Value = txtStartDate
            .Fields("EndDate").Value = txtEndDate
            .Fields("TimeSheetCode").Value = txtTimeSheetCode
            .Fields("EmployeeNumber").Value = txtEmployeeNumber
            .Fields("EmployeeName").Value = txtEmployeeName
            .Fields("HourlySalary").Value = txtHourlySalary
            .Fields("RegularTime").Value = txtRegularTime
            .Fields("RegularPay").Value = txtRegularPay
            .Fields("OvertimeTime").Value = txtOvertimeTime
            .Fields("OvertimePay").Value = txtOvertimePay
            .Fields("GrossPay").Value = txtGrossPay
            .Fields("FederalTax").Value = txtFederalTax
            .Fields("SocSecurityTax").Value = txtSocSecurityTax
            .Fields("MedicareTax").Value = txtMedicareTax
            .Fields("StateTax").Value = txtStateTax
            .Fields("NetPay").Value = txtNetPay
            .Fields("Notes").Value = txtNotes
           .Update
        End With
    
        Set rstPayrolls = Nothing
        Set curDatabase = Nothing
        
        MsgBox "The payroll has been issued."
        cmdReset_Click
    
    cmdApproveSubmitPayroll_Exit:
        Exit Sub
        
    cmdApproveSubmitPayroll_Error:
        MsgBox "There was a problem when submitting the pay roll."
        Resume cmdApproveSubmitPayroll_Exit
    End Sub
  24. In the Object combo box, select cmdClose
  25. Implement the event as follows:
     
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  26. Close Microsoft Visual Basic to return to Microsoft Access
  27. Create a few payrolls for previously created time sheets
     
    Payroll Processing
    Payroll Processing
    Payroll Processing
  28. Close the form
  29. On the Ribbon, click Create
  30. In the Forms section, click Form Design
  31. On the Ribbon, click Create
  32. In the forms section, click Form Design
  33. Save the form as PartSelection
  34. Design the form as follows (You don't have to follow the exact same design; you only need to have the same controls and names):
     
    Control Caption Name Row Source Type Format
    Combo Box Car Year: cbxCarYears Value List  
    Combo Box Make: cbxMakes Value List  
    Combo Box Model: cbxModels Value List  
    Combo Box Part Category: cbxCategories Value List  
    Text Box Part Name: txtPartName    
    Text Box Unit Price: txtUnitPrice   Fixed
    Text Box Part #: txtPartNumber    
    Button cmdClose Close    
  35. Double-click the Part Category text box
  36. In the Properties window, click Event and double-click On Change
  37. Implement the event as follows:
     
    Private Sub cbxCategories_Change()
        Dim i As Integer
        Dim dbAutoParts As Database
        Dim rstAutoParts As Recordset
        
        If cbxCarYears = "" Then
            MsgBox "You must select the car year"
            Exit Sub
        End If
        
        If cbxMakes = "" Then
            MsgBox "You must select the car make"
            Exit Sub
        End If
        
        If cbxModels = "" Then
            MsgBox "You must select the car model"
            Exit Sub
        End If
        
        If cbxCategories = "" Then
            MsgBox "You must select the part category"
            Exit Sub
        End If
        
        Set dbAutoParts = CurrentDb
        Set rstAutoParts = dbAutoParts.OpenRecordset( _
            "SELECT AutoParts.PartNumber, AutoParts.CarYear, " & _
            "       AutoParts.Make, AutoParts.Model, " & _
            "       AutoParts.Category , AutoParts.PartName , " & _
            "       AutoParts.UnitPrice " & _
            "FROM AutoParts " & _
            "WHERE AutoParts.CarYear = " & cbxCarYears & " AND " & _
            "      AutoParts.Make = '" & CStr(cbxMakes) & "' AND " & _
            "      AutoParts.Model = '" & CStr(cbxModels) & "' AND " & _
            "      AutoParts.Category = '" & CStr(cbxCategories) & "';")
        
        With rstAutoParts
            Do While Not .EOF
                For i = 0 To rstAutoParts.Fields.Count - 1
                    If rstAutoParts(i).Name = "PartName" Then
                        txtPartName = rstAutoParts(i).Value
                    End If
                    
                    If rstAutoParts(i).Name = "UnitPrice" Then
                        txtUnitPrice = rstAutoParts(i).Value
                    End If
                    
                    If rstAutoParts(i).Name = "PartNumber" Then
                        txtPartNumber = rstAutoParts(i).Value
                    End If
                Next
                
                .MoveNext
            Loop
        End With
    End Sub
 
 
 
 

Distinguishing Records

 

Selecting a Distinct Record

Consider the following table:

Employees

Starting in Lesson 22, we learned how to use the WHERE operator to specify a condition by which one or more records should be located. Here is an example:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Employees.LastName " & _
                   "FROM   Employees " & _
                   "WHERE  LastName = 'Sands';"

    txtLastName.ControlSource = "LastName"
End Sub

Employees

Notice that the statement produces more than one result, which means there is a duplicate result. You can create a statement that produces only the first record that matches the criterion. To support this, the SQL provides the DISTINCT keyword. To use it, precede the name of the field with DISTINCT. Here is an example:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT DISTINCT Employees.LastName " & _
                   "FROM   Employees " & _
                   "WHERE  LastName = 'Sands';"

    txtLastName.ControlSource = "LastName"
End Sub

Employees

Notice that, this time, we get only one, the first, record that responds to the criterion.

To get the DISTINCT operator to work, the statement may contain only one field. If it contains more than one but the combination would produce results that are not duplicate, the result would contain more than one record. If you want to use more than one field, the combination of fields must not have duplicate results.

Practical LearningPractical Learning: Distinguishing Records

  1. Right-click the button at the intersection of the rulers and click Build Event
  2. In the Chose builder dialog box, double-click Code Builder
  3. Implement the event as follows:
     
    Private Sub Form_Load()
        Dim i As Integer
        Dim dbAutoParts As Database
        Dim rstAutoParts As Recordset
    
        Set dbAutoParts = CurrentDb
        
        For i = Year(Date) + 1 To 1960 Step -1
            cbxCarYears.AddItem i
        Next
        
        cbxCarYears = ""
            
        Set rstAutoParts = dbAutoParts.OpenRecordset( _
                "SELECT DISTINCT AutoParts.Category " & _
                "FROM AutoParts " & _
                "ORDER BY AutoParts.Category ASC;")
        rstAutoParts.MoveFirst
    
        With rstAutoParts
            Do While Not .EOF
                For i = 0 To rstAutoParts.Fields.Count - 1
                    cbxCategories.AddItem rstAutoParts(i).Value
                Next
                
                .MoveNext
            Loop
        End With
    End Sub
  4. In the Object combo box, select cbxMakes
  5. In the Procedure combo box, select Change
  6. Implement the event as follows:
     
    Private Sub cbxMakes_Change()
        Dim i As Integer
        Dim dbAutoParts As Database
        Dim rstAutoParts As Recordset
        
        Set dbAutoParts = CurrentDb
        Set rstAutoParts = dbAutoParts.OpenRecordset( _
                "SELECT DISTINCT AutoParts.Make, " & _
                "AutoParts.Model FROM AutoParts " & _
                "WHERE AutoParts.Make = '" & CStr(cbxMakes) & "';")
    
        
        With rstAutoParts
            Do While Not .EOF
                For i = 0 To rstAutoParts.Fields.Count - 1
                    If rstAutoParts(i).Name = "Model" Then
                        cbxModels.AddItem rstAutoParts(i).Value
                    End If
                Next
                
                .MoveNext
            Loop
        End With
    End Sub
  7. In the Object combo box, select cmdClose
  8. Implement the event as follows:
     
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  9. From the Navigation Pane, double-click Part Selection to open it
  10. Again, from the Navigation Pane, double-click NewInvoice to open it
  11. Position both forms side by side
     
  12. In the Part Selection form, to get its part number, locate a Climate Control item for a 2004 Honda Civic 1.7 EX 4DR
     
    Part Selection
  13. Close the forms

Selecting a Distinct Record

If you are creating a SELECT statement that involves a relationship, use the DISTINCTROW operator to get distinct records. Here is an example:

SELECT DISTINCTROW Videos.CopyrightYear,
		   Sum(Videos.PriceBought) AS [Sum Of PriceBought],
		   Count(*) AS [Count Of Videos]
FROM Videos
GROUP BY Videos.CopyrightYear
HAVING (((Videos.CopyrightYear) Is Not Null));

 

 
 
   
 

Previous Copyright © 2005-2011 FunctionX, Inc. Next