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 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
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) );
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 or the List Box button 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.
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.
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.
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.
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.
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:
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.
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
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"", " & _ " ""firstname.lastname@example.org"", ""(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"", ""email@example.com"", " & _ " ""Helene"");" End Sub
Notice that, during data entry, the columns are provided in an order different than that in which they were created.
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
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.
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:
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.
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.