Home

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

First Name Last Name Date Hired Hourly Salary
Julie Hanson 04/12/2004 12.52
Suzie Jones 08/02/2002 8.48
John Orbach 04/12/2004 10.84
Lucie Johnson 10/05/2000 12.08

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

AcDataObjectType Member Value Description
acActiveDataObject -1 The action will apply on the current record of the object that is opened
acDataTable 0 The action will be applied on a record of a table
acDataQuery   The action will be applied on a record of a query
acDataForm 2 The action will be applied on a record of a form
acDataReport 3 The action will be applied on a record of a report
acDataServerView 7 The action will be applied on a view of a Microsoft SQL Server database 
acDataStoredProcedure 9 The action will be applied on a stored procedure of a Microsoft SQL Server database 
acDataFunction 10 The action will be applied on a function of a Microsoft SQL Server database

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 Intersection of Rulers
  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 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:

Value Description
0 No scroll bar will display
1 Only the horizontal scroll bar will display
2 Only the vertical scroll bar will display
3 Both the horizontal and the vertical scroll bars will display

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 Intersection of Rulers
  11. In the Property Sheet, double-click On Current
  12. Click its ellipsis button Browse 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 Ellipsis 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

    Losing Focus

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

    Losing Focus

  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:

Property Sheet Properties Window Description
General 0 This is the default value. It means you let Microsoft Access decides, based on the type of value of the control.
If the control is configured to display text, its string would be aligned to the left. If the control is configured to display numbers, dates, or times, its value would be aligned to the right.
Left 1 The values would be aligned to the left
Center 2 The values would be aligned to the center
Right 3 The values would be aligned to the right
Distribute 4 The content would be evenly distributed

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 Align Left
  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:

Constant Description
100 Thin
200 Extra Light
300 Light
400 (Default) Normal
500 Medium
600 Semi-bold
700 Bold
800 Extra Bold
900 Heavy

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 Bold
  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 Browse 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 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:

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:

Property Sheet Properties Window Description
Flat 0 The control is flat. The borders use either a default color specified by the operating system or a color specified in Design view
Raised 1 The control's borders appear raised. The left and top borders are highlighted. The right and bottom borders appear as its shadow
Sunken 2 The control's borders appear sunken. The left and top borders appear as its shadow. The right and bottom borders are highlighted
Etched 3 The control is flat. It uses a sunken line on its borders
Shadowed 4 The control displays a shadow on its right and bottom borders
Chiseled 5 The control displays a sunken line on its bottom border

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:

    Form Design

  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:

Property Sheet Properties Window Description
Transparent 0 The control shows no border
Solid 1 The control's borders display a solid line
Dashes 2 The control's borders display dashed lines
Short Dashes 3 The control's borders display short dashed lines
Dots 4 The control's borders display dotted lines
Sparse Dots 5 The control's borders display spaced dots on its lines
Dash Dot 6 The control's borders display with a dash-dot combination
Dash Dot Dot 7 The control's borders display with a dash-dot-dot combination
Double Solid 8 The control's borders display a double solid line

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:

Borders Styles

Practical Learning: Ending the Lesson

  • Close Microsoft Access
 
 
   
 

Orevious Copyright © 2016, FunctionX, Inc. Next