Home

Introduction to Data Selections

 

Field Selection

 

Introduction

The main purpose of data filtering consists of selecting records. As you know already, records are stored in tables but they can be identified by the columns of a table. Therefore, before filtering records, you must first specify the columns that would be involved in your filtering.

Practical LearningPractical Learning: Introducing Filtering and Sorting

  1. Start Microsoft Access
  2. From the resources that accompany these lessons, open the ROSH1 database
  3. In the navigation Pane, right-click Students and click Design View
  4. In the Controls section of the Ribbon, click the Combo Box and click the left side under the Form Footer bar. If the Combo Box wizard starts, click Cancel
  5. In the Properties window, change its Name to cbxColumnNames
  6. Change the caption of the accompanying label to Sort by:
  7. Make sure the Control Wizard button of the Ribbon is down. In the Controls section of the Ribbon, click the Combo Box and click on the right side of the previously added combo box in the Form footer section
  8. In the first page of the Combo Box Wizard, click the second radio button (I Will Type In The Values That I Want) and click Next
  9. Click under Col1 and type Ascending Order
  10. Press the down arrow key and type Descending Order
  11. Click Next
  12. In the third page of the wizard, accept the first radio button and click Next
  13. Change the label to in and click Finish
  14. In the Properties window, change the combo box' Name to cbxSortOrder
  15. Using the objects in the Controls section of the Ribbon, complete the design of the Footer section as follows:
     
    Students
    Control Name Caption Additional Properties
    Combo Box cbxColumnNames Sort by: Row Source Type:  Value List
    Combo Box cbxSortOrder in  
    Button cmdRemoveFilterSort Remove Filter/Sort  
    Button cmdClose Close  
  16. Access the properties of the form by double-clicking the button at the intersection of the rulers and click the Events tab
  17. Double-click On Open to select [Event Procedure], then click its ellipsis button

Selecting a Field

Data selection in the SQL consists of using the SELECT keyword. The primary formula to follow is:

SELECT What FROM WhatObject;

The What factor of our syntax can be the name of a column of a table or query. The WhatObject factor can be the name of a table or a query.

To specify the column you want to select, replace the What factor in the syntax with the name of the desired column. Here is an example:

SELECT LastName FROM Employees;

Selecting all Fields

To select everything from a table or query, you can use the asterisk as the What factor of our formula. For example, to select all records, you would use the statement as follows:

SELECT * FROM Employees;

Alternatively, you can precede the * with the ALL keyword. Here is an example:

SELECT ALL * FROM Employees;

Selecting Some Fields

To consider more than one column in a statement, you can list them in the What factor of our syntax, separating them with a comma except for the last column. The syntax to use is:

SELECT Column1, Column2, Column_n FROM WhatObject;

Here is an example:

SELECT FirstName, LastName, HourlySalary
FROM Employees;

As mentioned earlier, to programmatically create a query, you can pass the SQL statement as the second argument of the CreateQueryDef() method. Here is an example that uses the Microsoft Access Library:

Private Sub cmdCreateQuery_Click()
    Dim curDatabase As Object
    Dim qryEmployees As Object
    Dim strStatement As String

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    strStatement = "SELECT DateHired, FirstName, " & _
                   "LastName, HourlySalary FROM Employees;"
    ' Create a new query named EmployeesInfo
    Set qryEmployees = curDatabase.CreateQueryDef("EmployeesInfo", strStatement)
End Sub

You can use the same approach with DAO.

When writing the name of a table, a query, or a column, if it's in more than one word, you must include it in square brackets. To be safe, even if the name is in one word, you should still include it in square brackets. Based on this, the above statement would be written as follows:

SELECT * FROM [Employees];

Another suggestion you can use is to qualify the name of each column to indicate the table or query it belongs to. To do this, type the name of the table or query, followed by a period, followed by the name of the column or the *. Here is an example:

SELECT Employees.FirstName, Employees.LastName
FROM Employees;

You can also delimit each name with square brackets as follows:

SELECT [Employees].[FirstName], [Employees].[LastName]
FROM [Employees];

Data Analysis: Sorting Records

 

Introduction

One of the actions you can take consists of rearranging the list of records in an order other than the one in which they were entered. For example, a user enters the list of students in the order they arrive. At one time the user may want to see a list of students in alphabetical order based on they last names. Rearranging a list of records is referred to as sorting. Microsoft Access provides the means of sorting records on all database objects, including tables, queries, and forms. 

To programmatically sort records on a table, a query, or a form, etc, call its OrderBy() method and pass it the name of the column on which the sorting would be based. After calling OrderBy(), access its OrderByOn Boolean property and set its value to True. Here is an example:

Private Sub cmdSortByLastName_Click()
    OrderBy = "LastName"
    OrderByOn = True
End Sub

To remove the sorting, access the OrderByOn and set its value to False.

Sorting Records With SQL

In the SQL, to sort a field in ascending order, you can include the ORDER BY clause in your statement. The syntax used would be:

SELECT What FROM WhatObject ORDER BY WhatField;

The field used as the basis must be recognized as part of the selected columns. Imagine you have created a list of staff members made of their first and last names in a table named Employees. If you want to order the list in alphabetical order based on the LastName column, you would use a statement such as:

SELECT FirstName, LastName FROM Employees ORDER BY LastName;

If you use the * operator to include all fields, you can order the list based on any of the table's fields, as we learned during data analysis. Imagine that you have created a query that includes all fields. The following statement would list the records of the Employees table based on the alphabetical order of the LastName column:

SELECT * FROM Employees ORDER BY LastName;

By default, records are ordered in ascending order. Nevertheless, the ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in ascending order of a query that includes the first and last names, the above statement can also be written as follows:

SELECT FirstName, LastName FROM Employees ORDER BY LastName ASC;

The second statement can be written as:

SELECT * FROM Employees ORDER BY LastName ASC;

If you want to sort records in descending order, use the DESC keyword instead. It produces the opposite result to the ASC effect. To sort records in reverse alphabetical order, the above two statements can be written as:

SELECT FirstName, LastName FROM Employees ORDER BY LastName DESC;

The second statement can be written as:

SELECT * FROM Employees ORDER BY LastName DESC;

If you want to programmatically create a query from one of these statements, remember that you can use the CreateQueryDef() method.

Practical LearningPractical Learning: Filtering and Sorting Records on a Form

  1. Implement the event as follows:
     
    Private Sub Form_Open(Cancel As Integer)
    On Error GoTo FormOpen_Err
        
        Dim curDatabase As Object
        Dim strColumnsNames As String
        Dim tblStudents As Object
        Dim fldColumn As Object
    
        ' Get a reference to the current database
        Set curDatabase = CurrentDb
        ' Get a reference to a table named Students
        Set tblStudents = curDatabase.TableDefs("Students")
        
        ' Retrieve the name of each column of the table and
        ' store each name in the strColumnsNames string
        For Each fldColumn In tblStudents.Fields
            strColumnsNames = strColumnsNames & fldColumn.Name & ";"
        Next
        
        ' Set the strColumnsNames string as the data source of the combo box
        cbxColumnNames.RowSource = strColumnsNames
        
        ' Select the name of the first column as the default of the combo box
        cbxColumnNames = tblStudents.Fields(0).Name
        cbxSortOrder = "Ascending Order"
        
        Exit Sub
    
    FormOpen_Err:
        MsgBox "There was an error when opening the form." & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description & vbCrLf & _
               "Please contact the program vendor if " & _
           "he is not sleeping at this time."
        Resume Next
    End Sub
  2. In the Object combo box, select cbxColumnNames
  3. In the Procedure combo box, select After Update and implement the event as follows:
     
    Private Sub cbxColumnNames_AfterUpdate()
        Dim strColumnName As String
        Dim strSortOrder As String
        
        ' Get the name of the column from the Sort By combo box
        strColumnName = cbxColumnNames
        
        ' Sort the records based on the column name from the combo box
        Me.OrderBy = strColumnName
        Me.OrderByOn = True
        
        ' Set the In combo box to ascending order by default
        cbxSortOrder = "Ascending Order"
    End Sub
  4. In the Object combo box, select cbxSortOrder
  5. In the Procedure combo box, select AfterUpdate and implement the event as follows:
     
    Private Sub cbxSortOrder_AfterUpdate()
        Dim strColumnName As String
        Dim strSortOrder As String
        
        strColumnName = cboColumnNames
        
        ' Unless the user selects Descending Order...
        If cbxSortOrder = "Descending Order" Then
            strSortOrder = "DESC"
        Else ' We will consider that it should be sorted in ascending order
            strSortOrder = "ASC"
        End If
        
        Me.OrderBy = strColumnName & " " & strSortOrder
        Me.OrderByOn = True
    End Sub
  6. In the Object combo box, select cmdRemoveFilterSort
  7. Implement the event as follows:
     
    Private Sub cmdRemoveFilterSort_Click()
        Me.OrderBy = "StudentID"
        Me.OrderByOn = True
        
        Me.cbxColumnNames = "StudentID"
        Me.cbxSortOrder = "Ascending Order"
    End Sub
  8. Return to the form and save it
  9. Using the combo box in the Form Footer section, try sorting the records by the LastName is descending order
     
    Students
  10. Click the Remove Filter/Sort button
  11. Try sorting the records in ascending order based on the date of birth
     
    Students
  12. Notice that the records with no date of birth appear first
  13. Close the form
  14. When asked whether you want to save, click Yes

Data Filtering

 

Introduction

Data filtering consists of isolating particular records based on a condition, also called a criterion or criteria. You start data filtering by selecting one or more columns that hold data you are interested in. This allows you to have access to all values of the table. Then, among the selected columns, instead using all available records, you specify a condition. Only the records that abide by the rule(s) you set would be produced.

Practical LearningPractical Learning: Introducing Data Filtering

  1. In the Navigation Pane, right-click SelectStudents and click Design View
  2. In the Controls section of the Ribbon, make sure the Use Control Wizard button is clicked Use Control Wizard.
    Click the Combo Box and click under the Form Footer bar
  3. In the first page of the wizard, click the second radio button
  4. Click Next
  5. In the second page of the wizard, click under Col1 and type All
  6. Press the down arrow key and type Male
  7. Press the down arrow key and type Female
  8. Press the down arrow key and type Unknown
     
    Combo Box Wizard
  9. Click Next
  10. Click Next
  11. Click Finish
  12. Complete the design of the form as follows:
     
    Students
    Control Name Caption
    Combo Box cbxGenders Show
    Label   Students
    Button cmdStudentsByGender Show
  13. Save the form

WHERE is the Criterion?

To specify a condition, you combine the SELECT expression with the WHERE operator. The basic formula to follow is:

SELECT What FROM WhatObject WHERE Expression;

The What factor is used to specify the column(s) whose data would be considered. The WhatObject is the name of the table (or query) that holds the data. The SELECT and the WHERE keywords are required. The Expression is the condition that will set the rule to follow. The Expression is a real expression. It can be made of the name of a column involved in a special conditional statement.

Programmatically Filtering Data

To programmatically perform data filtering on a table, a query, a form, or a report, use its Filter property and assign it the WHERE condition. To apply the filter, access its FilterOn property and assign it a value of True. To remove the filter, assign to False value to its FilterOn property.

Data Filtering With Operators

Imagine that you have a table of students and you want to extract only a list of male students. Yyou would write the condition as Gender = Male. This would be done as follows:

SELECT * FROM Students WHERE Gender="Male";

Remember that you can use the square brackets around the name of a table, query, or column in a SQL statement. Here is an example:

SELECT * FROM [Students] WHERE [Gender]="Male";

Here is an example where the names of of tables and columns are included in square brackets and the names of columns are qualified with periods:

SELECT [Students].[FirstName], [Students].[LastName]
FROM [Students]
WHERE [Students].[Gender]="Male";

The opposite to the equality condition is the not equality. For example, instead of getting a list of male students as above, to get a list of students who are not male, you can write the condition as <>"Male" as follows:

SELECT * FROM Students WHERE Gender<>"Male";

Practical LearningPractical Learning: Filtering Records WHERE True

  1. Right-click the Show button and click Build Event...
  2. In the Choose Builder dialog box, double-click Code Builder
  3. Implement the event as follows:
     
    Private Sub cmdStudentsByGender_Click()
        If IsNull(cbxGenders) Then
            RecordSource = "SELECT * FROM Students"
        End If
        
        If cbxGenders = "All" Then
            RecordSource = "SELECT * FROM Students"
        End If
        
        If cbxGenders = "Male" Then
            RecordSource = "SELECT * FROM Students WHERE Gender = 'Male'"
        End If
        
        If cbxGenders = "Female" Then
            RecordSource = "SELECT * FROM Students WHERE Gender = 'Female'"
        End If
        
        If cbxGenders = "Unknown" Then
            RecordSource = "SELECT * FROM Students WHERE Gender = 'Unknown'"
        End If
    End Sub
  4. Return to Microsoft Access
  5. Switch the form to Form View
  6. In the bottom combo box, select Female
  7. Click the Show button
     
    Red Oak High School
  8. Save the form

Conditionally Opening a Form or Report

Imagine you create a form that shows the records of a table or query. When navigating among the records, imagine the user comes to a particular record he or she wants to print. If you simply write normal code to open the related report, it would show all records from the beginning. Fortunately, Microsoft Access provides an easy mechanism to execute such a scenario.

If you add a button to a form in Design View, if the Button Wizard starts, you can follow it to select the report that would be opened when a button is clicked. Microsoft Access would write the code for you:

Private Sub cmdPreviewRentalOrder_Click()
On Error GoTo Err_cmdPreviewRentalOrder_Click

    Dim stDocName As String
    Dim strWHERECondition As String

    stDocName = "RentalOrders"
    strWHERECondition = "RentalOrderID = " & RentalOrderID
    DoCmd.OpenReport stDocName, acPreview, , strWHERECondition

Exit_cmdPreviewRentalOrder_Click:
    Exit Sub

Err_cmdPreviewRentalOrder_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewRentalOrder_Click
    
End Sub
 
 

 

 

Details on Column Selection

 

Hiding a Column

Consider a table created with the following code:

Private Sub cmdTable_Click()
    Dim dbCurrent As Object
    Dim tblEmployees As Object
    Dim fldEmployee As Object
    
    Set dbCurrent = CurrentDb
    
    Set tblEmployees = dbCurrent.CreateTableDef("Employees")
    
    Set fldEmployee = tblEmployees.CreateField("DateHired", dbDate)
    tblEmployees.Fields.Append fldEmployee

    Set fldEmployee = tblEmployees.CreateField("FirstName", dbText, 40)
    tblEmployees.Fields.Append fldEmployee

    Set fldEmployee = tblEmployees.CreateField("LastName", dbText, 40)
    tblEmployees.Fields.Append fldEmployee

    Set fldEmployee = tblEmployees.CreateField("Department", dbText, 50)
    tblEmployees.Fields.Append fldEmployee

    Set fldEmployee = tblEmployees.CreateField("HourlySalary", dbDouble)
    tblEmployees.Fields.Append fldEmployee
    
    dbCurrent.TableDefs.Append tblEmployees
    MsgBox "A table named Employees has been created"
    
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('10/22/2006', 'Jerry', 'Carlton', " & _
                 "'Corporate', 22.45);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('05/12/2000', 'Christopher', 'Haloux', " & _
                 "'Accounting', 28.05);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('12/05/2005', 'Grattie', 'Sanion', " & _
                 "'Human Resources', 18.45);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('08/08/2002', 'Nicolas', 'Sands', 'IT', 19.85);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('12/04/1998', 'Dennis', 'Fleming', " & _
                 "'Corporate', 14.55);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('06/10/2008', 'Julie', 'Woods', " & _
                 "'Corporate', 30.2);"
    DoCmd.RunSQL "INSERT INTO Employees(DateHired, FirstName, LastName, " & _
                 "Department, HourlySalary) " & _
                 "VALUES('12/05/2005', 'Barbara', 'Seans', " & _
                 "'Human Resources', 18.45);"
End Sub

Imagine that you want to create a list of employees using their names and you want to show their work department. Imagine that you want the list to include only the employees who work at the corporate office, that is, employees whose Department value is Corporate. You can create a SQL statement as follows:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Employees.DateHired, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Employees.Department " & _
                   "FROM Employees " & _
                   "WHERE Employees.Department = 'Corporate';"

    txtDateHired.ControlSource = "DateHired"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub

This would produce:

Columns

Notice that the Department column is included as part of the SELECT statement. Since the list includes employees who work at the corporate office and we know that this is what the query would produce, it becomes redundant, quite useless, to include the Department column in our list. Therefore, we can hide it. To create such a query, omit the column in the SELECT statement but include it as part of your WHERE condition. Here is an example:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Employees.DateHired, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName " & _
                   "FROM Employees " & _
                   "WHERE Employees.Department = 'Corporate';"

    txtDateHired.ControlSource = "DateHired"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
End Sub

Notice that the Department column is not included as part of the SELECT statement. This would produce:

Employees

The Alias Name of a Column

In your SELECT statement, after specifying the column(s) as we have done so far, when you execute the query, the name of each column would appear as the column header. When creating the table, if you had explicitly specified a caption for the column, the caption would display also when the query shows its result. On a query, instead of the default name used as the caption, if you want, you can display any string of your choice for a column header. To specify a column header other than the name of the column, if you are visually creating the SQL statement in the Select Query window, in the box that receives the name of the selected column, type the desired string, followed by a colon ":", followed by the actual name of the column from the table.

In Microsoft Access (unlike many other database environments), if the column has a caption that was set in the table, creating the alias would not have any effect in the query.

If you are manually writing the SQL statement, type the actual name of the column, followed by the AS keyword, followed by the desired string. If the desired column header is in one word, you can simply type it. Here is an example:

SELECT DateHired AS EmployedSince, LastName, HourlySalary
FROM Employees;

If the string is in more than one word or contains a symbol that could be confused with an operator, you should include the whole string between an opening square bracket and a closing square bracket. In fact, you should always include the string between square brackets. Here are two examples:

SELECT DateHired AS [EmployedSince], LastName, HourlySalary AS [Pay Rate]
FROM Employees;

You can also include the string in single-quotes. Here are two examples:

SELECT DateHired AS [EmployedSince], LastName, HourlySalary AS 'Pay Rate'
FROM Employees;

A Combination or Expression of Columns

When creating a query, instead of having separate columns, you can combine two or more columns to create a string or a value that is in fact an expression. For example, you can combine a first name and a last name to create a full name.

An expression that combines columns can be performed on text-based columns. such as a first name being added to a last name to get a full name. To create this type of expression, you can use the + operator to concatenate the string as in FirstName + " " + LastName. After creating the expression, because the result is not part of the table (or the query) from which the query is based, you must give an alias name to the result. Here is an example:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Employees.DateHired, " & _
                   "       Employees.LastName + ', ' + " & _
                   "       Employees.FirstName As FullName, " & _
                   "       Employees.Department " & _
                   "FROM Employees;"

    txtDateHired.ControlSource = "DateHired"
    txtFullName.ControlSource = "FullName"
    txtDepartment.ControlSource = "Department"
End Sub

Columns

Instead of the addition operator, you can use the ampersand & operator to perform the same operator. Instead of the single-quotes used to add a string in the expression, you can use double-quotes.

Besides string, you can create a type of expression that uses a date on the table, add a number to it to get a date on another day. An expression can also be used to perform a calculation on two or more columns such as employees weekly hours multiplied by their hourly salary to get their weekly salary.

Field Selection Into a Record Set

 

Introduction

In the previous lesson, we saw different ways of creating simple SELECT statement and we saw how to use them. We saw that, using fields from a table, we could create a SELECT statement and use it to generate a query in the Microsoft Access Object Library or in DAO:

Private Sub cmdCreateQuery_Click()
    Dim curDatabase As DAO.Database
    Dim qryEmployees As DAO.QueryDef
    Dim strStatement As String

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    strStatement = "SELECT FirstName, LastName FROM Employees;"
    ' Create a new query named EmployeesInfo
    Set qryEmployees = curDatabase.CreateQueryDef("EmployeesInfo", strStatement)
End Sub

In the same way, we can also call the RunSQL() method of the DoCmd object and pass a SQL statement to it. Besides these techniques, we can also use a record set object.

Practical LearningPractical Learning: Introducing Domain Aggregate Functions

  1. Create a blank database named Greenbelt Auto Parts1
  2. Close the default table without saving it
  3. From the resources that accompany these lessons, import the AutoParts table from the Exercise1 database
  4. Click the Office button and click Access Options...
  5. Click Current Database and click Overlapped Windows
  6. Click OK and click OK
  7. Close Microsoft Access
  8. Start Microsoft Access again and open the Greenbelt Auto Part database
  9. On the Ribbon, click Create
  10. In the Forms section, click Form Design
  11. 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
  12. On the form, double-click the button to access its Properties window.
    Change its Name to cmdCreateTables
  13. Change its Caption to Create Table
  14. Right-click the Create Tables button and click Build Event...
  15. In the Choose builder dialog box, double-click Code Builder
  16. Implement the event as follows:
     
    Private Sub cmdCreateTable_Click()
        DoCmd.RunSQL "CREATE TABLE Invoices(" & _
                     "InvoiceID AutoIncrement(1, 1) " & _
                     "           primary key not null, " & _
                     "InvoiceDate Date, CustomerName varchar(80), " & _
                     "CustomerAddress varchar(80), CustomerCity varchar(50), " & _
                     "CustomerState varchar(50), " & _
                     "CustomerZIPCode varchar(40), Part1Number varchar(20), " & _
                     "Part1Name varchar(50), Part1Quantity Integer, " & _
                     "Part1SubTotal double, Part2Number varchar(20), " & _
                     "Part2Name varchar(50), Part2Quantity Integer, " & _
                     "Part2SubTotal double, Part3Number varchar(20), " & _
                     "Part3Name varchar(50), Part3Quantity Integer, " & _
                     "Part3SubTotal double, Part4Number varchar(20), " & _
                     "Part4Name varchar(50), Part4Quantity Integer, " & _
                     "Part4SubTotal double, Part5Number varchar(20), " & _
                     "Part5Name varchar(50), Part5Quantity Integer, " & _
                     "Part5SubTotal double, Part6Number varchar(20), " & _
                     "Part6Name varchar(50), Part6Quantity Integer, " & _
                     "Part6SubTotal double, " & _
                     "PartsTotal double, TaxRate double, " & _
                     "TaxAmount double, InvoiceTotal double);"
        MsgBox "A table named Invoices has been created."
    End Sub
  17. Return to Microsoft Access
  18. Switch the form to Form View and click the button
  19. Click OK each time to create the table and create its records
  20. Close the form
  21. When asked whether you want to save it, click No
  22. On the Ribbon, click Create
  23. In the Forms section, click Form Design
  24. Save the form as NewInvoice
  25. 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):
     
    Invoice
    Control Caption Name Format Decimal Places
    Text Box Invoice Date: txtInvoiceDate Short Date  
    Group Box Customer Information      
    Text Box Name: txtCustomerName    
    Text Box Address: txtCustomerAddress    
    Text Box City: txtCustomerCity    
    Text Box State: txtCustomerState    
    Text Box ZIP Code: txtCustomerZIPCode    
    Label Part #      
    Label Part Name/Description      
    Label Unit Price      
    Label Qty      
    Label Sub Total      
    Text Box   txtPart1Number    
    Text Box   txtPart1Name    
    Text Box   txtPart1UnitPrice Fixed  
    Text Box   txtPart1Quantity Fixed 0
    Text Box   txtPart1SubTotal Fixed  
    Text Box   txtPart2Number    
    Text Box   txtPart2Name    
    Text Box   txtPart2UnitPrice Fixed  
    Text Box   txtPart2Quantity Fixed 0
    Text Box   txtPart2SubTotal Fixed  
    Text Box   txtPart3Number    
    Text Box   txtPart3Name    
    Text Box   txtPart3UnitPrice Fixed  
    Text Box   txtPart3Quantity Fixed 0
    Text Box   txtPart3SubTotal Fixed  
    Text Box   txtPart4Number    
    Text Box   txtPart4Name    
    Text Box   txtPart4UnitPrice Fixed  
    Text Box   txtPart4Quantity Fixed 0
    Text Box   txtPart4SubTotal Fixed  
    Text Box   txtPart5Number    
    Text Box   txtPart5Name    
    Text Box   txtPart5UnitPrice Fixed  
    Text Box   txtPart5Quantity Fixed 0
    Text Box   txtPart5SubTotal Format: Fixed  
    Text Box   txtPart6Number    
    Text Box   txtPart6Name    
    Text Box   txtPart6UnitPrice Fixed  
    Text Box   txtPart6Quantity Fixed 0
    Text Box   txtPart6SubTotal Fixed  
    Line        
    Text Box Items Total: txtPartsTotal Fixed  
    Text Box Tax Rate: txtTaxRate Percent  
    Text Box Tax Amount: txtAmountAmount Fixed  
    Text Box Invoice Total: txtInvoiceTotal Fixed  
    Line        
    Button btnSave Save    
    Button btnClose Close    
  26. Right-click the Reset Invoice button and click Build Event...
  27. Double-click Code Builder
  28. Implement the event as follows:
     
    Private Sub cmdResetInvoice_Click()
        Me.txtInvoiceDate = Date: Me.txtCustomerName = ""
        Me.txtCustomerAddress = "": Me.txtCustomerCity = ""
        Me.txtCustomerState = "": Me.txtCustomerZIPCode = ""
        Me.txtPart1Number = "": Me.txtPart1Name = ""
        Me.txtPart1UnitPrice = "0.00": Me.txtPart1Quantity = "0"
        Me.txtPart1SubTotal = "0.00": Me.txtPart2Number = ""
        Me.txtPart2Name = "": Me.txtPart2UnitPrice = "0.00"
        Me.txtPart2Quantity = "0": Me.txtPart2SubTotal = "0.00"
        Me.txtPart3Number = "": Me.txtPart3Name = ""
        Me.txtPart3UnitPrice = "0.00": Me.txtPart3Quantity = "0"
        Me.txtPart3SubTotal = "0.00": Me.txtPart4Number = ""
        Me.txtPart4Name = "": Me.txtPart4UnitPrice = "0.00"
        Me.txtPart4Quantity = "0": Me.txtPart4SubTotal = "0.00"
        Me.txtPart5Number = "": Me.txtPart5Name = ""
        Me.txtPart5UnitPrice = "0.00": Me.txtPart5Quantity = "0"
        Me.txtPart5SubTotal = "0.00": Me.txtPart6Number = ""
        Me.txtPart6Name = "": Me.txtPart6UnitPrice = "0.00"
        Me.txtPart6Quantity = "0": Me.txtPart6SubTotal = "0.00"
        Me.txtPartsTotal = "0.00": Me.txtTaxRate = "5.75"
        Me.txtTaxAmount = "0.00": Me.txtInvoiceTotal = "0.00"
    End Sub
  29. In the Object combo box, select Form
  30. Implement the event as follows:
     
    Private Sub Form_Load()
        cmdResetInvoice_Click
    End Sub
  31. In the Object combo box, select txtPart1Quantity
  32. In the Procedure combo box, select LostFocus and implement the event as follows:
     
    Private Sub CalculateInvoice()
        Dim Part1UnitPrice As Double, Part1Quantity As Integer
        Dim Part1SubTotal As Double, Part2UnitPrice As Double
        Dim Part2Quantity As Integer, Part2SubTotal As Double
        Dim Part3UnitPrice As Double, Part3Quantity As Integer
        Dim Part3SubTotal As Double, Part4UnitPrice As Double
        Dim Part4Quantity As Integer, Part4SubTotal As Double
        Dim Part5UnitPrice As Double, Part5Quantity As Integer
        Dim Part5SubTotal As Double, Part6UnitPrice As Double
        Dim Part6Quantity As Integer, Part6SubTotal As Double
        Dim PartsTotal As Double, TaxRate As Double
        Dim TaxAmount As Double, InvoiceTotal As Double
        
        Part1UnitPrice = CDbl(Nz(txtPart1UnitPrice))
        Part1Quantity = CInt(Nz(txtPart1Quantity))
        Part1SubTotal = Part1UnitPrice * Part1Quantity
        
        Part2UnitPrice = CDbl(Nz(txtPart2UnitPrice))
        Part2Quantity = CInt(Nz(txtPart2Quantity))
        Part2SubTotal = Part2UnitPrice * Part2Quantity
        
        Part3UnitPrice = CDbl(Nz(txtPart3UnitPrice))
        Part3Quantity = CInt(Nz(txtPart3Quantity))
        Part3SubTotal = Part3UnitPrice * Part3Quantity
        
        Part4UnitPrice = CDbl(Nz(txtPart4UnitPrice))
        Part4Quantity = CInt(Nz(txtPart4Quantity))
        Part4SubTotal = Part4UnitPrice * Part4Quantity
        
        Part5UnitPrice = CDbl(Nz(txtPart5UnitPrice))
        Part5Quantity = CInt(Nz(txtPart5Quantity))
        Part5SubTotal = Part5UnitPrice * Part5Quantity
        
        Part6UnitPrice = CDbl(Nz(txtPart6UnitPrice))
        Part6Quantity = CInt(Nz(txtPart6Quantity))
        Part6SubTotal = Part6UnitPrice * Part6Quantity
        
        TaxRate = CDbl(Nz(txtTaxRate))
        
        PartsTotal = Part1SubTotal + Part2SubTotal + Part3SubTotal + _
                     Part4SubTotal + Part5SubTotal + Part6SubTotal
        TaxAmount = PartsTotal * TaxRate / 100
        InvoiceTotal = PartsTotal + TaxAmount
        
        txtPart1SubTotal = FormatNumber(Part1SubTotal)
        txtPart2SubTotal = FormatNumber(Part2SubTotal)
        txtPart3SubTotal = FormatNumber(Part3SubTotal)
        txtPart4SubTotal = FormatNumber(Part4SubTotal)
        txtPart5SubTotal = FormatNumber(Part5SubTotal)
        txtPart6SubTotal = FormatNumber(Part6SubTotal)
        
        txtPartsTotal = FormatNumber(PartsTotal)
        txtTaxAmount = FormatNumber(TaxAmount)
        txtInvoiceTotal = FormatNumber(InvoiceTotal)
    End Sub
    
    Private Sub txtPart1Quantity_LostFocus()
        CalculateInvoice
    End Sub
  33. In the Object combo box, select txtPart2Quantity
  34. In the Procedure combo box, select LostFocus and implement the event as follows:
     
    Private Sub txtPart2Quantity_LostFocus()
        CalculateInvoice
    End Sub
  35. In the Object combo box, select txtPart3Quantity
  36. In the Procedure combo box, select LostFocus and implement the event as follows:
     
    Private Sub txtPart3Quantity_LostFocus()
        CalculateInvoice
    End Sub
  37. In the Object combo box, select txtPart4Quantity
  38. In the Procedure combo box, select LostFocus and implement the event as follows:
     
    Private Sub txtPart4Quantity_LostFocus()
        CalculateInvoice
    End Sub
  39. In the Object combo box, select txtPart5Quantity
  40. In the Procedure combo box, select LostFocus and implement the event as follows:
     
    Private Sub txtPart4Quantity_LostFocus()
        CalculateInvoice
    End Sub
  41. In the Object combo box, select txtPart6Quantity
  42. In the Procedure combo box, select LostFocus and implement the event as follows:
     
    Private Sub txtPart5Quantity_BeforeUpdate(Cancel As Integer)
        CalculateInvoice
    End Sub
  43. In the Object combo box, select cmdClose
  44. Implement the event as follows:
     
    Private Sub txtPart6Quantity_BeforeUpdate(Cancel As Integer)
        CalculateInvoice
    End Sub
  45. In the Object combo box, select txtPart2Quantity

Data Selection in a Recordset

So far, when we needed a record set, we passed the name of a table or query to it. This meant that we were getting all fields from the table or query. Here is an example:

Private Sub cmdSelectSomeFields_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    Dim fldEmployee As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    . . . Use the record set here
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

In some case, you may not be interested in all fields from a table or table. In this case, you can create a record set that uses only selected fields from a table or query.

To create a record set that involves only some desired fields, create a SELECT statement and pass it to a Recordset object. Here is an example:

Private Sub cmdSelectSomeFields_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    Dim fldEmployee As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset( _
            "SELECT FirstName, LastName FROM Employees")
    
    . . . Use the record set here
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

You can use any of the techniques we saw in the previous lesson and those we will study in next sections for your record set. After creating the record set, you can use it as we have done so far. Keep in mind that only the fields that are part of the SELECT statement will be available to you.

Practical LearningPractical Learning: Looking for a Record in a Domain

  1. In the Object combo box, select txtPart1Number
  2. In the Properties window, click Event and double On Lost Focus
  3. Click its ellipsis button
  4. Implement the event as follows:
     
    Private Sub txtPart1Number_LostFocus()
    On Error GoTo txtPart1Number_Error
    
        Dim i As Integer
        Dim dbAutoParts As Database
        Dim rstAutoParts As Recordset
        
        If txtPart1Number = "" Then
            Exit Sub
        End If
        
        Set dbAutoParts = CurrentDb
        Set rstAutoParts = dbAutoParts.OpenRecordset( _
            "SELECT AutoParts.PartName , " & _
            "       AutoParts.UnitPrice " & _
            "FROM AutoParts " & _
            "WHERE AutoParts.PartNumber = '" & txtPart1Number & "';")
        
        With rstAutoParts
            Do While Not .EOF
                For i = 0 To rstAutoParts.Fields.Count - 1
                    If rstAutoParts(i).Name = "PartName" Then
                        Me.txtPart1Name = rstAutoParts(i).Value
                    End If
                    
                    If rstAutoParts(i).Name = "UnitPrice" Then
                        txtPart1UnitPrice = rstAutoParts(i).Value
                    End If
                    
                    txtPart1Quantity = "1"
                    txtPart1SubTotal = txtPart1UnitPrice
    
    		CalculateInvoice
                Next
                
                .MoveNext
            Loop
        End With
    
    txtPart1Number_Exit:
        Exit Sub
        
    txtPart1Number_Error:
        MsgBox "The system encountered a problem when " & _
               "trying to locate the part." & vbCrLf & _
               "Error #:" & vbTab & Err.Number & vbCrLf & _
               "Error Message: " & Err.Description
        Resume txtPart1Number_Exit
    End Sub
  5. In the Object combo box, select txtPart2Number
  6. In the Procedure combo box, select LostFocus
  7. Implement the event using the same code as above but replacing txtPart1Number with txtPart2Number, txtPart1Name with txtPart2Name, txtItem1UnitPrice with txtItem2UnitPrice, and txtPart1SubTotal and txtPart2SubTotal 
  8. In the Object combo box, select txtPart3Number
  9. In the Procedure combo box, select LostFocus
  10. Implement the event using the same code as above but replacing txtPart1Number with txtPart3Number, txtPart1Name with txtPart3Name, txtItem1UnitPrice with txtItem3UnitPrice, and txtPart1SubTotal and txtPart3SubTotal 
  11. In the Object combo box, select txtPart4Number
  12. In the Procedure combo box, select LostFocus
  13. Implement the event using the same code as above but replacing txtPart1Number with txtPart4Number, txtPart1Name with txtPart4Name, txtItem1UnitPrice with txtItem4UnitPrice, and txtPart1SubTotal and txtPart4SubTotal  
  14. In the Object combo box, select txtPart5Number
  15. In the Procedure combo box, select LostFocus
  16. Implement the event using the same code as above but replacing txtPart1Number with txtPart5Number, txtPart1Name with txtPart5Name, txtItem1UnitPrice with txtItem5UnitPrice, and txtPart1SubTotal and txtPart5SubTotal 
  17. In the Object combo box, select txtPart6Number
  18. In the Procedure combo box, select LostFocus
  19. Implement the event using the same code as above but replacing txtPart1Number with txtPart6Number, txtPart1Name with txtPart6Name, txtItem1UnitPrice with txtItem6UnitPrice, and txtPart1SubTotal and txtPart6SubTotal
  20. In the Object combo box, select cmdClose
  21. Implement the event as follows:
     
    Private Sub cmdClose_Click()
    On Error GoTo Err_cmdClose_Click
    
        DoCmd.Close
    
    Exit_cmdClose_Click:
        Exit Sub
    
    Err_cmdClose_Click:
        MsgBox Err.Description
        Resume Exit_cmdClose_Click
    End Sub
  22. Close Microsoft Visual Basic and return to Microsoft Access
  23. Click the first text box under Part #
  24. Type 118448 and press Tab
  25. Type 4 and press Tab
     
    Invoice
  26. Type 283615 and press Tab twice
     
    Invoice
  27. Close the form
  28. When asked whether you want to save, click Yes
 
 
   
 

Previous Copyright © 2005-2011 FunctionX, Inc. Next