Data Entry Fundamentals
Introduction to Records
Each series of horizontal boxes is called a row or a record.
Visual Data Entry
In a database, a record is a series of values that each corresponds to a column of a table. To visually create a record, open a table in the Datasheet View. Click a cell and type or select the desired value. You can then press either Tab or Enter to move to the next field or cell. Probably a better alternative is to use a form. You must create such a form and equip it with the controls the user will use.
Practical Learning: Visually Creating Records
Introduction to Programmatic Data Entry
To assist your users in creating records for your database, you can create a situation that would open a form and move focus directly to a new record. To do this, you can call the GoToRecord() method of the DoCmd object. The syntax of this method is:
GoToRecord(ObjectType, object-name, Record, Offset)
The first argument of this method must be a constant value. In this case, it would be acDataForm. If you are calling it to act on the current form, set this argument as acActiveDataObject. In this case, you can omit this argument. The second argument is the name of the form to which the new record will be added. If the record is being added to the same form that is making the call, you can omit this argument. The third argument specifies the action that will be performed. This argument holds a constant value. In the case of adding a new record, the value of this argument would be acNewRec. The last argument has to do with other values of the third argument.
Here is an example that opens a form named Customers at a new record:
Private Sub cmdAddCustomer_Click() DoCmd.OpenForm "Customers" DoCmd.GoToRecord acDataForm, "Customers", acNewRec End Sub
Instead of writing this code, you can use the Command Button Wizard where you would select Record Operations followed by Add New Record.
Practical Learning: Introducing Programmatic Data Entry
The Value of a Field
A record is created by entering a value under a column in a table or in a Windows control of a form. To identify the value held by a column, the Field class is equipped with a property named Value.
Data Entry on a Date-Based Field
Microsoft Access provides two techniques to assist the user in specifying the value of a date-based field. To make data entry easy, if you had specified the data type of a field as Date/Time or Date & Time, in both the table and the form, the field or text box becomes equiped with a calendar. The user can click the calendar and select the desired date. If the field was created using the input mask, the user can click the text box and follow the rules of date values.
Before performing data entry on a table, you must know how the table is structured, the sequence of its columns, the type of data each column is made for. Also, you must make sure that the table exists. Otherwise, you would receive an error.
To enter data in a table, 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
Practical Learning: Introducing SQL Data Entry
Random Data Entry in SQL
The adjacent data entry requires that you know the position of each column. As an alternative, you can provide the values of fields in an 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. In the parentheses of VALUES, provides the value of each column in the order they appear in the previously mentioned list. 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
Date-Based Data Entry in SQL
In the SQL, to perform data entry on a date or time field, you should/must use an appropriate formula with the year represented by 2 or 4 digits. You should also include the date between # and #. Use any of the formulas we reviewed:
#mm-dd-yy# #mm/dd/yy# #mm/dd/yyyy# #mm-dd-yyyy# #yyyy/mm/dd# #yyyy-mm-dd#
Here are examples:
Private Sub cmdCreateRecord_Click() DoCmd.RunSQL "INSERT INTO Employees VALUES('Annette Schwartz', #22-10-09#, #11/22/09#)" MsgBox "A record has been added to the Employees table." DoCmd.RunSQL "INSERT INTO Employees VALUES('Mark Drowns', #2009-06-02#, #2009/06/28#)" MsgBox "A record has been added to the Employees table." DoCmd.RunSQL "INSERT INTO Employees " & _ "VALUES('Spencer Harland', '03-08-09', '03/28/09')" MsgBox "A record has been added to the Employees table." DoCmd.RunSQL "INSERT INTO Employees " & _ "VALUES('Kevin Sealans', '2009-07-20', '2009/08/18')" MsgBox "A record has been added to the Employees table." DoCmd.RunSQL "INSERT INTO Employees " & _ "VALUES('Anselme Bows', #09-13-2009#, '10/10/2009')" MsgBox "A record has been added to the Employees table." DoCmd.RunSQL "INSERT INTO Employees " & _ "VALUES('Jeremy Huissey', '07-20-2009', #08/18/2009#)" MsgBox "A record has been added to the Employees table." End Sub
Practical Learning: Performing Data Entry in SQL
Data Entry on a Time-Based Field
To perform data entry on a time-based field, the user can click the text box and follow the rules of the time values.
In the SQL, to perform data entry on a time-based field, include the date between # and #. Use any of the formulas we reviewed for time values:
#HH:MM# #HH:MM:SS# #HH:MM AM/PM# #HH:MM:SS AM/PM#
Other Techniques of Getting Records
You can copy records from one table to another. You can also copy values from a spreadsheet to a table in Microsoft Access.
Microsoft Access makes it exceptionnally easy to import records into a table. You can import a text file, a spreadsheet, an HTML file, an XML document, etc.
The Characteristics of a Form Related to its Controls
To let you get the list of records that the RecordSource property of a form or a report holds, their classes have a property named RecordsetClone.
The Record Selector is a vertical bar on the left side of a form. The presence or absence of the record selector is controlled by a property named Record Selectors. To let you programmatically control the presence or absence of the record selector on a form, the Form class is equipped with a Boolean property named RecordSelector. You can assign a value of True (to display it) or False (to hide it) to the RecordSelector property. Here is an example:
Private Sub cmdManipulate_Click() Me.RecordSelectors = False End Sub
Record navigation consists of moving from one record to another. To assist you with programmatically navigating among records, the DoCmd object is equipped with a method named GoToRecord. Its syntax is:
DoCmd.GoToRecord(ByVal Optional ObjectType As AcDataObjectType = AcDataObjectType.acActiveDataObject, _ ByVal Optional object-name As String = "", _ ByVal Optional Record As AcRecord = AcRecord.acNext, _ ByVal Optional Offset As Integer = 1)
This method takes four arguments and all of them are optional. The first argument is a member of the AcDataObjectType enumeration. If allows you to specify the type of object on which the action will be applied. The members of this enumeration and their integral values are:
If you omit the first argument and you call the DoCmd.GoToRecord() method from a form or report, the navigation action will be applied on the record that is currently displaying on that form or report.
The second argument is the name of the object selected in the first argument. For example, if you specify the first argument as a form (AcDataObjectType.acDataForm), you can pass the name of that form or report as the second argument.
The third argument specifies the actual action to perform. This argument is a member of an enumeration named AcRecord.
By default, a form is equipped with buttons that allow the user to navigate back and forth among records. The presence or absence of the navigation buttons is controlled by a Boolean property named Navigation Buttons. To let you programmatically control the presence or absence of the navigation buttons, the Form class is equipped with a property named NavigationButtons. Assign the desired Boolean value to it. Here is an example:
Private Sub cmdManipulate_Click() Me.NavigationButtons = False End Sub
If you are using the DoCmd.GoToRecord() method, you can pass the third argument that specifies the action to perform. That argument is of type AcRecord. The members of that enumeration are:
To assist you with creating your own navigation buttons, Microsoft Access provides the Button Wizard. Otherwise, you can write your own code. To do this, call the DoCmd.GoToRecord() method. Pass the third argument as one of the AcRecord members. Here is an example:
Private Sub cmdNextStudent_Click() On Error GoTo Err_cmdNextStudent_Click DoCmd.GoToRecord , , acNext Exit_cmdNextStudent_Click: Exit Sub Err_cmdNextStudent_Click: MsgBox Err.Description Resume Exit_cmdNextStudent_Click End Sub
This code is used to move to the next record.
Practical Learning: Navigating the Records
The Form's Scroll Bars
If a form hosts too many controls, Microsoft Access would equip it with one or two scroll bars. The presence of scroll bars is controlled by the Scroll Bars field in the Property Sheet or the ScrollBars field in the Properties window.
To let you programmatically control the presence or absence of scroll bars, the Form class is equipped with a property named ScrollBars. Its value can be one of the following four values:
Records-Related Events of a Form
Before Creating a New Record
To create a new record, a user can open a form to get ready to type something in a text box. As soon as the user types the first character, the form fires an event named Before Insert. The structure of this event is:
Private Sub Form_BeforeInsert(Cancel As Integer) End Sub
As soon as the user types something, this event presents a message box with an OK and an Cancel button. At this time, you can programmatically take an action. If you want to let the user continue to create the new record, either ignore (don't use) the argument or set it to False or 0. If you want to prevent the user from making creating a new record, set the argument to either True or any numeric value other than 0 (positive or negative).
After Creating a New Record
After providing the values of a new record, a user can move to another record. After this time, Microsoft Access automatically save the record. But just before the record is saved, the form fires an event named After Insert. The structure of this event is:
Private Sub Form_AfterInsert() End Sub
As you can see, this event doesn't carry much information, only to let you know that a new record was created and its has (already) been saved.
The Current Event of a Form
If you create a form that is used to present records to a user, you may also provide the ability to navigate among records. When doing this, whenever the form displays a record, it fires an event named Current. The structure of this event is:
Private Sub Form_Current() End Sub
Practical Learning: Accessing the Current Record
The Characteristics of Windows Controls
A Windows control is primarily a class. It is equipped with properties that describe it, methods that allow it to perform actions, and events that allow it to take actions at appropriate times and/or to communicate with the other controls on the database or in the operating system. While Windows controls are different, they share some characteristics, behaviors, and events.
While or after creating a control, you can either specify its location or change it. This is easily done when designing a form or report. The distance from the left border of the container to the left border of a control is represented by a property named Left. The distance from the top border of a host to the top border of a control is its Top property.
If you are programmatically creating a new control using the Application.CreateObject() method, the sixth and the seventh arguments specify the location of the control. The values of these arguments are integers.
To manually position or move a control, click it on the form or report. Hold your mouse down on the control and drag in the desired direction. If you want to specify the location by values, in the Property Sheet or the Properties window, set or change the values of the Left and the Top fields. To programmatically move a control horizontally, assign a numeric value to its Left property. Here is an example:
Private Sub cmdMove_Click() cmdSubmit.Left = 450 End Sub
To programmatically move a control vertically, assign a numeric value to its Top property.
A Control's Size
The size of a control or a section of a form or report is a combination of the distance from its left to its right borders and from its top to its bottom borders. These dimensions are represented by the Height and the Width properties respectively..
To visually set or change the size of a control, click it. Then hold the mouse on one of the handles around its border. Drag in the direction of your choice. To manually set or change the size by values, change the values of these properties in either the Property Sheet or the Properties window.
If you are programmatically creating a new control using the Application.CreateObject() method, the eighth and the ninth arguments hold the dimensions of the new control. If you omit these two arguments, their default values would be used
To programmatically change the height of a control, assign a numeric value to its Height property. In the same way, to heighten a control, assign the desired numeric value to its Width property. Here is an example:
Private Sub cmdResize_Click() cmdSubmit.Width = 2450 End Sub
The Visibility of a Control
Normally, a control is meant to be displayed to the user. At times, you will want to hide the control. If you create a form but specify that it should display in Datasheet View, its controls in the Form Header and the Form Footer sections are automatically hidden.
To let you control the visibility of a control, the classes of the Windows controls are equipped with a Boolean property named Visible.
To programmatically hide a control, access its Visible property and set it to False. Here is an example:
Private Sub cmdChangeProperty_Click() txtUserPassword.Visible = False End Sub
To display a hidden control, set its Visible property to True.
The Availability of a Control
Most controls are meant to let the user select, specify, or change their value. Such a control is said to be enabled. If you want the control to only display its value without the user being able to change it, access its Enabled Boolean property whose default value is True. If you set this property to No or False, the user cannot do anything on this control only to see its value. Here is an example:
Private Sub cmdChangeProperty_Click() txtEmployeeID.Enabled = False End Sub
To enable a control, set its Enabled property to True.
Practical Learning: Disabling a Control
A Locked Control
A control is said to be locked if the user cannot change the value of the control on the form. To let you lock a control, the classes of the control are equipped with a property named Locked.
To lock a control, after selecting it in the Design View, access its Boolean Locked property in the Property Sheet and set it to Yes (the default value is No).
Practical Learning: Locking a Control
Deleting a Control
When a control is on a form or report, you can delete it. You can also delete a group of controls in one step. To do this, click the control or select the group of controls and press Delete. You can also right-click the control or the group and press Delete.
To allow you to programmatically delete a control, the Application class is equipped with a method named DeleteControl. Its syntax is:
Sub Application.DeleteControl(ByVal FormName/ReportName As String, ByVal ControlName As String)
The first argument is the form or report on which the control is positioned. The second argument is the name of the control you want to delete.
The Focus of a Control
The focus is the visual aspect that indicates the control that would receive a change if the user types a new value. This also means that a control must receive focus before it can receive a value. A user usually gives focus to a control by clicking it or pressing Tab continuously until the control indicates that it has focus.
When the user clicks a control or attempts to give it focus using the keyboard or other means, just before it receives focus, the control fires an event named On Enter:
Private Sub Control_Enter() End Sub
When the control actually receives focus, it fires an event named On Got Focus:
Private Sub Control_GotFocus() End Sub
As you can see, these events don't carry much information, except to let you know that they are currently getting focus so you can take appropriate action.
To let you explicitly give focus to a control, the class of every visual control has a method name SetFocus. Call it to give focus to a control. Here is an example:
Private Sub Form_Load() NavigationButtons = False [Company Name].SetFocus End Sub
Alternatively, to support this operation, the DoCmd object is equipped with a method named GoToControl. Its syntax is:
DoCmd.GoToControl(ByVal ControlName As String)
This method takes the name of a control as argument. When this method is called, the database engine gives focus to the control passed as argument.
After using a control, the user can click another control or use the keyboard to move to another control. When this happens, just before it loses focus, the control fires an event named On Exit. It appears as follows:
Private Sub Control_Exit(Cancel As Integer) End Sub
This event carries one piece of information. The Cancel argument specifies whether the event should be kept (if set to False) or canceled (if set to True). When the control actually loses focus, it fires an event named On Lost Focus:
Private Sub Control_LostFocus() End Sub
Practical Learning: Losing Focus
The Text and Font of a Control
The Text Alignment of a Control
By default, text-based controls align their strings to the left. To support the allignment of text, controls are equipped with a property named TextAlign. Its value can be:
Practical Learning: Introducing Control's Text
The Font Name of a Control
A font is the design used to draw the letters and symbol of a text piece.
To support fonts, the class of every visual control is equipped with a text-based properties named FontName, which represents the name of the font you want to use. To specify the font of a control, assign the desired font to this property. Here is an example:
Private Sub cmdChangeProperty_Click() txtLastName.FontName = "Rockwell" End Sub
Practical Learning: Specifying the Record Source of a Form
The Font Size Applied to a Control
To control the size of characters of a control, their classes are equipped with a property named FontSize. After accessing this property, assign the desired integer to it. Here is an example:
Private Sub cmdChangeProperty_Click() txtLastName.FontName = "Rockwell" txtLastName.FontBold = True txtLastName.FontItalic = True txtLastName.FontUnderline = True txtLastName.FontSize = 12 End Sub
Practical Learning: Applying a Font Size
The Font Style Applied to a Control
The font style specifies whether the characters appear in bold, in italic, or underlined. The properties used to control these aspects are FontItalic and FontUnderline, respectively. Each of these properties is Boolean. If you set the property to True, its corresponding style applies. Here are examples:
Private Sub cmdChangeProperty_Click() txtLastName.FontName = "Rockwell" txtLastName.FontItalic = True txtLastName.FontUnderline = True End Sub
The Font Weight of a Control
To make the characters of a control appear thick, you can apply a weight to it. The primary property you can use is called FontBold. This is a Boolean property. If you assign the True value to it, the characters of the value of a control appear bold. Here is an example:
Private Sub cmdChangeProperty_Click() txtLastName.FontName = "Rockwell" txtLastName.FontBold = True End Sub
If you want, you can make the boldness appear with more style. To do this, access the FontWeight property of the control and assign one of the following values:
Practical Learning: Specifying the Font Weight of a Control
The Font Color of a Control
To enhance the appearance of characters on a control, you can use a color of your choice. To support this, the controls classes are equipped with a property named ForeColor. To specify the color for the characters, assign either a long constant or a color produced by a call to the RGB() function to this property. Here is an example:
Private Sub cmdChangeProperty_Click() ' Use a red color txtLastName.ForeColor = RGB(255, 0, 0) End Sub
Practical Learning: Using the Font Color of a Control
Aesthetic Characteristics of Database Objects
The Background Color of a Control
By default, the background of text-based controls appears white. If you want to change it, access the BackColor property of a control and assign either a constant integer or a call to the RGB() function to it. Here is an example:
Private Sub cmdChangeProperty_Click() txtLastName.BackColor = 795734 End Sub
Practical Learning: Using the Background Color of a Control
The Border Color of a Control
During design, Microsoft Access applies a default border to the controls you add. If you want, you can specify a specific color to a control or to the controls on a form or report. To support this, each control is equipped with a property named BorderColor. To control it, assign the desired color to this property. Here is an example:
Private Sub cmdChangeProperty_Click() txtLastName.BorderColor = 795734 End Sub
Practical Learning: Setting the Border Color of a Control
You can control the borders of a control to look a certain way. This is the job of the special effects:
To let you apply a special effect to a control, the classes of the controls are equipped with a property name SpecialEffect. To specify a special effect, assign one of the following values to this property:
Here is an example:
Private Sub cmdChangeProperty_Click() txtLastName.SpecialEffect = 3 End Sub
Practical Learning: Setting Special Effects on a Control
The Border Style
By default, the border of a control appears as a line. You can make it appear with a style. To support this, the classes of the controls are equipped with a property named BorderStyle. Its values are:
Here are examples of specifying the border styles:
Private Sub Form_Load() boxTransparent.BorderStyle = 0 boxSolid.BorderStyle = 1 boxDashes.BorderStyle = 2 boxShortDashes.BorderStyle = 3 boxDots.BorderStyle = 4 boxSparseDots.BorderStyle = 5 boxDashDot.BorderStyle = 6 boxDashDotDot.BorderStyle = 7 boxDoubleSolid.BorderStyle = 8 End Sub
This would produce:
Practical Learning: Ending the Lesson