Microsoft Access Database Development With VBA

Introduction to Data Entry
 

Data Relationships
 

Introduction

When performing data entry, a user's job should be as easy and smooth as possible. One of the aspects that take care of this is the flow of information from one list to another. In other words, data stored in one list can be made available to another list so the user not only doesn't have to enter the same piece of information in different lists but also the should be able to access, from one list or table, data stored in another table.

To allow the information to flow from one list to another, there should (must) exist a relationship between both tables. A relationship is made possible through a type of link from one table to another. This is the essence of relational databases.

Practical LearningPractical Learning: Introducing Data Relationships
  1. Start Microsoft Access
  2. From the Resources that accompany these lessons, open the FunDS1 (FunDS stands for Fun Department Store) database
  3. In the Navigation Pane, double-click the StoreItemEditor form to open it in Form View
     
    Store Item Editor
  4. After viewing the form, right-click its title bar and click Design View

Introduction to Primary Keys

A primary key is a field that keeps records distinct from one another. You can use one or more fields to act as the primary key of a table. If you are using one column for your table's primary key, the data type of that field can be almost any type, certainly a string or a number, as long as each record has a unique value. If you are using more than column, it is possible that two records can have the same value on one of the columns but the important rule to follow is that the combination of the values from fields involved must produce a unique value.

To programmatically apply a primary key to a column, when creating the table using SQL, one of the solutions is to add the PRIMARY KEY attribute to its list of flags. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Departments(Gender varchar(20) PRIMARY KEY);"
End Sub

As another technique, you can give a specific name to a primary key. To do this, you can first create the column. Then, somewhere before the closing parenthesis of the table, specify the primary key column using the following formula:

CONSTRAINT PrimaryKeyName PRIMARY KEY(ColumnName)

In this formula, the CONSTRAINT keyword and the PRIMARY KEY (case-insensitive) expression are required. In the PrimaryKeyName placeholder, enter the name you want to give to the primary key. In the parentheses of the PRIMARY KEY expression, enter the name of the column that will be used as the primary key. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Persons" & _
                 "(" & _
                 "    PersonalIdentification char(10)," & _
                 "    FirstName varchar(20)," & _
                 "    LastName varchar(20) NOT NULL," & _
                 "    CONSTRAINT PrimKeyPeople PRIMARY KEY(PersonalIdentification)" & _
                 ");"
End Sub

By convention or tradition, the name of the primary starts with PK_ followed by the name of the table. Here is an example:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Persons" & _
                 "(" & _
                 "    PersonalIdentification char(10)," & _
                 "    FirstName varchar(20)," & _
                 "    LastName varchar(20) NOT NULL," & _
                 "    CONSTRAINT PK_People PRIMARY KEY(PersonalIdentification)" & _
                 ");"
    MsgBox "A table named Persons has been created"
End Sub

Introduction to Foreign Keys

A foreign key is a column on a table whose data is coming from another table. Obviously, in order to have information flowing from one table to another, the table that holds the primary information must be created. The table that contains a primary key and that holds the information that another table would use is called the primary table or the parent table. The table that will receive the information from the other table is called the foreign table or the child table.

The formula to create a foreign key in the SQL is:

ColumnName DataType REFERENCES ParentTableName(ForeignKeyCcolumn)

The REFERENCES keyword is required. In the ParentTableName placeholder, enter the name of the primary table that holds the information that will be accessed in the current table. In the parentheses of ParentTableName, enter the name of the primary column of the parent table. Here is an example:

CREATE TABLE Departments(Gender varchar(20) PRIMARY KEY);

CREATE TABLE Persons
(
    PersonalIdentification char(10),
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    Gender varchar(20) REFERENCES Departments(Gender),
    CONSTRAINT PK_Persons PRIMARY KEY(PersonalIdentification)
);

Relationship-Based Controls
 

Introduction to Combo Boxes and List Boxes

A combo box is a Windows control made of two parts: a text portion and a list. A text box is used to display a selection made from a list of items. A list box displays a list of items, usually in one column, but a list box can also be configured to display more than one column.

There are various ways you can create a combo box or a list box in Microsoft Office Access 2010. The classic way is that, after displaying a form or report in Design View, in the Controls section of the Ribbon, click either the Combo Box button Combo Box or the List Box button List Box and click the form or report. When you do this, if the Control Wizards button is down, a wizard would start. If you want to create a list manually, you can click Cancel. Otherwise, you can continue with the wizard.

To programmatically create a combo box, call the CreateConotrol() function and pass the ControlType as acComboBox. The first argument is the name of the form or report on which the label will be positioned. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlGenders As Control
    
    Set ctlGenders = CreateControl("Exercise", _
                                   AcControlType.acComboBox)

    Set ctlGenders = Nothing
End Sub

To programmatically create a list box, call the CreateConotrol() function and pass the ControlType as acListBox. The first argument is the name of the form or report on which the label will be positioned. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlGenders As Control
    
    Set ctlGenders = CreateControl("Exercise", _
                                   AcControlType.acListBox)

    Set ctlGenders = Nothing
End Sub

The third argument is the section of the form or report where the control will be positioned. You can pass the fourth argument as the name of the form or report on which the label will be positioned. That is, the first and the fourth argument can be the same.

Properties of Combo and List Boxes

The primary reason for having a combo or a list box is to display a list of items. There are various ways you can specify this list.

When adding a combo or a list box to a form or report, if you use the wizard, it would assist you with creating and even configuring the list of items. If you don't use the wizard, you will need to create and configure the list yourself. Before creating the list of items of a combo or a list box, you must first specify how the list would be created. The property that allows you to specify the type of list is called RowSourceType. As it happens, the combo and the list boxes of Microsoft Access provide three ways to specify the origin of the list. Two options require a table (or a query, or a SQL statement).

To specify the list of items of a combo or a list box, each of their object (ComboBox or ListBox) is equipped with a property named RowSource.

If you want to create a list of strings to display in a combo box or list box, set the RowSourceType property to "Value List". This would be done as follows:

Private Sub Detail_Click()
    cbxGenders.RowSourceType = "Value List"
End Sub

After specifying this, to assist you with adding the items to the list of the control, the ComboBox and the ListBox classes are equipped with a collection property. This property mimics the behavior of the Collection class. For example, to add an item to the control, you can call its AddItem() method. Here are examples:

Private Sub Detail_Click()
    cbxGenders.RowSourceType = "Value List"
    
    cbxGenders.AddItem "Male"
    cbxGenders.AddItem "Female"
    cbxGenders.AddItem "Unknown"
End Sub

After creating the control, to locate an item in its list, you can use its indexed property.

Many of the combo boxes you will use in your forms or reports get their values from another table through a pre-established relationship. Such combo boxes have their RowSourceType set to Table/Query. To make data entry convenient, you can allow the user to add a value from the form or report where the combo box resides. Unfortunately, after adding the new value, the combo box is not automatically updated. You or the user must manually update the combo box. The user can change the form's view to design and switch it back to Form View. This is inconvenient and most users do not that this is possible. Fortunately, the ComboBox class is equipped with a method to update itself. The method is called Requery.

Updating 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: Updating a Combo Box
  1. On the form, right-click the top New... button and click Build Event...
  2. In the Choose Builder dialog box, click Code Builder and click OK
  3. Implement the event as follows:
    Private Sub cmdNewManufacturer_Click()
On Error GoTo cmdNewManufacturer_Error
    
    ' Display the Manufacturers form as a dialog box
    DoCmd.OpenForm "Manufacturers", , , , acFormAdd, AcWindowMode.acDialog
    
    ' After using the Manufacturers form, when the user closes it,
    ' refresh the Manufacturer combo box
    ManufacturerID.Requery
    
cmdNewManufacturer_Exit:
    Exit Sub
    
cmdNewManufacturer_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 cmdNewManufacturer_Exit
End Sub
  4. In the Object combo box, select cmdNewCategory
  5. Implement the event as follows: 
    Private Sub cmdNewCategory_Click()
On Error GoTo cmdNewCategory_Error
    
    DoCmd.OpenForm "Categories", , , , acFormAdd, AcWindowMode.acDialog
    
    CategoryID.Requery
    
cmdNewCategory_Exit:
    Exit Sub
    
cmdNewCategory_Error:
    MsgBox "An error occured when trying to update the list." & vbCrLf & _
           "=- Please report the error as follows -=" & vbCrLf & _
           "Error #: " & Err.Number & vbCrLf & _
           "Error Message: " & Err.Description
           Resume cmdNewCategory_Exit
End Sub

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.

Practical LearningPractical Learning: Refreshing a Combo Box
  1. In the Object combo box, select ManufacturerID
  2. In the Procedure combo box, select NotInList
  3. Implement the event as follows:
    Private Sub ManufacturerID_NotInList(NewData As String, Response As Integer)
On Error GoTo ManufacturerIDNotInList_Error
    Dim NewManufacturerID As Long
    
    If IsNull(ManufacturerID) Then
        ' Set the value of the combo box empty
        ManufacturerID = ""
    Else
        ' If the foreign key currently has a value,
        ' assign that value to the declared value
        NewManufacturerID = ManufacturerID
        ' Set the foreign key to null
        ManufacturerID = Null
    End If
    
    ' The combo box is ready to receive a new value.
    ' To make it happen, display the Manufacturers form
    ' as a dialog box so the user would not use
    ' the Store Items form while the Manufacturers form is opened
    ' When opening the Manufacturers form, create a new record
    ' and display the new manufacturer in it
    If MsgBox("The '" & NewData & "' manufacturer does not exist in the database. " & _
              "Do you want to add it?", _
              vbYesNo, "Fun Department Store - FunDS") = vbYes Then
        DoCmd.OpenForm "Manufacturers", , , , acFormAdd, AcWindowMode.acDialog, NewData
    
        ' After using the Manufacturers dialog box, let the user close it.
        ' When the user closes the Manufacturers form, refresh the ManufacturerID combo box
        ManufacturerID.Requery
    
        ' If the user had created a new manufacturer,
        ' assign its ManufacturerID to the variable we had declared
        If ManufacturerID <> 0 Then
            ManufacturerID = NewManufacturerID
        End If
        
        ' Assuming that the manufacturer was created, ignore the error
        Response = acDataErrAdded
    Else
        ' If the manufacturer was not created, indicate an error
        Response = acDataErrContinue
    End If
    
ManufacturerIDNotInList_Exit:
    Exit Sub
    
ManufacturerIDNotInList_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 ManufacturerIDNotInList_Exit
End Sub
  4. In the Object combo box, select CategoryID
  5. In the Procedure combo box, select NotInList
  6. Implement the event as follows: 
    Private Sub CategoryID_NotInList(NewData As String, Response As Integer)
On Error GoTo CategoryIDNotInList_Error
    
    Dim NewCategoryID As Long
    
    If IsNull(CategoryID) Then
        CategoryID = ""
    Else
        NewCategoryID = CategoryID
        CategoryID = Null
    End If
    
    If MsgBox(NewData & " is not a valid category of this database. " & _
              "Do you want to add it?", _
              vbYesNo, "Fun Department Store - FunDS") = vbYes Then
        DoCmd.OpenForm "Categories", , , , acFormAdd, AcWindowMode.acDialog, NewData
    
        CategoryID.Requery
    
        If CategoryID <> 0 Then
            CategoryID = NewCategoryID
        End If
        
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
    
CategoryIDNotInList_Exit:
    Exit Sub
    
CategoryIDNotInList_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 CategoryIDNotInList_Exit
End Sub
  7. Return to Microsoft Access
  8. In the Navigation Pane, right-click the Manufacturers form and click Design View
  9. In the Properties window, click Event and double-click On Load
  10. Click its ellipsis button to switch to Microsoft Visual Basic
  11. Implement the event as follows: 
    Private Sub Form_Load()
    ' When this form opens, find out if it received an external 
    ' value from another object (such as the StoreItemEditor form).
    If Not IsNull(Me.OpenArgs) Then
    	' If it did, put that value in the Manufacturer text box
        Me.Manufacturer = Me.OpenArgs
        ' Since our database allows up to three different names for 
        ' a manufacturer, the user will optionnally fill the other two text boxes
    End If
End Sub
  12. Return to Microsoft Visual Access
  13. In the Navigation Pane, right-click the Categories form and click Design View
  14. In the Event section of the Properties window, double-click On Load
  15. Click its ellipsis button and implement the event as follows: 
    Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
        Me.Category = Me.OpenArgs
    End If
End Sub
  16. Return to Microsoft Access
  17. Close the forms
  18. When asked whether you want to save it, click Yes

Sub-Forms and Sub-Reports

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.
 
 
 

Introduction to Data Entry
 

Overview

Before performing data entry on a table, you must know how the table is structured, the sequence of its columns, the type of data that each column is made of: it is certainly undesirable to have a numeric value as somebody's first name.

Before performing data entry, you must make sure that the table exists. Otherwise, you would receive a 3192 error:

Error 3192

To enter data in a table, you start with the INSERT combined with the VALUES keywords. The statement uses the following syntax:

INSERT TableName VALUES(Column1, Column2, Column_n)

Alternatively, or to be more precise, you can specify that you are entering data in the table using the INTO keyword between the INSERT keyword and the TableName factor. This is done with the following syntax:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

TableName must be a valid name of an existing table in the current database. If the name is wrong, the SQL interpreter would simply consider that the table you are referring to doesn't exist. Consequently, you would receive an error.

The VALUES keyword announces the values for the columns. The values of the columns must be included in parentheses.

If the data type of a column is a string type, include its value between double-quotes if you are using the DoCmd.RunSQL() method of Microsoft Access or you should include it in single-quotes.

Adjacent Data entry in SQL

The most common technique of performing data entry requires that you know the sequence of columns of the table in which you want to enter data. With this sequence in mind, enter the value of each field in its correct position.

During data entry on adjacent fields, if you don't have a value for a string field, type two double-quotes to specify an empty field. Imagine you have a table equipped with two string columns. Here is an example that creates a record made of two strings:

Private Sub cmdEnterData_Click()
    DoCmd.RunSQL "INSERT INTO Employees VALUES(""Jimmy"", ""Collen"");"
End Sub

Random Data Entry in SQL

The adjacent data entry requires that you know the position of each column. The SQL provides an alternative that allows you to perform data entry using the name of a column instead of its position. This allows you to provide the values of fields in any order of your choice.

To perform data entry at random, you must provide a list of the columns of the table in the order of your choice. You can either use all columns or provide a list of the same columns but in your own order. Here is an example:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE Table Employees (" & _
                 "FirstName Text, " & _
                 "LastName Text, " & _
                 "EmailAddress Varchar, " & _
                 "HomePhone Char);"
End Sub

Private Sub cmdCreateNewRecord_Click()
    DoCmd.RunSQL "INSERT INTO Employees (" & _
                 "FirstName, LastName, EmailAddress, HomePhone) " & _
                 "VALUES(""Gertrude"", ""Monay"", " & _
                 "       ""gmonay@ynb.com"", ""(104) 972-0416"");"
End Sub

You don't have to provide data for all columns, just those you want, in the order you want. To do this, enter the names of the desired columns on the right side of the name of the table, in parentheses. The syntax used would be:

INSERT TableName(ColumnName1, Columnname2, ColumnName_n)
VALUES(ValueFormColumnName1, ValueFormColumnName2, ValueFormColumnName_n);

Here is an example:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE Table Employees (" & _
                 "FirstName Text, " & _
                 "LastName Text, " & _
                 "EmailAddress Varchar, " & _
                 "HomePhone Char);"
End Sub

Private Sub cmdCreateNewRecord_Click()
    DoCmd.RunSQL "INSERT INTO Employees (" & _
                 "LastName, EmailAddress, FirstName) " & _
                 "VALUES(""Mukoko"", ""hmukoko@ynb.com"", " & _
                 "       ""Helene"");"
End Sub

Notice that, during data entry, the columns are provided in an order different than that in which they were created.

Assistance With Data Entry
 

The Size of a Text-Based Field

When creating a text-based field, you have the option of controlling the number of characters or symbols that the field can hold.

We saw that, in SQL, a text-based field can use the TEXT, CHAR, or VARCHAR data type. By default, when a column of a table has been set to TEXT, CHAR, or VARCHAR, it is made to hold 255 characters. To specify the number of characters of the string-based column, add an opening and a closing parentheses to the TEXT, the CHAR, or the VARCHAR data types. In the parentheses, enter the desired number. Here are examples:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE Employees (" & _
             	 "EmplNumber TEXT(6)," & _
             	 "FirstName Text(20)," & _
             	 "LastName Text(20)," & _
             	 "Address varchar(100)," & _
             	 "City VARCHAR(40)," & _
             	 "State char(2));"
End Sub

Required or Null Fields

When performing data entry, you can expect the user to skip any column whose value is not available and move to the next. In some cases, you may want to require that the value of a column be specified before the user can move on. Such a field is referred to as required. If you are creating the table in the Design View, to require that the user enter a value for a particular column, in the lower section of the window, use the Required Boolean property.

If you are programmatically creating the column using SQL, if you want to let the user add or not add a value for the column, type the NULL keyword on the right side of the data type. If you want to require a value for the column, type NOT NULL. Here are examples:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Contractors(" & _
                 "FirstName TEXT NULL, " & _
                 "LastName VARCHAR NOT NULL);"
End Sub

In this case, when performing data entry, the user must always provide a value for the LastName column in order to create a record. If you omit to specify the nullity of a field, it is assumed NULL.

Practical LearningPractical Learning: Introducing NULL and NOT NULL Fields
  1. To create a new database, on the Ribbon, click File and click New
  2. Change the File Name to WattsALoan1
  3. Click Create
  4. Close the default table without saving it
  5. On the Ribbon, click File -> Options
  6. Click Current Database
  7. Click Overlapping Windows
  8. Click Compact on Close
  9. Click OK
  10. On the message box, click OK
  11. On the Ribbon, click Create
  12. In the Queries section, click Query Design
  13. On the Show Table dialog box, click Close
  14. Right-click the middle of the window and click SQL View
  15. Delete the default text in the window
  16. Type the following: 
    CREATE TABLE Employees
(
    EmployeeNumber text(10) not null,
    FirstName text(24) null,
    LastName text(24) not null,
    FullName text(50),
    Title text(50) null,
    WorkPhone text(20) null,
    CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber)
);

The Uniqueness of Records

One of the primary concerns of records is their uniqueness. In a professional database, you usually want to make sure that each record on a table can be uniquely identified. There are various ways you can create a unique value, including applying the primary key to a field.

To let you create a column whose values are unique, the SQL provides the UNIQUE keyword. To apply it on a column, after the data type, type UNIQUE. Here is an example:

CREATE TABLE Students
(
    StudentNumber int UNIQUE,
    FirstName varchar(24),
    LastName varchar(24) NOT NULL
);

When a column has been marked as unique, during data entry, the user must provide a unique value for each new record created. If an existing value is assigned to the column, this would produce an error:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE Students" & _
                 "(" & _
                 "  StudentNumber int UNIQUE," & _
                 "  FirstName varchar(24)," & _
                 "  LastName varchar(24)" & _
                 ");"

    DoCmd.RunSQL "INSERT INTO Students VALUES(24880, 'John',  'Scheels');"
    DoCmd.RunSQL "INSERT INTO Students VALUES(92846, 'Rénée', 'Almonds');"
    DoCmd.RunSQL "INSERT INTO Students VALUES(47196, 'Peter', 'Sansen');"
    DoCmd.RunSQL "INSERT INTO Students VALUES(92846, 'Daly',  'Camara');"
    DoCmd.RunSQL "INSERT INTO Students VALUES(36904, 'Peter', 'Sansen');"
End Sub

When the fourth record is entered, since it uses a student number that exists already, the database engine would produce an error:

Unique Values

Large Text-Based Columns

The text data types we have above can hold a maximum of 255 characters. Of course, sometimes you will want to have a column that can hold longer text. If you want to create a field that uses longer text, in Microsoft Access, display the table in Design View and set its Data Type to Memo. Like the Text data type, the Memo type is used for any type of text, any combination of characters, and symbols, up to 64000 characters.

If you are using SQL to create your table, you can apply the MEMO, the NOTE, or the LONGTEXT data types to a column that would hold long text.

Practical LearningPractical Learning: Introducing Large Text-Based Columns
  1. Change the code in the Query1 window as follows: 
    CREATE TABLE Employees
(
    EmployeeNumber text(10) not null,
    FirstName text(24) null,
    LastName text(24) not null,
    FullName text(50),
    Title text(50) null,
    WorkPhone text(20) null,
    Address text(50),
    City text(40),
    State text(40),
    ZIPCode text(20),
    Country text(40),
    Username text(20) null,
    Password text(24) null,
    HomePhone text(20) null,
    Notes LONGTEXT,
    CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber)
);

Binary and Image Columns

The binary data type can let a column accept any type of data but it is equipped to interpret the value. For example, it can be used to receive hexadecimal numbers. To specify this when programmatically creating a column using SQL, set its data type to one of the following: IMAGE, OLEOBJECT, LONGBINARY, or GENERAL.
 
 
   
 
Previous Copyright © 2011 FunctionX, Inc. Next