1. Start Microsoft Accecss
2. From the resources that accompany these lessons, open the Clarksville Ice Cream1 database

You may know already that, to specify a numeric value for the property of a control in the Properties window, you can type the value as you know it. An example would be 2. Another example would be 4.50. Behind the scenes, Microsoft Access (actually the database engine) uses another technique to determine these types of measure.

A twentieth of an imperial point, abbreviated twip (derived from TWentieth of an Imperial Point) represents the 1/20 of a typographical point. Since you are probably more familiar with the inch system, a twip represents 1/1440 inch. Therefore, in many examples that follow, you may see higher numbers than those specified in the Properties window, simply remember that the Visual Basic language (the compiler in the Microsoft Visual Basic environment used in Microsoft Access) uses the twip system for the measures.

Many of the aesthetic characteristics of an object (tables, forms, reports, and controls) use colors. The color provides an enhanced variation of the ratios of red, green, and blue applied to the appearance of an object. The Visual Basic language provides support for colors at different levels.

In Microsoft Windows, a color is a long integer whose value ranges from 0 to 16777216. In many cases, to use a color, if you know the exact value it represents, you can assign it to the property.

To programmatically create a color if you know its variances of red, green, and blue, you can call the RGB() function. Its syntax is:

Function RGB(RedValue As Byte, GreenValue As Byte, BlueValue As Byte) As long

This function takes three arguments and each must hold a value between 0 and 255:

• The first argument represents the ratio of red of the color
• The second argument represents the green ratio of the color
• The last argument represents the blue of the color.

After the function has been called, it produces a number whose maximum value can be 256 (that is, the number ranges from 0 to 255 included) * 256 * 256 = 16,777,216, which represents a color.

To help you programmatically create a form, the Application class is equipped with a method named CreateForm. The syntax of this method is:

CreateForm([database[, formtemplate]])

This method takes two arguments and both are optional. The first argument to this method is the name of the database that will receive the form. If the form will be added to the current database, you can omit this argument. The second argument is the name of an existing form that you want to use as template. If you specify this argument, you must make sure that you provide the name of a form. Otherwise, you can omit it. Here is an example of creating a form:

Private Sub cmdCreateForm_Click()
    Dim frmEmployees As Form
    
    Set frmEmployees = CreateForm
End Sub

After this method has been called, a form is created but it is minimized.

To refer to a form of a Microsoft Visual Basic application, you can use the Me keyword.

After creating a database, or while working on one, all of the forms that belong to the database are stored in a collection called AllForms. As seen for the AllTables collection, the AllForms collection is equipped with the Count property that holds the number of forms that belong to the current database. Each form can be located by its name or its index, using the Item() property.

In Microsoft Access, a form is an object of type Form. A form that is opened in a database is a member of the Forms collection. To refer to an open form in your code, you can enter the Forms collection, followed by an exclamation point !, followed by the name of the form. Here is an example that refers to a form named Students:

Forms!Students

You can also include the name of the form between an opening square bracket and a closing square bracket but, if the name is in one word, then the square brackets are optional:

Forms![Students]

If the name is made of more than one word, then the square brackets become required. Here is an example that refers to a form named Potential Applicants:

Forms![Potential Applicants]

If you omit the square brackets in this case, the expression or the code may not work.

A form can also be referred to by an index in the Forms collection. The first form of the collection has an index of 0 and can be accessed with Forms(0). The second form in the collection has an index of 1 and can be referred to by Forms(1), and so on. The name of the form can also be used as index. For example, a form named Students can be referred to as Forms("Students").

We mentioned that a form is mostly used as a container because it hosts some of the controls of its application. The controls that a form hosts are members of the Controls collection.

If a database is configured to show overlapped windows, the right side of a form's title bar displays three system buttons , (or ), or . To programmatically minimize a form, you can call the Minimize() method of the DoCmd object. Here is an example:

Private Sub cmdManipulate_Click()
    DoCmd.Minimize
End Sub

To programmatically maximize a form, you can call the Maximize() method of the DoCmd object.

If a form is maximized, to restore it, the user can click the Restore button . If you programmatically create a form, it gets automatically minimized.

To programmatically restore a form, call the Restore() button of the DoCmd object. Here is an example:

Private Sub cmdCreateForm_Click()
    Dim frmEmployees As Form
    
    Set frmEmployees = CreateForm

    ' Restore the minimized form.
    DoCmd.Restore
End Sub

A form displays on the screen using its dimensions. These are the width and height. The form itself controls only the width and this is represented by a property Width. The height is controlled by the form's sections.

To programmatically change the width of a form, access its Width property and assign the desired value. Here is an example:

Private Sub cmdWidth_Click()
    Width = 8650
End Sub

The most visible part of a form is an area called Detail. This section starts on a bar labeled Detail and ends at the bottom unless a new section starts. To programmatically access the Detail section of a form, use either Detail or Me.Detail.

Besides the Detail section, a form can be enhanced with one or two more sections: Header and Footer.

To access the Header section in your code, use the form's FormHeader property. To programmatically access the Footer section, use the form's FormFooter property. This also applies for the BackColor property. The SpecialEffect property allows you to raise or sink a section.

As much as you can, you should design your (non-Datasheet) form to display all of the fields of a record. Sometimes this will not be possible. If a form possesses too many fields, Microsoft Access would equip it with one or two scroll bars.

The presence of scroll bars is controlled by the Scroll Bars property. To programmatically control the presence or absence of scroll bars, access the ScrollBars property of the form and assign one of the following four values:

By default, when a previously created and saved form appears, Microsoft Access remembers the previous position the form had and restores it. If you can make sure that the form is always centered when it comes up. To make this possible, the form is equipped with the Auto Center Boolean property. When set to Yes, this property causes the form to be centered when it displays to the user.

To programmatically center a form, access its AutoCenter property and assign True or False to it. 

If you create a form that has too many objects, you can divide the form in sections called pages. Then, you can ask Microsoft Access to display only the desired section when necessary. To make this possible, use the Insert or Remove Page Break control to create the desired sections. You should also provide buttons that would be used to access the sections. Here is an example:

After creating the sections, you should (must) move the border of the form in Design View:

To access a page, you can call the GoToPage() method of either the Form or the DoCmd class. The syntax of this method is:

Form|DoCmd.GoToPage(ByVal PageNumber As Lond, _
		    Optional ByVal Right As Long = 0, _
		    Optional ByVal Down As Long = 0)

Here is an example:

Private Sub cmdTop_Click()
    GoToPage 1
End Sub

Private Sub cmdMiddle_Click()
    DoCmd.GoToPage 2
End Sub

Private Sub cmdBottom_Click()
    GoToPage 3
End Sub

A form is equipped with special horizontal lines used to visually separate sections of a form. The presence or absence of the horizontal lines is controlled by the Boolean Dividing Lines property. To programmatically control the presence or absence of the dividing lines of a form, access its DividingLines property and assign the desired Boolean value. Here is an example:

Private Sub cmdManipulate_Click()
    DividingLines = False
End Sub

When the user is ready to perform data entry, you can open the form specifically for that purpose. That is, you can programmatically navigate the form 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, ObjectName, Record, Offset)

The first argument to 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, you can set this argument to 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 would 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.

1. In the Navigation Pane, right-click the IceCreamOrders form and click Design View
2. Right-click the New Order button and click Build Event...
3. In the Choose Builder dialog box, double-click Code Builder
4. Implement the event as follows:

   Private Sub cmdNewOrder_Click()
       DoCmd.GoToRecord , , AcRecord.acNewRec
   End Sub

5. Return to Microsoft Access

After creating a table and its column(s), you can populate the list 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.

Employees usually use forms to perform data entry and you have various options to control data entry on a form. To make data entry possible, the form is equipped with a Boolean property named AllowAdditions. The default value of this property is True. If you want to prevent the user from adding a new record to a form, set its AllowAdditions property to False. Here is an example:

Private Sub cmdRecordAddition_Click()
    AllowAdditions = False
End Sub

The user would be able to change existing records, even possibly to delete an existing record but not create a new record.

Most users know how to use navigation buttons to move among records. Some others don't. This means that, sometimes, you will need a way to make it easy for the user to perform this navigation. Fortunately, Microsoft Access provides everything you need to do this.

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 ObjectName As String = "", _
	         ByVal Optional Record As AcRecord = AcRecord.acNext, _
	         ByVal Optional Offset As Integer = 1)

As you can see, 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:

As you can see, 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 the AcRecord enumeration. The members of this 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 hte 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.

1. Double-click the button at the intersection of the rulers
2. In the Properties window, click All and double-click Navigation Buttons to set its value to No
3. Return to Microsoft Visual Basic
4. In the Object combo box, select cmdFirstRecord
5. Implement the event as follows:
   

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

6. In the Object combo box, select cmdPreviousRecord
7. Implement the event as follows:
   

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

8. In the Object combo box, select cmdNextRecord
9. Implement the event as follows:
   

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

10. In the Object combo box, select cmdLastRecord
11. Implement the event as follows:
    

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

12. Return to Microsoft Access
13. Switch the form to Form View
     
    
14. Save the form
15. Right-click the title bar of the form and click Design View
16. Return to Microsoft Visual Basic

After creating a table and saving it, you can use it as the source of data for a form. To do this visually, in the Properties window of the form, specify the Record Source property. You can also assign the name of a table, as a string, to the form's RecordSource property. Here is an example:

Private Sub cmdSetRecordSource_Click()
    Me.RecordSource = "Employees"
End Sub

Alternatively, you can access the Record Source property of the form and click its ellipsis button . This would prompt you to visually create a SQL statement. Microsoft Access would create the necessary SQL statement for you and assign it to the Record Source property of the form.

If you had programmatically create the form and you plan to use it for data, access its RecordSource property and assign a table, a query, or a SQL expression to it. Here is an example:

Private Sub cmdCreateForm_Click()
    Dim frmEmployees As Form
    
    Rem Create the form
    Set frmEmployees = CreateForm
    
    frmEmployees.RecordSource = "Employees"
End Sub

Instead of visually creating the statement, you can write a SQL statement that selects the records and assign that statement to the RecordSource property of the form in code.