Introduction to the Records of a Database

Data Entry Fundamentals

Introduction to Records

After creating a table and its column(s), you can populate it with values. The series of values is also called data. Here is an example of a table with values (data):

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

1. Start Microsoft Access
2. On the list of files, click Brokerage Company1 from the previous lesson
3. In the Navigation Pane, double-click the Agents form
4. Click the first text box and type 924-50-TG
5. Press Tab and type Joshua for FirstName
6. Press Enter and type Rohrgood for LastName
7. Click Title and type Transactions Manager
8. Click PayFrequency and type Biweekly
9. Close the Agents form
10. In the Navigation Pane, double-click the Agents table to open it in the Datasheet View
11. Click the first empty cell under AgentCode and type 370-84-DP
12. Press Tab and type Daniella for FirstName
13. Press Enter and type Reasons for LastName
14. Click the empty cell under Title, type Transactions Associate and press Tab
15. Type Monthly for PayFrequency
16. Close the Agents table
17. In the Navigation Pane, double-click the Companies table to open it in the Datasheet View
18. Click the first empty cell below CompanyCode and type CDANL
19. Click the first empty cell below CompanyName and type CompuData Analytics
20. Press Tab and type MTDNS for CompanyCode
21. Press Tab and type MattGenie Dennison for CompanyName
22. Press Enter and type MCUW for CompanyCode
23. Press Enter and type Mechano Ultra Worldwide for CompanyName
24. Click the cell below MCUW and type CHANC
25. Click the cell below Mechano Ultra Worldwide and type Chancey Burtons International
26. Close the Companies table
27. In the Navigation Pane, right-click the Transactions form and click Open
28. Enter the values as follows:
    TransactionDate: 9/25/2017
    AgentCode: 924-50-TG
    CompanyCode: MCUW
    NumberOfShares: 3822
    PricePerShare: 62.50
    PaymentStatus: Paid
29. Close the Transactions form
30. In the Navigation Pane, right-click the Transactions table and click Open
31. Use the empty cells and create values as follows:
    TransactionDate: 9/25/2017
    AgentCode: 370-84-DP
    CompanyCode: CDANL
    NumberOfShares: 14204
    PricePerShare: 24.85
    PaymentStatus: Paid
32. Close the Transactions table

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

1. In the Navigation Pane, right-click the Transactions form and click Design View
2. In the Controls section of the Ribbon, click the Button
3. Click somewhere in the left side of the Form Footer section (no need for precision).
   If a wizard starts, click Cancel
4. Click somewhere else on the form (no need for precision)
5. Double-click the button you just added
6. In the Property Sheet, change the following characteristics:
   Name: cmdNewTransaction
   Caption: New Transaction
7. Right-click the button and click Build Event...
8. Click Code Builder and click OK
9. Implement the event as follows:

   Private Sub mdNewTransaction_Click()
       DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
   End Sub

10. Return to Microsoft Access and switch the form to Form View
11. Click the newly added button
12. Enter the values as follows:
    TransactionDate: 10/04/2017
    AgentCode: 924-50-TG
    CompanyCode: MCUW
    NumberOfShares: 6318
    PricePerShare: 55.18
    PaymentStatus: On Hold
13. Close the Transactions form
14. When asked whether you want to save, click Yes

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.

Data Entry in SQL

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

1. On the Ribbon, click Create and click Query Design
2. On the Show table dialod box, click Close
3. Right-click the body of the window and click SQL View
4. Type the follow code:

   INSERT INTO Agents
   VALUES('294-85-FW', 'Frank', 'Feuer', 'Transactions Associate', 'Biweekly');

5. To create the record, on the Ribbon, click Design if necessary and click the Run button
6. Click Yes on the message box
7. Change the code as follows:

   INSERT INTO Agents
   VALUES('183-74-BB', 'Eduardo', 'Guardia', 'Sales Planning Manager', 'Monthly');

8. To execute, on the Ribbon, click the Run button
9. Click Yes on the message box

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"", " & _
                 "       ""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

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

1. Change the code as follows:

   INSERT INTO Agents(PayFrequency, LastName, FirstName, Title, AgentCode)
   VALUES('Biweekly', 'Barber', 'Paula', 'Transactions Associate', '628-74-KD');

2. To execute, on the Ribbon, click the Run button
3. Click Yes on the message box
4. Close the Query window
5. When asked whether you want to save, click No
6. In the Navigation Pane, right-click New Transaction and click Design View
7. On the form, right-click the Submit button and click Build Event...
8. In the Choose Builder dialog box, click Code Builder and click OK
9. Implement the event as follows:

   Private Sub cmdSubmit_Click()
       If IsNull(txtTransactionDate) Then
           MsgBox "You must specify the date the transaction took place or started.", _
                  vbOKOnly Or vbInformation, "Brokerage Company - New Transaction"
           Exit Sub
       End If
       
       If IsNull(txtAgentCode) Then
           MsgBox "Please provide the agent code of the employee who is creating, created, or completed the transaction(s).", _
                  vbOKOnly Or vbInformation, "Brokerage Company - New Transaction"
           Exit Sub
       End If
       
       If IsNull(txtCompanyCode) Then
           MsgBox "Please provide the code of the company whose stocks were processed.", _
                  vbOKOnly Or vbInformation, "Brokerage Company - New Transaction"
           Exit Sub
       End If
       
       DoCmd.RunSQL "INSERT INTO Transactions(TransactionDate, AgentCode, CompanyCode, NumberOfShares, PricePerShare, PaymentStatus) " & _
                    "VALUES(#" & CDate(txtTransactionDate) & "#, '" & txtAgentCode & "', '" & txtCompanyCode & "', " & CInt(Nz(txtNumberOfShares)) & ", " & CDbl(Nz(txtPricePerShare)) & ", '" & txtPaymentStatus & "');"
       
       DoCmd.Close
   End Sub

10. Return to Microsoft Access and close the form
11. When asked whether you want to save, click Yes
12. In the Navigation Pane, right-click New Transaction and click Open
13. Click the text boxes and enter the following values. Click Submit each time. Click OK after each record and click Yes on the message box:
    
    

    TransactionDate	AgentCode	CompanyCode	NumberOfShares	PricePerShare	PaymentStatus
    10/04/2017	370-84-DP	CDANL	4002	28.35	Processing
    10/04/2017	628-74-KD	MTDNS	10527	35.37	On Hold
    10/06/2017	294-85-FW	MCUW	7901	58.26	Paid
    10/06/2017	628-74-KD	MTDNS	4206	32.48	Processing

14. Close the form

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

Copying Records

You can copy records from one table to another. You can also copy values from a spreadsheet to a table in Microsoft Access.

Importing Records

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

The Clone of a Form's Recordset

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.

Record Selectors

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

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.

Navigation Buttons

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:

AcRecord Member	Value	Same as Clicking	Description
acFirst	2		Navigates to the first record
acPrevious	0		Navigates to the previous record
acNext	1		Navigates to the Next record
acLast	3		Navigates to the last record
acGoTo	4		Navigates to a record. In this case, pass a number as the fourth argument
acNewRec	5		Opens a new empty record

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

1. In the Navigation Pane, right-click the Transactions form and click Design View
2. Double-click the Properties button of the form
3. In the All tab of the Property Sheet, click Navigation Buttons, then click the arrow of its combo box and select No
4. In the Controls section of the Ribbon, click the Button and click the Form Footer bar.
   If a wizard starts, click Cancel
5. In the Property Sheet, change the characteristics of the button as follows:
   Name: cmdFirstRecord
   Caption: | <
6. Right-click the new button and click Copy
7. Right-click the Form Footer section and click Paste
8. In the Property Sheet, change the characteristics of the new button as follows:
   Name: cmdPreviousRecord
   Caption: < <
9. Right-click one of the buttons and click Copy
10. Right-click the Form Footer section and click Paste
11. In the Property Sheet, change the characteristics of the new button as follows:
    Name: cmdNexRecord
    Caption: > >
12. Right-click one of the buttons and click Copy
13. Right-click the Form Footer section and click Paste
14. In the Property Sheet, change the characteristics of the new button as follows:
    Name: cmdLastRecord
    Caption: > |
15. Right-click the | < button and click Build Event...
16. Click Code Builder and click OK
17. Implement the event as follows:
    

    Private Sub cmdFirstRecord_Click()
        DoCmd.GoToRecord , , AcRecord.acFirst
    End Sub

18. In the Object combo box, select cmdPreviousRecord
19. Implement the event as follows:
    

    Private Sub cmdPreviousRecord_Click()
        DoCmd.GoToRecord , , AcRecord.acPrevious
    End Sub

20. In the Object combo box, select cmdNextRecord
21. Implement the event as follows:
    

    Private Sub cmdNextRecord_Click()
        DoCmd.GoToRecord , , AcRecord.acNext
    End Sub

22. In the Object combo box, select cmdLastRecord
23. Implement the event as follows:
    

    Private Sub cmdLastRecord_Click()
        DoCmd.GoToRecord , , AcRecord.acLast
    End Sub

24. Return to Microsoft Access
25. Switch the form to Form View
26. Click the buttons you had added
27. Save the form
28. Right-click the title bar of the form and click Design View

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

1. The Transactions form should currently be opened in Design View.
   In the Controls section of the Ribbon, click the Text box and click the Detail section of the form (no need for precision; you will continue with the design later)
2. While the text box is selected, in the Property Sheet, change its Name to txtPrincipal
3. Change the caption its accompanying label to Principal:
4. In the Controls section of the Ribbon, click the Text box and click the Detail section of the form (no need for precision)
5. While the new text box is selected, in the Property Sheet, change its Name to txtCommission
6. Change the caption its accompanying label to Commission:
7. In the Controls section of the Ribbon, click the Text box and click the Detail section of the form (no need for precision)
8. While the new text box is selected, in the Property Sheet, change its Name to txtTotalInvestment
9. Change the caption its accompanying label to Total Investment:
10. Click the Properties button of the form
11. In the Property Sheet, double-click On Current
12. Click its ellipsis button and implement the event as follows:

    Private Sub Form_Current()
    On Error GoTo Form_Current_Error
        Dim principal As Double, commission As Double
        Dim shares As Integer, price As Double
    
        shares = CInt(Nz(NumberOfShares))
        price = CDbl(Nz(PricePerShare))
    
        principal = shares * price
    
        If principal = 0# Then
            commission = 0#
        End If
    
        If (principal > 0#) And (principal <= 2500#) Then
            commission = 26.25 + (principal * 0.0014)
        End If
            
        If (principal > 2500#) And (principal <= 6000#) Then
            commission = 45# + (principal * 0.0054)
        End If
            
        If (principal > 6000#) And (principal <= 20000#) Then
            commission = 60# + (principal * 0.0028)
        End If
            
        If (principal > 20000#) And (principal <= 50000#) Then
            commission = 75# + (principal * 0.001875)
        End If
            
        If (principal > 50000#) And (principal <= 500000#) Then
            commission = 131.25 + (principal * 0.0009)
        End If
            
        If (principal > 500000#) Then
            commission = 206.25 + (principal * 0.000075)
        End If
    
        txtPrincipal = FormatNumber(principal)
        txtCommission = FormatNumber(commission)
        txtTotalInvestment = FormatNumber(principal + commission)
        
        Exit Sub
        
    Form_Current_Error:
        MsgBox "There was a problem when processing this transaction.", _
               vbOKOnly Or vbInformation, "Brokerage Company"
        Resume Next
    End Sub

13. Return to Microsoft Access and switch the form to Form View
14. Click the next record button
15. Click the next record button again
16. Switch the form back to Design View

The Characteristics of Windows Controls

Introduction

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.

Control Location

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

1. On the Transactions form, click the TransactionID text box
2. In the Property Sheet, click the Data tab
3. Double-click Enabled to set its value to No

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

1. In the Property Sheet, double-click Locked to set its value to Yes
2. Save the form

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

Getting Focus

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.

Setting Focus

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.

Losing Focus

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

1. In the Navigation Pane, right-click Employment Application and click Design View
2. On the form, click the top text box
3. In the Property Sheet, click the Event tab and double-click On Lost Focus
4. Click its ellipsis button and implement the event as follows:

   Private Sub txtFirstName_LostFocus()
       Dim firstName As String
       Dim lastName As String
       Dim fullName As String
       
       firstName = Nz(txtFirstName)
       lastName = Nz(txtLastName)
       
       If Len(firstName) = 0 Then
           fullName = lastName
       Else
           fullName = firstName & " " & lastName
       End If
       
       txtFullName = fullName
   End Sub

5. In the Object combo box, select txtLastName
6. In the Procedure combo box, select LostFocus
7. Implement its event as follows:

   Private Sub txtLastName_LostFocus()
       Dim firstName As String
       Dim lastName As String
       Dim fullName As String
       
       firstName = Nz(txtFirstName)
       lastName = Nz(txtLastName)
       
       If Len(firstName) = 0 Then
           fullName = lastName
       Else
           fullName = firstName & " " & lastName
       End If
       
       txtFullName = fullName
   End Sub

8. Return to Microsoft Access and switch the form to Form View
9. Click the top text box, type Horace and press Tab. Notice that only the first name displays in the Full Name text box

   

10. In the other text box, type Critenden and Press Enter

    

11. Close the form
12. When asked whether you want to save it, click Yes

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

1. In the Navigation Pane, right-click the Transactions form and click Design View
2. On the form, click the TransactionDate text box
3. On the Ribbon, click Format and click the Align Text Left button
4. To preview, switch the form to Form View
5. Right-click the form's title bar and click Design View

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

1. In the combo box on top of the Property Sheet, select lblMainTitle
2. On the Ribbon, click Format if necessary.
   In the Font section, click the arrow of the Font combo box and select Bodony MT Black (if you don't have that font, select Times New Roman)
3. In the combo box on top of the Property Sheet, select lblAgentTitle
4. On the Ribbon, click Home
5. In the Text Formatting section, click the arrow of the Font combo box and select Georgia (if you don't have that font, select Times New Roman)

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

1. While the lblAgentTitle label is still selected and the Home tab of the Ribbon is displaying, in the Text Formatting section, click the arrow of the Font Size combo box and select 24
2. Double-click one of the small orange squares around the label to resize it
3. In the combo box on top of the Property Sheet, select lblMainTitle
4. On the Ribbon, click Format
5. In the Font section, click the arrow of the Font Size combo box and select 20
6. Double-click one of the small orange squares around the label to resize it

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

1. While the lblMainTitle label is still selected and the Format tab of the Ribbon is displaying, in the Font section, click the Bold button
2. In the combo box on top of the Property Sheet, select lblAgentTitle
3. On the Ribbon, click Home
4. In the Text Formatting section, click the Bold button

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

1. On the form, click main title
2. On the Ribbon, click the arrow of the Font Color button and select White, Background 1 (Theme Colors, 1st column, 1st row)
3. On the form, click the Transactions title
4. In the Property Sheet, click Fore Color, then click its ellipsis button and select Yellow (Standard Colors, 4th column, 7th row)

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

1. In the Controls section of the Ribbon, click the Label and click an unoccupied area in the Detail section of the form
2. Type Financial Details and press Enter
3. On the Ribbon, click the Format tab
4. Click the arrow of the Back Color button and select White, Background 1, Darker 15% (Theme Colors, 1st column, 3rd row)
5. Save the form

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

1. While the Finantial Details label is still selected, in the Property Sheet, click Border Color, then click the arrow of its combo box and select Black, Text 1 (Theme Colors, 2nd column, 1st row)
2. Click each text box and set its Border Color to Black

Special Effects

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

1. On the form, click the TransactionID text box
2. In the Property Sheet,, click Special Effect, then click the arrow of its combo box and select Chiseled
3. Click each text box and set its Border Color to Black
4. Complete the design of the form as follows:

   

5. Save and close the form

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

• Close Microsoft Access