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:
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:
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:
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:
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:
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 . 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:
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.
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:
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.
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.
We saw that the Windows controls on a form could be used to display data from a table. This is done by specifying a list of values in the RecordSource property of the form. To get the set of records that the RecordSource property of a form holds, you can access its RecordsetClone property.
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 the Boolean Record Selectors property field. To programmatically control the presence or absence of the record selector on a form, assign a value of True (to display it) or False (to hide it) to the RecordSelector property of the form. Here is an example:
Private Sub cmdManipulate_Click() Me.RecordSelectors = False End Sub
By default, a form is equipped with buttons that allow the user to navigate back and forth between records. The presence or absence of navigation buttons is controlled by the Boolean Navigation Buttons property. To programmatically control the presence or absence of the navigation buttons, access the form's NavigationButtons property and assign the desired Boolean value. Here is an example:
Private Sub cmdManipulate_Click() Me.NavigationButtons = False End Sub
Some operations to perform on a form require that you select it first. If a form is opened on the screen, it may not have focus or it may not be activated. To select a form, if the database is configured to display overlapped windows, you can click its title bar or any section of its body. If a form is closed, to manually select it, in the Navigation Pane, click the form to select it. The name of the form would become highlighted, indicating that it is selected. Here is an example where a form named Exercise and that is selected in the Navigation Pane:
DoCmd.SelectObject acForm, [objectname][, indatabasewindow]
The first argument must be acForm to indicate that you are select a form. The second argument is the name of the form to select. If you only want to highlight the form in the Navigation Pane, then pass the third argument as True.
Here is an example that selects a form named Teachers to highlight it in the Navigation Pane:
Private Sub cmdSelect_Click() DoCmd.SelectObject acForm, "Teachers", True End Sub
If the form is already displaying, it may be in the background. If there is no form by the name you specified in the second argument, you would receive a 2544 error:
If you omit the third argument or pass it as False, the form would be displayed in the foreground. If the form is not opened and you omit the third argument or pass it as False, you would receive a 2489 error:
You can use a conditional statement and error handling to make sure the user doesn't see this dialog box.
If you create a new form, it automatically receives a temporary name but you must save it to have an actual name for the form. Once a form exists, if you want, you can change its name if you judge this necessary. In order to rename a form, it must be closed.
To programmatically rename a form, you can call the Rename() method of the DoCmd object. The syntax to use is:
DoCmd.Rename(NewName, ObjectType, OldName)
The first argument is the new name that the form will have. Normally, only the first argument is required. If you call this method using only the first argument and if you call this argument from a form, the form you are using would be renamed. But, based on the rules of the Microsoft Windows operating system, you cannot rename a form that is currently opened. Consequently, if you call this method from a form and provide only the first argument, you would receive a 2009 error:
This means that, you should always pass the first argument.
The second argument is a member of the AcObjectType enumeration. For a form, this member must be acForm. The third argument is the name of the existing form that you want to rename. Here is an example:
Private Sub cmdRename_Click() DoCmd.Rename "Children", AcObjectType.acForm, "Pupils" End Sub
Instead of renaming a form, you can make a copy of it and keep the original. To visually copy an existing form, in the Navigation Pane, right-click the form and click Copy. Then right-click an area of the Navigation Pane and click Paste. This would open the Paste Table As dialog box in which you can enter the new name of the copied object.
To programmatically copy a form, you can call the CopyObject() method of the DoCmd object using the following syntax:
DoCmd.CopyObject [destinationdatabase][, newname], ObjectType, sourceobjectname]
The destinationdatabase argument is the name or path of the database where the copied form would be sent to. If you are copying the form in the same database, you can omit this argument. The newname argument is the name that you want the new form to hold. The third argument must be acForm. The last argument is the name of the existing form.
Before using a form or performing an update in it, in most cases, you probably would need to open it first but this may depend on what you want to do at the time. This is because a form offers many types of views. One of them in the Form View.
DoCmd.OpenForm FormName[, view][, datamode]
The first argument of this method is the name of the form that you want to open. This second argument is optional. Here is an example:
Private Sub cmdOpenForm_Click() DoCmd.OpenForm "Employees" End Sub
If you want to use it, the second argument is a member of the acFormView enumeration. Its value can be one of the following:
If you omit the second argument, the acNormal option applies. Here is an example:
Private Sub cmdOpenForm_Click() DoCmd.OpenForm "Employees", AcFormView.acNormal End Sub
When this code executes, a form named Employees would be opened in the Form View.
Instead of writing the code to open a form, you can use the Command Button Wizard that can do this for you. To do this, while the form is opened in Design View, in the Controls section of the Ribbon, click the More button and make sure the Use Control Wizards option is down . Then, click the Button and click the form. The wizard would start and you can select the Open Form option after selecting Form Operations.
When you open a form, it fires the Open event. This event runs before the form actually displays. The structure of this event is:
Private Sub Form_Open(Cancel As Integer) End Sub
This event takes one argument. Because this event is fired as the form is about to be opened but before it actually does, the Cancel argument allows you at the last minute to proceed or to stop the process. If you set this argument to False, the form would not be opened.
When you open a form using the OpenForm method of the DoCmd argument, the form is equipped with an argument named OpenArgs, which is of type Variant. The OpenArgs argument is typically used to carry a string from the object or action that is opening the form, to the form that is being opened.
Form maintenance includes deleting, copying, or renaming a form, etc. Most of these operations require that the form be closed. To manually delete a form, in the Navigation Pane, right-click it and click Delete. To programmatically delete a form, call the DeleteObject() method of the DoCmd object. Its syntax is:
DoCmd.DeleteObject ObjectType, [objectname]
The ObjectType argument is a member of the AcObjectType enumeration. For a form, it would be acForm. Since the second argument is optional, you can call this method as follows:
Private Sub cmdDelete_Click() DoCmd.DeleteObject AcObjectType.acForm End Sub
You can omit AcObjectType. When this method is called, the form that is selected in the Navigation Pane would be deleted. As mentioned already, the second argument is optional. If you want to use it, pass the name of the form to be deleted. Here is an example:
Private Sub cmdDelete_Click() DoCmd.DeleteObject AcObjectType.acForm, "Customers" End Sub
When this code executes, a form named Customers would be deleted. When you call this method and pass the second argument, if no form is selected in the Navigation Pane and if you omit the second argument, you would receive a 2493 error stating that the action requires a form name:
If you decide to pass the second argument but provide a name for a form that cannot be found in the Navigation Pane, you would receive a 7874 error:
This means that, either you select a form prior to calling this method, or you pass the name of the form to delete as the second argument.
To open a form and display it to the user, the computer must allocate memory for it. This is referred to as loading the form into memory. When this happens, the form fires the Load event. The structure of this event is:
Private Sub Form_Load() End Sub
This event simply allows you to know that the form as been presented to the user so you can take the appropriate action.
After using a form, you can close it. As the form is being closed, it must empty the memory it was using. At this time, the form fires an Unload event. Its structure is:
Private Sub Form_Unload(Cancel As Integer) End Sub
This event takes one argument: Cancel. If you don't want the form to be closed, set the Cancel argument to False.
The Microsoft Windows operating system provides the ability to work on many objects and many windows at the same time. In Microsoft Access, you can display as many forms as you want (or as your computer memory can afford). When many forms display, only one can geometrically be in the front. To bring another form to the front, you can click it, usually its title bar. When a form is brought to the front, it is said to be activated. When this happens, the form fires the Activate event. The structure of this event is:
Private Sub Form_Activate() End Sub
As you can see, this event does not take any argument. It simply allows you to know that the form has been brought to the front and you can do what you judge necessary.
As mentioned already, you can display different forms on the screen and switch to the one you need at one particular time. When you switch from one from to another, the form that goes in the background fires the Deactivate event. Its structure is:
Private Sub Form_Deactivate() End Sub
This event takes no argument.
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 the Current event. The structure of this event is:
Private Sub Form_Current() End Sub
This event only signals that you have just gotten to a record.
After using a form, you can close it if it is (still) opened. If there is a structural change that needs to be saved, Microsoft Access would prompt you.
To manually close a form, you can click its close button . You can also press Ctrl + F4.
DoCmd.Close ObjectType, [objectname], [save]
The first argument is a member of the AcObjectType enumeration. For a form, the member to use is called acForm:
DoCmd.Close AcObjectType.acForm, [objectname], [save]
Of course, you can omit AcObjectType and use the following syntax:
DoCmd.Close acForm, [objectname], [save]
The first argument must be specified as acForm to indicate that you want to close a form. If you are closing the same form that is calling this method, this is the only argument you would need. Consider the following example:
Private Sub cmdClose_Click() DoCmd.Close End Sub
In this case, the form would be closed.
The second argument can be the name of the form you want to close. This argument is useful if you are trying to close a form other than the one that is making the call. Here is an example:
Private Sub cmdClose_Click() DoCmd.Close acForm, "Employees" End Sub
In this example, a form named Employees is asked to be closed. If you suspect that the form would need to be saved before formally being closed, you can pass a third argument that is a member of the AcCloseSave enumeration. The available values are:
When calling the Close() method to close a form, if the form is not opened or if the specified form does not exist, nothing would happen (you would not receive an error).
Instead of writing your own code, to let Microsoft Visual Basic write it for you, you can use the Command Button Wizard.
As a form is being closed, it fires a Close event. The structure of this event is:
Private Sub Form_Close() End Sub
Sometimes, you will open a form from another form. Some other times, you will use intermediary actions to create new values of a record. When the values in the controls of a record have been changed, the form does not automatically update the controls. To take care of this, you can refresh the record. To do this programmatically, you can call the Refresh() method of the Form class. This could be done as follows:
Private Sub cmdRefresh_Click() Refresh End Sub
An alternative is to use the DoCmd object that is equipped with a method named RefreshRecord. Here is an example of calling it:
Private Sub cmdRefreshRecord_Click() DoCmd.RefreshRecord End Sub
You may have specified the record source of a form. When the records in that source changes, the form does not automatically update itself. To take care of this, you can call the Requery() method of the Form class. This could be done as follows:
Private Sub cmdQuery_Click() Requery End Sub
As an alternative, the DoCmd object is equipped with a method named Requery. Here is an example of calling it:
Private Sub cmdRefreshRecord_Click() DoCmd.Requery End Sub