Closing an Access form or report with DoCmd.Close

You can close the active form (or other Access object) using DoCmd.Close without any arguments. However if you want to be sure the intended form is closed it is better to be explicit. Below is code commonly used in a close button on a form.

Private Sub cmdClose_Click()
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name, Save:=acSavePrompt
End Sub

Save record before closing form

Closing the form this way however does not save a new record or any changes you may have made. If you want to save records you first have to call DoCmd.RunCommand acCmdSaveRecord.

Private Sub cmdClose_Click()
    On Error GoTo HandleError
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name, Save:=acSavePrompt
    HandleExit:
    Exit Sub
    HandleError:
    MsgBox Err.Description
    Resume HandleExit
End Sub

The role of the error handling On Error GoTo HandleError etcetera is to prevent you from getting an unfriendly run-time error message which may even result in the user getting in your code in case saving the record is not possible for some reason such as a required field having no data.

Click this to see the insertion of the close form command using Code VBA. You will notice several options are available to refine the close behaviour.

Ask the user for confirmation before closing a form

The code below shows how to prompt the user to verify that the form should closed by using the Form_Unload event.

Private Sub Form_Unload(Cancel As Integer)
    If MsgBox("Are you sure that you want to close this form?", vbYesNo) = vbYes Then
    Exit Sub
    Else
    Cancel = True
    End If
End Sub

Close all open forms

Two fragments are available in Code VBA that are used all open forms. The second version, shown below, closes all forms except the one from which the command was started Me.Name.

Dim lngFormsCount As Long, lngFormsCounter As Long
lngFormsCount = Forms.Count - 1
For lngFormsCounter = lngFormsCount To 0 Step -1
    If Forms(lngFormsCounter).Name <> Me.Name Then
    DoCmd.Close acForm, Forms(lngFormsCounter).Name
    End If
Next