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.

Practical LearningPractical Learning: Introducing Data Entry

  1. Start Microsoft Access
  2. Open the KoloBank1 database from the previous lesson
  3. In the Navigation Pane, double-click AccountsStatus and create records as follows:
     
    Account Status Notes
    Active The account is available for all types of operations
    Suspended Some operations are not allowed on the account, namely withdrawals
    Closed No operation is allowed on the account. The customer can neither deposit nor withdraw money
  4. Close the table
  5. In the Navigation Pane, double-click AccountsTypes and create records as follows:
     
    Account Type Notes
    Checking This is the most common type of account. The customer can deposit money any time. When funds are available, the customer can withdrawa any valid amount. At the end of every month, the customer is charged a $6.00 account fee
    Saving This account allows a customer to deposit money any time. Withdrawal is possible but the customer must bring back the balance in 14 business days. No monthly fee is charged on this account.
    CD Certificate of Deposit
  6. Close the table

Using Forms

Forms present a better way to perform data entry based on the graphical options they have. Microsoft Access allows you to create a form specially made for creating a new record. This is done by setting the Data Entry property to Yes.

Practical LearningPractical Learning: Introducing Forms Data Entry

  1. In the Navigation Pane, right-click the Locations form and click Copy
  2. Right-click any section of the Navigation Pane and click Paste
  3. Type NewLocation and click OK
  4. Right-click NewLocation and click Design View
  5. Using the Properties window, change the following characteristics:
    Auto Center: Yes
    Navigation Buttons: No
    Dividing Lines: Yes
    Data Entry: Yes
  6. Switch the form to Form View:
     
    Kolo Bank: New Location
  7. Save and close the form
  8. In the Navigation Pane, right-click the Employees form and click Copy
  9. Right-click any section of the Navigation Pane and click Paste
  10. Type NewEmployee and click OK
  11. Right-click NewEmployee and click Design View
  12. Using the Properties window, change the following characteristics:
    Caption: New Employee
    Auto Center: Yes
    Navigation Buttons: No
    Dividing Lines: Yes
    Data Entry: Yes
  13. Switch the form to Form View:
     
    Kolo Bank: New Employee
  14. Save and close the form
  15. In the Navigation Pane, double-click the NewLocation
  16. Create the locations records
  17. Close the form
  18. In the Navigation Pane, double-click the NewEmployee
  19. Create the employees records
  20. Close the form

Creating a Primary Key With a New 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.

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 a string or a natural 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

Adding a Primary Key to an Existing Table

Consider two tables named Departments and Employees and created using the following code:

Private Sub cmdCreateTables_Click()
    DoCmd.RunSQL "CREATE TABLE Departments" & _
                 "(" & _
                 "      DepartmentCode varchar(5)," & _
                 "      DepartmentName Text(50)," & _
                 ");"
    DoCmd.RunSQL "INSERT INTO Departments VALUES('HMNRS', 'Human Resources');"
    DoCmd.RunSQL "INSERT INTO Departments VALUES('ITSUP', 'Information Technology and Support');"
    DoCmd.RunSQL "INSERT INTO Departments VALUES('RSDEV', 'Research & Development');"
    
    DoCmd.RunSQL "CREATE TABLE Employees" & _
                 "(" & _
                 "      FirstName Text(24)," & _
                 "      LastName Text(24)," & _
                 "      DepartmentCode varchar(5)" & _
                 ");"
End Sub

Notice that the Departments table has a column that could be used as the primary key but is not specified as such. You may know already how to modify the structure of an existing table to add a new column. If you have a table that has a column that can be transformed to act as the primary key, create an ALTER TABLE expression and, to create a primary key, add an ADD CONSTRAINT clause that uses the same formula as if you were creating a primary key. The formulw would be:

ALTER TABLE TableName ADD CONSTRAINT Statement

Here is an example:

Private Sub cmdMakePrimaryKey_Click()
    DoCmd.RunSQL "ALTER TABLE Departments " & _
                 "ADD CONSTRAINT PK_Departments PRIMARY KEY(DepartmentCode);"
End Sub

In the code we used to create the tables, notice that the Employees table doesn't have a primary key. If you have such as table, to add a new column that would be the primary key, create an ALTER TABLE ... ADD COLUMN statement. To transform the same new column into a primary key, add a PRIMARY constraint. Here is an example:

Private Sub cmdCreatePrimaryKey_Click()
    DoCmd.RunSQL "ALTER TABLE Employees " & _
                 "ADD COLUMN EmployeeNumber varchar(10)," & _
                 "CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber);"
End Sub

Creating Foreign Key With a New Table

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

This technique allows you to ask the database interpreter to give a name to the foreign key you are creating. An alternative is to specify a name of your choosing. To do this, after the list of columns of the table, create a foreign key constraint with an explicit name. Here is an example:

Private Sub cmdCreateForeignKey_Click()
    DoCmd.RunSQL "CREATE TABLE CoursesLevels" & _
                 "(" & _
                 "    CourseLevel varchar(25), " & _
                 "    Comments note, " & _
                 "    CONSTRAINT PK_CoursesLevels PRIMARY KEY(CourseLevel) " & _
                         ");"

    DoCmd.RunSQL "CREATE TABLE Courses" & _
                 "( " & _
                 "    CourseCode varchar(10) not null, " & _
                 "    CourseName varchar(60), " & _
                 "    CourseLevel varchar(20), " & _
                 "    Notes note, " & _
                 "    CONSTRAINT FK_CourseLevels Foreign Key(CourseLevel)  " & _
                 "      REFERENCES CoursesLevels(CourseLevel), " & _
                 "    CONSTRAINT PK_Courses PRIMARY KEY(CourseCode) " & _
                 ");"
End Sub

Adding a Foreign Key to an Existing Table

Consider the following table named Courses:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE CoursesLevels" & _
                 "(" & _
                 "    CourseLevel varchar(25), " & _
                 "    Comments note, " & _
                 "    CONSTRAINT PK_CoursesLevels PRIMARY KEY(CourseLevel) " & _
                         ");"

    DoCmd.RunSQL "CREATE TABLE Courses" & _
                 "( " & _
                 "    CourseCode varchar(10) not null, " & _
                 "    CourseName varchar(60), " & _
                 "    Notes note, " & _
                 "    CONSTRAINT PK_Courses PRIMARY KEY(CourseCode) " & _
                 ");"
End Sub

This Courses table can benefit from a foreign key that represents the first table. If you have an existing table and you want to add a new column that would act as a foreign key,

Private Sub cmdCreateForeignKey_Click()
    DoCmd.RunSQL "ALTER TABLE Courses " & _
                 "ADD COLUMN CourseLevel varchar(25) " & _
                 "      REFERENCES CoursesLevels(CourseLevel);"
End Sub

As mentioned already, this type of code lets the database engine specify the name of the foreign key. If you want to create a name of your choice, explicitly create a constraint and give it a name. Here is an example:

Private Sub cmdCreateForeignKey_Click()
    DoCmd.RunSQL "ALTER TABLE Courses " & _
                 "ADD COLUMN CourseLevel varchar(25), " & _
                 "CONSTRAINT FK_CoursesLevels FOREIGN KEY(CourseLevel) " & _
                 "  REFERENCES CoursesLevels(CourseLevel);"
End Sub

Imagine you have a table that has a column that is in fact the foreign key of a parent table but the column was not specified as such.  To formally change such as a column into a foreign key, start an ALTER TABLE expression and add a foreign key that indicates the parent table and the corresponding column. Here is an example:

Private Sub cmdAddForeignKey_Click()
    DoCmd.RunSQL "ALTER TABLE Employees " & _
                 "ADD CONSTRAINT FK_Departments FOREIGN KEY(DepartmentCode) " & _
                 "  REFERENCES Departments(DepartmentCode);"
End Sub

Practical Learning: Creating Tables

  1. Besides the monthly charges applied on some checking accounts, customers get penalized some time to time. For example, if the balance of an account (checking or savings) becomes negative, the customer is charged an amount ($35) and the account gets suspended. There are some other reasons why an account would be charged a fee. To manage the charges, we will create a table for them.
    On the Ribboon, click CREATE
  2. In the Tables section, click Table Design
  3. Complete the definition of the table as follows:
     
    Field Name Data Type Caption Field Size
    ChargeReason Short Text Charge Reason 30
    Notes Long Text    
  4. Make the ChargeReason field the primary key
  5. Save the table as ChargesReasons and close it
  6. Using their bank account, customers can make deposits or withdraw money. Deposits or withdrawals are made with paper money, checks, transfers, and other means. To keep track of these, we will create a table.
    On the Ribbon, click CREATE and, in the Tables section, click Table Design
  7. Complete the definition of the table as follows:
     
    Field Name Data Type Caption Field Size
    CurrencyType Short Text Currency Type 30
    Notes Long Text    
  8. Make the CurrencyType field the primary key
  9. Save the table as CurrenciesTypes and close it
  10. As you may suspect, there are different types of transactions at a bank. The most common ones are deposits and withdrawals, but there are others. When a transactions occurs, we must identify its type. We will use a table to represent them.
    On the Ribbon, click CREATE and, in the Tables section, click Table Design
  11. Complete the definition of the table as follows:
     
    Field Name Data Type Caption Field Size
    TransactionType Short Text Transaction Type 40
    Notes Long Text    
  12. Make the TransactionType field the primary key
  13. Save the table as TransactionsTypes and close it
  14. Some time to time, either an employee or a customer will want to see a list of actions performed on a bank account. To keep track of those operations, we will create a table to hold a history of operations related to a bank account. The table will have the following columns:
    • Account Number: This column identifies the account on which the operation was performed
    • Account Status: This column specifies the status that was changed on the accout
    • Date Changed: This specifies the date when the action was performed
    • Short Note: This section allows an employee or the computer to give a short description of what happened
    • Detailed Notes: This column can be used to provide as much information as possible about the action
    On the Ribbon, click CREATE and, in the Tables section, click Table Design
  15. Complete the definition of the table as follows:
     
    Field Name Data Type Caption Field Size
    HistoryID AutoNumber History ID  
    AccountNumber Short Text Account # 20
    AccountStatus Short Text Account Status 30
    DateChanged Date/Time Date  
    ShortNote Short Text Short Note 80
    DetailedNotes Long Text Detailed Notes  
  16. Make the HistoryID field the primary key
  17. Save the table as AccountsHistories and close it
  18. An account is supposed to receive transactions, which are performed by customers, employees, or machines. Regular transactions include deposits, withdrawals, charges, etc. To keep track of all types of operations, we will create a table of transactions and it will have fields as:
    • Employee Number: This column identifies the employee who performed the transaction. The value of this column will come from the Employees table
    • Location Code: This column identifies the location where the operation took place. The value of this column will come from the Locations table
    • Transaction Date and Time: Two columns will be used to identify the day and time the transaction occurred
    • Account Number: This column specifies the account on which the transaction occurred. The value of this column will come from the Customers table
    • Transaction Type: This column specifies the type of transaction such as deposit, withdrawal, charge, or something else. The value of this column will come from the TransactionsTypes table
    • Currency Type: This column shows the physical way money was exchanged. The value of this column will come from the CurrenciesTypes table
    • Deposit Amount: If the transaction was a deposit, this column registers the amount that was deposited
    • Withdrawal Amount: If money was withdrawn, this column specifies how much was retrieved from the account
    • Charge Amount: If the account was charged money, this column shows the amount that was charged
    • Charge Reason: If a fee was charged, this column specifies the reason the charge occurred
    • Balance: After a transaction has occurred, we must make a note of the (new) balance. That's the purpose of this column
    • Of course, you can add any other piece of information you want
    On the Ribbon, click CREATE and, in the Tables section, click Table Design
  19. Complete the definition of the table as follows:
     
    Field Name Data Type Caption Field Size Format
    TransactionID AutoNumber Transaction ID 20  
    EmployeeNumber Short Text Employee # 20  
    LocationCode Short Text Location 10  
    TransactionDate Date/Time Date    
    TransactionTime Date/Time Time 40  
    AccountNumber Short Text Account Number 20  
    TransactionType Short Text Transaction Type 40  
    CurrencyType Short Text Currency Type 30  
    DepositAmount Number Deposit Double Fixed
    WithdrawalAmount Number Withdrawal Double Fixed
    ChargeAmount Number Charge Double Fixed
    ChargeReason Short Text Charge Reason 30  
    Balance Number   Double Fixed
    Notes Long Text      
  20. Make the TransactionID field the primary key
  21. Save the table as Transactions and close it
  22. On the Ribbon, click Database Tools and click Relationships
     
    Show Table
  23. In the Show Table dialog box, double-click each of the tables
  24. Click Close
  25. Configure the relationships as follows:
     
    Kolo Bank
    
    
  26. Save and close the relationship window
  27. In the Navigation Pane, double-click TransactionsTypes and create records as follows:
     
    Transaction Type Notes
    Deposit  
    Withdrawal  
    Service Charge  
    Local Fund Transfer Transfer made from another account of our bank
    External Fund Transfer Transfer made from an account from another bank
  28. Close the table
  29. In the Navigation Pane, double-click CurrenciesTypes and create records as follows:
     
    Currency Type
    Cash
    Check
    Direct Deposit
    Money Order
  30. Close the table

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. From the Resources that accompany these lessons, open the FunDS1 (FunDS stands for Fun Department Store) database
  2. In the Navigation Pane, double-click the StoreItemEditor form to open it in Form View
     
    Store Item Editor
  3. After viewing the form, right-click its title bar and click Design View
  4. On the form, right-click the top New... button and click Build Event...
  5. In the Choose Builder dialog box, click Code Builder and click OK
  6. 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
  7. In the Object combo box, select cmdNewCategory
  8. 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.

Practical LearningPractical Learning: Creating Sub-Forms

  1. The KoloBank1 database should still be opened.
    On the Ribboon, click CREATE
  2. To start a sub-form, in the Forms section, click Form Design
  3. Double-click the button at the intersection of the rulers
  4. In the Properties window, click All and set the Record Source to AccountsHistories
  5. Right-click the body of the form and click Form Header/Footer
  6. Click the button at the intersection of the rulers
  7. In the Properties window, set the Default View to Continuous Forms
  8. Save the form as sbfAccountHistory
  9. From the AccountsHistories table, add the AccountStatus, the DateChanged, and the ShortNote fields. Then design the sub-form as follows:
     
    Ceil Inn - Account History Sub-Form
     
  10. Save and close the sub-form
 
 
 

Topics on Data Entry

 

Introduction

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 Long Text. Like the Short Text data type, the Long Text 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)
    );
  2. To execute the statement, in the Results section of the Ribbon, click the Run botton Run
  3. Close the Query1 window
  4. When asked whether you want to save, click No

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.

Records Maintenance: Editing or Updating Records

 

Introduction

Both Microsoft Access and the SQL provide various ways of creating and maintaing record. Changing the value of a record is referred to as editing the record. To allow you to control whether the user can edit a record, the form is equipped with a Boolean property named AllowEdits. By default, this property is set to True. If you want to prevent the user from changing the values of a record, set this property to False.

Editing/Updating a Value With SQL

To edit a value using SQL, you start with the UPDATE keyword and follow this formula:

UPDATE TableName
SET ColumnName = NewValue

In our formula, the TableName must be the name of a table in the selected or current database. If you provide a name of a table that cannot be found on the database, you would receive a 3078 error:

If you provide a name of a table that cannot be found on the database, you would receive a 3078 error

The ColumnName must be a valid column in the TableName table. If you provide a column that is not found on the table, you may receive a 3073 error. The NewValue of our formula is the value that will replace the old one. If yo provide a value that is not compatible with the data type of the ColumnName, you would receive a message that states that "Microsoft Access didn't update the field due to a type conversion failure...". If you use this formula, all records of the ColumnName would be given the NewValue. Here is an example:

Private Sub cmdSetEmployeesSalaries_Click()
    DoCmd.RunSQL "UPDATE Employees SET HourlySalary = 20.00;"
    MsgBox "The salaries of all employees have been set to $20.00.", _
            vbOKOnly Or vbInformation, "Exercise"
End Sub

In some cases, you may want to change only one or more particular values. To do this, you must specify a condition that would be used to identify a record. The formula to use is:

UPDATE TableName
SET ColumnName = NewValue
WHERE Condition

The Condition specifies how the value will be located. Here is an example:

Private Sub cmdSetEmployeesSalaries_Click()
    DoCmd.RunSQL "UPDATE Employees " & _
                 "SET HourlySalary = 15.50 " & _
                 "WHERE Title = 'Accounts Associate';"
    MsgBox "The salaries of all employees have been set to $20.00.", _
            vbOKOnly Or vbInformation, "Exercise"
End Sub

Records Maintenance: Deleting Records

 

Introduction

Deleting a record consists of removing it from a table (or a form). To give you the ability to delete a record, the form is equipped with a Boolean property named AllowDeletions. By default, this property is set to True. If you want to prevent the user from deleting a record, set this property to False. Here is an example:

Private Sub cmdRecordDeletion_Click()
    AllowDeletions = False
End Sub

The user will still be able to perform any operation on a record as long this does not include removing the record.

Deleting Records in SQL

To programmatically delete a record using SQL, you combine the DELETE operator in the following primary formula:

DELETE FROM TableName

When this statement is executed, all records from the TableName table would be removed. Here is an example:

Private Sub cmdClearCustomers_Click()
    DoCmd.RunSQL "DELETE FROM Customers"
End Sub

In this case, all customer records from a table named Customers in the current database would be deleted. An alternative to the above formula is:

DELETE [ALL] *
FROM TableName

In this formula, you use either ALL * or * as the column placeholder. You can replace it with one or more names of columns but it doesn't matter because the DELETE operator signifies that the whole record will be deleted, regardless of the column name. The TableName must be a valid name of a table

Private Sub cmdDeleteRecords_Click()
    DoCmd.RunSQL "DELETE ALL * FROM Employees;"
    MsgBox "All employees have been dismissed because the company exists no more.", _
            vbOKOnly Or vbInformation, "Exercise"
End Sub

If you execute this type of statement, all records from the table would be deleted. You can specify what record to remove from a table. To do this, use the following formula of the DELETE operator:

DELETE *
FROM TableName
WHERE Condition

This time, the Condition factor allows you to set the condition that would be applied to locate the record. Here is an example of specifying a condition to delete a record:

Private Sub cmdDeleteRecords_Click()
    DoCmd.RunSQL "DELETE ALL * FROM Employees2 WHERE EmployeeNumber = '597740';"
    MsgBox "The employee has been thanked and sent home.", _
            vbOKOnly Or vbInformation, "Exercise"
End Sub
 
 
   
 

Previous Copyright © 2011-2013 FunctionX, Inc. Next