Guide to Closing Forms with VBA

Because it may seem like a mundane task to many, it can be easy to overlook a few opportunities for improving our applications' overall performance with something as simple as closing a form.  AccessInsider explores one such opportunity by providing an example for utilizing a public function call to close forms in order to reduce the amount of code required to load each time a form is launched.

While any actual performance benefit will likely be undetectable using this particular example, it still provides an adequate demonstration of how a developer can streamline an application's code for repetitive procedures to optimize performance.

The common way closing a Microsoft Access for using VBA is by utilizing the DoCmd.Close method.

DoCmd.Close(ObjectType, ObjectName, Save)

Parameters

Name	Required/Optional	Data Type	Description
ObjectType	Optional	AcObjectType	A AcObjectType constant that represents the type of object to close.
ObjectName	Optional	Variant	A string expression that's the valid name of an object of the type selected by the objecttype argument.
Save	Optional	AcCloseSave	A AcCloseSave constant tha specifies whether or not to save changes to the object. The default value isacSavePrompt.

Table Source: MSDN Office 2007: Access Developer's Reference

Example 1:

Private Sub cmdClose_Click()
     DoCmd.Close
End Sub

Result:

Closes the active window.

Example 2:

Private Sub cmdClose_Click()
     DoCmd.Close , , acSaveYes
End Sub

Result:

Closes and saves active window object.

Note: Notice that even though we didn't include the object type or object name parameters, we still had to include commas for those parameters when using the save parameter.  This can be avoided by identifying the parameters used as demonstrated below:

Private Sub cmdClose_Click()


    DoCmd.Close Save:=acSaveYes
   
End Sub

Tip: Naming the parameters in your code, as opposed to just using commas, will also make it more readable for other users/developers.

Example 3: Close a specified form.

Private Sub cmdCloseFunction_Click()


     DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name


End Sub

Tip: Use quotation marks ("") around the form name for the ObjectName parameter when closing other forms.

Example 4: Closing a specified form using a public function call.

 Module: modPublicFunctions

Public Function CloseForm(FormName As String)
    
'// Author:     Jonathon Carrell
'// Purpose:    Public function to close forms.
'// History:    7 Jan 2012  Function created.


'// Close form.
    DoCmd.Close ObjectType:=acForm, ObjectName:=FormName
    
End Function

Form: frmCloseExample

Private Sub cmdCloseFunction_Click()


'// Call CloseForm function from modPublicFunctions to close this form.
    CloseForm Me.Name
    
End Sub

A functional example database can be found at this link: DoCmd.Close Method, or on theAccessInsider Examples Page.

Comments have been included with the example code as to provide the readers with a better understanding of the methods used.

Posted in: Access,Code,database,Forms,Microsoft Access,MS Access,Programming,Software Development,VBA