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-2016, FunctionX, Inc. Next