Microsoft Access Database Development With VBA

Introduction to Data Analysis

 

Field Selection

 

Introduction

Consider the following tables:

Private Sub cmdCreateTables_Click()
    DoCmd.RunSQL "CREATE TABLE Departments(" & _
                 "DeptCode char(5) PRIMARY KEY NOT NULL," & _
                 "Department varchar(50));"
    MsgBox "A table named Departments has been created."
    
    DoCmd.RunSQL "INSERT INTO Departments VALUES('HMNRS', 'Human Resources');"
    DoCmd.RunSQL "INSERT INTO Departments VALUES('RESDV', 'Research & Development');"
    DoCmd.RunSQL "INSERT INTO Departments VALUES('ITMNG', 'Information Technology & Management');"
    
    DoCmd.RunSQL "CREATE TABLE Employees(" & _
                 "EmplNbr char(7) PRIMARY KEY NOT NULL," & _
                 "FirstName varchar(20)," & _
                 "LastName varchar(20) NOT NULL," & _
                 "DeptCode char(5) NULL);"
    MsgBox "A table named Employees has been created."
    
    DoCmd.RunSQL "INSERT INTO Employees VALUES('204-517', 'Albertine', 'Walley', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('792-826', 'Rick', 'Bowden', 'HMNRS');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('274-759', 'Lisa', 'Brayer');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('729-475', 'Wally', 'Bastion', 'HMNRS');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('759-794', 'Sylla', 'Nguyen', 'ITMNG');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('824-004', 'Donald', 'Wallace', 'HMNRS');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('279-405', 'Hermine', 'Khan', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('248-295', 'Jamie', 'Thomas');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('979-514', 'Campbell', 'Barns');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('297-924', 'Paula', 'Barners', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('249-920', 'Chrissie', 'Dentd', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('204-058', 'Ernestine', 'Essiane', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('828-294', 'Hallio', 'Randt');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('279-750', 'Helene', 'Cranston', 'RESDV');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('294-759', 'Hoanga', 'Klein', 'ITMNG');"
    DoCmd.RunSQL "INSERT INTO Employees(EmplNbr, FirstName, LastName) " & _
                 "VALUES('974-003', 'Phaolin', 'Krazucki');"
    DoCmd.RunSQL "INSERT INTO Employees VALUES('208-095', 'Frank', 'Burditt', 'HMNRS');"
End Sub

Once a table contains values, you can use them as you see fit. For example, you can retrieve the values of a table. This is referred to as selecting one or more values from a table. To programmatically perform this operation, you use the SQL.

Practical LearningPractical Learning: Introducing Data Analysis

  1. Start Microsoft Access
  2. Open the FunDS1 database from Lesson 15

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 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 formula, separating them with a comma. The formula to use is:

SELECT Column1, Column2, Column_n FROM WhatObject;

Here is an example:

SELECT FirstName, LastName, HourlySalary
FROM Employees;

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];

Using Built-In Functions

The Visual Basic language is equipped with an impressive library of functions. These functions can also be used in queries and even included in SQL statements. The SQL interpreter of Microsoft Access can recognize these functions as long as you use them appropriately.

Imagine that you want to create a column in a query and that column should hold the full name of each employee. In a column of a table, you could use an expression such as:

Employee: [FirstName] & " " & [MiddleName] & " " & [LastName] 

The SQL statement would be:

SELECT Employees.DateHired,
       [FirstName] & " " & [MiddleName] & " " & [LastName] AS Employee
FROM   Employees;

Imagine that you only want to include a middle initial instead of the whole middle name. You can use the Left$ function to retrieve the first character of the middle name and include the call to that function in your query. Here is an example:

SELECT Employees.DateHired,
       [FirstName] & " " & Left([MiddleName],1) & " " & [LastName]
FROM   Employees;

In this case, some records don't have a middle initial because they don't have a name. For the records that don't display a middle name, we can write a conditional statement, using the IIf() function, to check it and taking the appropriate action accordingly. Here is the result:

SELECT Employees.DateHired,
       IIf(IsNull([MiddleName]),
           [FirstName] & " " & [LastName],[FirstName] & " " & 
	   UCase(Left([MiddleName],1)) & " " & [LastName])
FROM Employees;

In the same way, you can use any of the built-in functions we reviewed in previous lessons.

Queries Fundamentals

 

Introduction to Queries

After creating a table and filling it up with some values, you can explore them. One way you can do this consists of isolating records based on specific conditions. This technique of isolating records is also referred to as filtering. To filter records of a table and display the results to the user, you have various alternatives.

Data filtering is performed using the data manipulation language (DML) of the SQL and other means provided by Microsoft Access. To filter data, you can either create a query or write a SQL statement.

Practical LearningPractical Learning: Creating a Query

  1. To start a new form, on the Ribbon, click CREATE
  2. In the Forms section, click Form Design
  3. Double-click the button at the intersection of the rulers.
    In the Properties window, click Record Source, then click its ellipsis button
  4. In the Tables property page of the Show Table dialog box, double-click Manufacturers, StoreItems, Categories, and SubCategories
  5. On the Show Table dialog box, click Close
  6. From the StoreItems list, double-click ItemNumber and DateEntered
  7. From the Manufacturers list, double-click Manufacturer
  8. From the Categories list, double-click Category
  9. From the SubCategories list, double-click SubCategory
  10. From the StoreItems list, double-click ItemName, UnitPrice, and DiscountRate
  11. In the lower section of the window, click DiscountRate and press Tab
  12. Type DiscountAmount: IIf(IsNull(DiscountRate), '', FormatNumber(UnitPrice * DiscountRate))
  13. Press Tab
  14. Type AfterDiscount: IIf(IsNull(DiscountRate), '', FormatNumber(UnitPrice - DiscountAmount))
    Store Items
  15. Close the Query Builder
  16. When asked whether you want to save, click Yes
  17. Save the form as InventoryAnalysis
  18. Complete the design of the form as follows:
    Store Items Inventory
    
    
  19. Switch the form to Form View to see the result:
    Store Items Inventory
    
    
  20. Switch the form to Design View

Opening a Query

Once a query has been created and saved, it becomes a regular database object. If a query exists already, to use it, the user can open it like a table. To programmatically open a query, you can call the OpenQuery() method of the DoCmd object. This method takes one string argument as the name of the query. Here is an example:

Private Sub cmdOpenVideoTitles_Click()
    DoCmd.OpenQuery "VideoTitles"
End Sub

Closing a Query

After using a query, the user can close it like a regular window by clicking its system Close button. To programmatically close a query, you can call the Close() method of the DoCmd object, passing the first argument as acQuery and the second argument as the name of the query. Here is an example:

Private Sub cmdCloseVideoTitles_Click()
    DoCmd.Close acQuery, "VideoTitles"
End Sub

When this method is called, it checks whether the query is opened. If a query with that name is opened, it would be closed. If no query with that name is opened, nothing would happen.

Indexes

 

Introduction

As you may know from reading books, an index is a list of words that makes it easy to locate information. When it comes to a book, an author or editor can check each chapter to get key words, create a list of those word and put that list at the end of the book. When it comes to a database, you can ask the database engine to use the values of one or more columns as the basis of an index. In other words, you would create an index based on one or more fields and the databse engine would take care of everytihng behind the scenes, which would consist of eventually find the records when a request is made.

Creating an Index

To programmatically create an index in SQL, use the following formula:

CREATE [UNIQUE] INDEX IndexName ON ObjectName(Column(s) Options);

The CREATE INDEX expression and the ON keyword are required. Like every object, an index must have a name, in this case ObjectName. You must also specify the table or a query that holds the list whose index you wan to create. You must also specify the column(s) that has(have) the values used for the index. Here is an example of creating an index:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors" & _
                 "(" & _
                 "      ContractorCode int, " & _
                 "      FirstName varchar(20)," & _
                 "      LastName varchar(20) NOT NULL," & _
                 "      Title varchar(50) NULL," & _
                 "      HourlySalary double" & _
                 ");"
End Sub

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE INDEX LocateContractors ON Contractors(ContractorCode);"
End Sub

In most cases, you use only one column as the basis of your index. You can also use more than one column. In this case, in the parentheses of the name of the table, list the columns separated by commas. Here is an example:

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE INDEX FindContractors " & _
                 "ON Contractors(LastName, FirstName);"
End Sub

By habit or pure tradition, most people start the name of an index with either idx or IDX_.

Options on Indexes

There are many characteristics to apply to an index.

If you create an index on a column that includes null values, the index may end up with null values. If you want the database engine to exclude null values, add a WITH DISALLOW NULL clause. Here is an example:

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE INDEX IDX_Contractors " & _
                 "ON Contractors(ContractorCode) " & _
                 "WITH DISALLOW NULL;"
End Sub

Another option is to ask the databsae engine to ignore null values in the index you are creating. To do this, add a WITH IGNORE NULL clause. Here is an example:

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE INDEX IDX_Contractors " & _
                 "ON Contractors(ContractorCode) " & _
                 "WITH IGNORE NULL;"
End Sub

In most cases, you want to make sure that all words used in an index are unique (an index with a repeating word can be confusing). Normally, this is the default characteristic of an index. Still, if you want to enforce it, you can precede the word INDEX with UNIQUE. Here is an example:

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE UNIQUE INDEX IDX_Contractors " & _
                 "ON Contractors(ContractorCode);"
End Sub

In most cases, an index is created on the column(s) used as the primary key. Normally, if your table formally includes a primary key, that (those) column(s) is(are) used as the index. Otherwise, when creating an index, if you want to indicate that it is based on the column(s) used as the primary key, add a WITH PRIMARY clause to it. Here is an example:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors" & _
                 "(" & _
                 "      ContractorCode int, " & _
                 "      FirstName varchar(20)," & _
                 "      LastName varchar(20) NOT NULL," & _
                 "      Title varchar(50) NULL," & _
                 "      HourlySalary double" & _
                 ");"
End Sub

Private Sub cmdCreateIndex_Click()
    DoCmd.RunSQL "CREATE UNIQUE INDEX IDX_Contractors " & _
                 "ON Contractors(ContractorCode) " & _
                 "WITH PRIMARY;"
End Sub

Deleting an Index

To delete an index, use the following forrmula:

DROP INDEX IndexName ON TableName;

Here is an example:

Private Sub cmdDeleteIndex_Click()
    DoCmd.RunSQL "DROP INDEX IDX_Contractors ON Contractors;"
End Sub

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.

Practical LearningPractical Learning: Introducing Sorting Records

  1. On the Ribbon, click Design and, in the Controls section, click More.
    Make sure the Use Control Wizards option is selected Use Coontrol Wizards.
    In the Controls section of the Ribbon, click the Combo Box Combo Box and click the left side under the Form Footer bar
  2. 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
  3. Click under Col1 and type Item Number
  4. Press the down arrow key and type Date Entered
  5. Complete the list with Manufacturer, Category, Sub-Category, Item Name, Unit Price, and Price After Discount
  6. Click Next
  7. In the third page of the wizard, accept the first radio button and click Next
  8. Change the label to Sort by:
  9. Click Finish
  10. In the Properties window, change its Name to cbxColumnNames
  11. 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
  12. 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
  13. Click under Col1 and type Ascending Order
  14. Press the down arrow key and type Descending Order
     
    Combo Box Wizard
  15. Click Next
  16. In the third page of the wizard, accept the first radio button and click Next
  17. Change the label to in and click Finish
  18. In the Properties window, change the combo box' Name to cbxSortOrder
  19. Using the objects in the Controls section of the Ribbon, complete the design of the Footer section as follows:
     
    Store Items Inventory
    
    
    Control Name Caption Additional Properties
    Combo Box cbxColumnNames Sort by: Row Source Type: Value List
    Combo Box cbxSortOrder in  
    Button cmdRemoveFilterSort Remove Filter/Sort  

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: Sorting Records on a Form

  1. Double-click the Sort By combo box to access its properties and click the Events tab
  2. Double-click After Update, then click its ellipsis button Ellipsis
  3. Implement the event as follows:
    Option Compare Database
    Option Explicit
    
    Private strColumnName As String
    Private strSortOrder As String
    
    Private Sub cbxColumnNames_AfterUpdate()
    On Error GoTo cbxColumnNames_AfterUpdate_Error
      
        ' Get the string selected in the Sort By combo box
        ' and find its equivalent column name
        If cbxColumnNames = "Item Number" Then
            strColumnName = "ItemNumber"
        ElseIf cbxColumnNames = "Date Entered" Then
            strColumnName = "DateEntered"
        ElseIf cbxColumnNames = "Manufacturer" Then
            strColumnName = "Manufacturer"
        ElseIf cbxColumnNames = "Category" Then
            strColumnName = "Category"
        ElseIf cbxColumnNames = "Sub-Category" Then
            strColumnName = "SubCategory"
        ElseIf cbxColumnNames = "Item Name" Then
            strColumnName = "ItemName"
        ElseIf cbxColumnNames = "Unit Price" Then
            strColumnName = "UnitPrice"
        ElseIf cbxColumnNames = "Price After Discount" Then
            strColumnName = "AfterDiscount"
        Else
            strColumnName = ""
        End If
        
        ' 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"
    
        Exit Sub
        
    cbxColumnNames_AfterUpdate_Error:
        MsgBox "There was an error when trying to sort the records. " & _
               "Please report the error as follows." & 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
  4. In the Object combo box, select cbxSortOrder
  5. In the Procedure combo box, select AfterUpdate
  6. Implement the event as follows:
    Private Sub cbxSortOrder_AfterUpdate()
    On Error GoTo cbxSortOrder_AfterUpdate_Error
        
        ' 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
        Exit Sub
        
    cbxSortOrder_AfterUpdate_Error:
        MsgBox "There was an error when trying to sort the records. " & _
               "Please report the error as follows." & 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
  7. In the Object combo box, select cmdRemoveFilterSort
  8. Implement the event as follows:
    Private Sub cmdRemoveFilterSort_Click()
        Me.OrderBy = ""
        Me.OrderByOn = False
        
        Me.cbxColumnNames = "Item Number"
        Me.cbxSortOrder = "Ascending Order"
    End Sub
  9. Return to the form and save it
  10. Using the combo box in the Form Footer section, try sorting the records by the Manufacturer
    Store Items Inventory
    
    
  11. Click the Remove Filter/Sort button
  12. Sort the records by Unit Price in descending order
    Store Items Inventory
    
    
  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 InventoryAnalysis and click Design View
  2. In the Controls section of the Ribbon, 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 Men
  7. Press the down arrow key and type Girls
  8. Press the down arrow key and type Boys
  9. Press the down arrow key and type Women
     
    Combo Box Wizard
  10. Click Next
  11. Click Next
  12. Click Finish
  13. In the Controls section of the Ribbon, click the Combo Box and click under the Form Footer bar
  14. In the first page of the wizard, click the second radio button
  15. Click Next
  16. In the second page of the wizard, click under Col1 and type lower than
  17. Press the down arrow key and type lower than or equal to
  18. Press the down arrow key and type equal to
  19. Press the down arrow key and type higher than or equal to
  20. Press the down arrow key and type higher than
  21. Press the down arrow key and type different from
  22. Click Next
  23. Click Next
  24. Click Finish
  25. In the Controls section of the Ribbon, click the Text Box Text Box and click under the Form Footer bar
  26. Delete its accompanying label
  27. In the Controls section of the Ribbon, click the Button Text Box and click under the Form Footer bar. If the wizard starts, click Cancel
  28. Complete the design of the form as follows:
     
    Store Items Inventory
    Control Name Caption
    Combo Box Combo Box cbxCategories Show items for
    Combo Box Combo Box cbxOperators  
    Text Box Text Box txtUnitPrice Show items whose price is
    Button Text Box cmdShowPrices Show
  29. 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, you can use its Filter property and assign it the WHERE condition. To apply the filter, one solution is to access its FilterOn property and assign it a value of True. To remove the filter, assign the False value to its FilterOn property. As an alternative, the DoCmd object has a method named ApplyFilter. Its syntax is:

DoCmd.ApplyFilter(Optional FilterName, Optional Condition, Optional ControlName)

The FilterName argument can be the name of a filter or of a query in the database. In most cases, you will leave this optional argument blank. The Condition argument is the actual filter you want to use. The ControlName argument is the name of the control on which to apply the filter. If this argument is left empty, the filter can be applied to the form or report where the method was called. Here is an example of calling this method:

Private Sub cmdFilter_Click()
    DoCmd.ApplyFilter , "Employees.EmplNbr = '249-920'"
End Sub

Practical LearningPractical Learning: Filtering Data

  1. On the form, click the Show Items For combo box
  2. In the Properties window, click Event and double-click After Update
  3. Click its ellipsis button and implement the event as follows:
    Private Sub cbxCategories_AfterUpdate()
    On Error GoTo cbxCategories_AfterUpdate_Error
    
        Filter = "Category = '" & cbxCategories & "'"
        FilterOn = True
        
        Exit Sub
        
    cbxCategories_AfterUpdate_Error:
        MsgBox "There was an error when trying to filter the records. " & _
               "Please report the error as follows." & 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
  4. Locate the Click event of the cmdRemoveFilterSort button and change it as follows:
    Private Sub cmdRemoveFilterSort_Click()
        Me.OrderBy = ""
        Me.Filter = ""
        
        Me.OrderByOn = False
        Me.FilterOn = False
        
        Me.cbxColumnNames = "Item Number"
        Me.cbxSortOrder = "Ascending Order"
        cbxCategories = ""
        cbxOperator = ""
        txtUnitPrice = "0.00"
    End Sub
  5. Return to Microsoft Access
  6. Switch the form to Form View
  7. In the Show Items For combo box, select Boys
  8. Click the Show button
    Store Items Inventory
    
  9. Save the form
  10. Return to Microsoft Visual Basic

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. In the Object combo box, select cmdShowPrices
  2. Implement the event as follows:
    Private Sub cmdShowPrices_Click()
    On Error GoTo cmdShowPrices_Click_Error
    
        Dim strFilter As String
        Dim dUnitPrice As Double
        
        If Me.cbxOperators = "lower than" Then
            strFilter = "UnitPrice < "
        ElseIf Me.cbxOperators = "lower than or equal to" Then
            strFilter = "UnitPrice <= "
        ElseIf Me.cbxOperators = "equal to" Then
            strFilter = "UnitPrice = "
        ElseIf Me.cbxOperators = "higher than or equal to" Then
            strFilter = "UnitPrice >= "
        ElseIf Me.cbxOperators = "higher than" Then
            strFilter = "UnitPrice > "
        ElseIf Me.cbxOperators = "different from" Then
            strFilter = "UnitPrice <> "
        Else
            MsgBox "You must select an operation to perform.", _
            vbOKOnly Or vbInformation, "Fun Department Store"
            Exit Sub
        End If
        
        If IsNull(txtUnitPrice) Then
            MsgBox "You must specify a unit price.", _
            vbOKOnly Or vbInformation, "Fun Department Store"
            Exit Sub
        End If
        
        Filter = strFilter & CDbl(txtUnitPrice)
        FilterOn = True
    
        Exit Sub
        
    cmdShowPrices_Click_Error:
        MsgBox "There was an error when trying to sort the records. " & _
               "Please report the error as follows." & 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
  3. In the Object combo box, select cmdClose
  4. Implement the event as follows:
    Private Sub cmdClose_Click()
    On Error GoTo cmdClose_Click_Err
    
        DoCmd.Close , ""
    
    cmdClose_Click_Exit:
        Exit Sub
    
    cmdClose_Click_Err:
        MsgBox "Error #: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description
               
        Resume cmdClose_Click_Exit
    
    End Sub
  5. Close Microsoft Visual Basic and return to Microsoft Access
  6. Switch the form to Form View
  7. In the Show Items Whose Price Is combo box, select higher than or equal to
  8. In the next text boxk, type 250.00
  9. Click the Show button
    Store Items Inventory
    
    
  10. Save and close 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
 
 
   
 

Previous Copyright © 2011-2013 FunctionX, Inc. Next