Microsoft Access Database Development With VBA

Introduction to Forms and Records

 

Forms Fundamentals

 

Introduction

A visual application such as a computer database usually starts with a rectangular form that hosts other controls. In Microsoft Access, you can create a form that stands on its own like the forms of other programming environments, or you can create a form that is tied to a table.

Practical LearningPractical Learning: Introducing Forms

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

Measuring in Twips

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.

Using Colors

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.

The Primary Characteristics of a Form

 

Creating a Form

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.

Referring to a Form

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.

Minimizing or Maximizing a Form

If a database is configured to show overlapped windows, the right side of a form's title bar displays three system buttons Minimize, Maximize (or Restore), or Close. 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.

Restoring a Form

If a form is maximized, to restore it, the user can click the Restore button Restore. 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

The Form's Width

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 Detail Section of a Form

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.

The Header and Footer Sections

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.

The Form's Scroll Bars

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:

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
   

Form Automatic Centering

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. 

Using the Pages of a Form

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 Insert or Remove Page Break to create the desired sections. You should also provide buttons that would be used to access the sections. Here is an example:

A form with page breaks

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

Page Break

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

Dividing Lines

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

Forms and Records

   

Introduction to Programmatic Data Entry

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.

Practical LearningPractical Learning: Accessing a Form for Data Entry

  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

Controlling Record Addition on a Form

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

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.

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.

Record Navigation

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:

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

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.

Practical LearningPractical Learning: Navigating the Records

  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
     
    Clarksville Ice Cream
  14. Save the form
  15. Right-click the title bar of the form and click Design View
  16. Return to Microsoft Visual Basic

The Record Source of a Form

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

 

 

 
 
 

The Clone of a Form's Recordset

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.

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

Navigation Buttons

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

Introduction to Using a Form

 

Selecting a Form

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:

A form selected in the Navigation Pane

To programmatically select a form, use the DoCmd object that is equipped with the SelectObject() method. The syntax to use would be:

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.

Renaming a Form

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:

Error 2009

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

Copying a Form

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.

Opening a 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.

To programmatically open a form, you can use the DoCmd object that provides the OpenForm() method. Its syntax is:

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:

acFormView Member Result
acDesign The form will display in Design View
acFormDS The form will display like a table in Datasheet View
acFormPivotChart The form will display as a pivot chart
acFormPivotTable The form will display as a pivot table
acLayout The form will display in Layout View
acNormal The form will display in Form View
acPreview The form will display in Print Preview

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 More and make sure the Use Control Wizards option is down Use Control Wizards. Then, click the Button 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.

The Opening Argument of a Form

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.

Deleting a Form

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:

Error 2493

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:

Error 7874

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.

The Events of a Form

   

Loading a Form

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.

Practical LearningPractical Learning: Loading a Form

  1. In the Object combo box, select Form
  2. Implement the event as follows:
    Private Sub Form_Load()
        MsgBox "The form has been loaded.", _
               vbInformation Or vbOKOnly, _
               "Clarksville Ice Cream"
    End Sub

Activating a Form

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.

Practical LearningPractical Learning: Activating a Form

  1. In the Procedure combo box, select Activate
  2. Implement the event as follows:
    Private Sub Form_Activate()
        MsgBox "The form has been activated.", _
               vbInformation Or vbOKOnly, _
               "Clarksville Ice Cream"
    End Sub

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

Practical LearningPractical Learning: Accessing the Current Record

  1. In the Procedure combo box, select Activate
  2. Implement the event as follows:
    Private Sub Form_Current()
        MsgBox "A record has been accessed.", _
               vbInformation Or vbOKOnly, _
               "Clarksville Ice Cream"
    End Sub

Closing a Form

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 Close. You can also press Ctrl + F4.

To programmatically close a form, you can call the Close() method of the DoCmd object. Its syntax is:

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:

View Name Result
acSaveNo The form doesn't need to be saved
acSavePrompt Prompt the user to save the changes. This is the default
acSaveYes Save the form without having to prompt the user

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

Practical LearningPractical Learning: Closing the Form

  1. In the Procedure combo box, select Close
  2. Implement the event as follows:
    Private Sub Form_Close()
        MsgBox "The form will be closed.", _
               vbInformation Or vbOKOnly, _
               "Clarksville Ice Cream"
    End Sub
  3. Close Microsoft Visual Basic and return to Microsoft Access
  4. Click OK every time
  5. Click the New Record button and observe the message box
  6. Click OK
  7. Close the form
  8. When asked whether you want to save, click No

A Form and its Records

 

Refreshing a Form

Sometimes, you will open a form from another form. Some other times, you will use intermediary actions to create new values of a form. When the values in the controls of a form have been changed, the form does not automatically update the controls. To take care of this, you can refresh the form. 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

Re-Querying a Form

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
 
 
   
 

Previous Copyright © 2002-2013 FunctionX, Inc. Next