DoCmd.OpenForm and its options

The following description of DoCmd.OpenForm comes from the Access online help. The syntax of the method is

DoCmd.OpenForm FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs

Only FormName is required, all other arguments are optional. If you want to use some, but not all, arguments, you must include commas for the ones you skip, or the code will fail.

The arguments for DoCmd.OpenForm. Click on the links to see descriptions for constants.

Name	Data Type	Description
FormName	Variant	A string expression  that's the valid name of a form  in the current database. If you execute Visual Basic code containing the OpenForm method in a library database , Microsoft Access looks for the form with this name first in the library database, then in the current database.
View	AcFormView	A AcFormView constant that specifies the view in which the form will open. The default value is acNormal.
FilterName	Variant	A string expression that's the valid name of a query  in the current database. This query van be used to filter the form.
WhereCondition	Variant	A string expression that's a valid SQL WHERE clause  without the word WHERE.
DataMode	AcFormOpenDataMode	A AcFormOpenDataMode constant that specifies the data entry mode for the form. This applies only to forms opened in Form view or Datasheet view. The default value is acFormPropertySettings.
WindowMode	AcWindowMode	A AcWindowMode constant that specifies the window mode in which the form opens. The default value is acWindowNormal.
OpenArgs	Variant	A string expression. This expression is used to set the form's OpenArgs property. This setting can then be used by code in a form module , such as the Open event procedure . The OpenArgs property can also be referred to in macros  and expressions . For example, suppose that the form you open is a continuous-form  list of clients. If you want the focus  to move to a specific client record when the form opens, you can specify the client name with the openargs argument, and then use the FindRecord method to move the focus to the record for the client with the specified name.

AcFormView constants

Name	Value	Description
acDesign	1	The form opens in Design view.
acFormDS	3	The form opens in Datasheet view.
acFormPivotChart	5	The form opens in PivotChart view.
acFormPivotTable	4	The form opens in PivotTable view.
acLayout	6	The form opens in Layout view.
acNormal	0	(Default) The form opens in Form view.
acPreview	2	The form opens in Print Preview.

AcFormOpenDataMode constants

Name	Value	Description
acFormAdd	0	The user can add new records but can't edit existing records.
acFormEdit	1	The user can edit existing records and add new records.
acFormPropertySettings	-1	The user can only change the form's properties.
acFormReadOnly	2	The user can only view records.

AcWindowMode constants

Name	Value	Description
acDialog	3	The form or report's Modal and PopUp properties are set to Yes.
acHidden	1	The form or report is hidden.
acIcon	2	The form or report opens minimized in the Windows taskbar.
acWindowNormal	0	(Default) The form or report opens in the mode set by its properties.

Examples

Opening a form and using an existing query as a filter

Assuming that qrySales_NSW filters the Sales qrySales for sales from New South Wales, the following code will use that query to filter frmSales:

DoCmd.OpenForm "frmSales", acNormal, "qrySales_NSW"

Creating an SQL statement to display records matching the ID of the current form

Often you will want to open a form and display records matching the ID of the currently open form.

Private Sub cmdOpenSales_Click()
  Dim sWHERE As String

  'Comment out or delete the line that does not apply --
  ''If the ID is a number, built the WHERE clause like this:
  sWHERE = "[CustomerID] = " & Me.CustomerID
  ''If the ID is a text value, you need to surround the ID in single quotes
  sWHERE = "[CustomerID] = '" & Me.CustomerID & "'"

  'Open the form
  DoCmd.OpenForm "frmSales", acNormal, , sWHERE
End Sub

Opening a new, blank record for data entry

To open a blank record, set the data mode to acFormAdd:

 DoCmd.OpenForm "frmSales", acNormal, , , acFormAdd

Opening a form so that the data is read-only

To open a form in read-only mode, set the data mode to acFormReadOnly:

 DoCmd.OpenForm "frmSales", acNormal, , ,acFormReadOnly

Opening a form as a Dialog form

When a form opens in Dialog mode, its Popup and Modal properties are set to Yes. This means that:

• The form will stay on top until you dismiss it
• The user is forced to interact with the form -- no other forms or windows are active while the Dialog form is displayed
• If your window is Maximized, the dialog will not affect the window state of the application, even though the popup form will not open Maximized. This means that you won't have your forms resizing as you open and close them

 DoCmd.OpenForm "frmSales", acNormal, , , , acDialog

Opening a form and keeping it hidden

If you are building a multi-user database where users will have different permissions, you can create a startup form that determines the user's Windows login name and holds a number of settings that determine what happens when a user launches a form or report. Because this is often a sentinel form, you don't necessarily want to display it and run the risk of having a user close it; so, open it hidden, and refer to it as needed.

 DoCmd.OpenForm "frmStartup", acNormal, , , , acHidden

Using OpenArgs to pass one or more parameters to a form when it opens

The OpenArgs argument is very versatile. It is not limited to just passing one parameter; you can use a divider and parse out the components to change values of controls, set form properties, navigate to a selected record, and more.

First, create the DoCmd statement in the caller form. This statement will create a new record in the Sales form, populated with the ID of the customer on the current record.

 DoCmd.OpenForm "frmSales", acNormal , , , acFormAdd, , "CustomerID|" & Me.CustomerID

Then, use the target form's Load event to set the value in the combo box. The pipe symbol is the separator for the multiple parameters. We can use the Split function (in Access 2000 and higher) to parse out the values. In the first code sample, the ID is a number; in the second, it is text.

Note: In SQL it is necessary to wrap text values in single or double quotes. This is NOT required for OpenArgs, and will cause errors if you do it.

Private Sub Form_Load()
  'Use this version if the ID is a number
  Dim x As Variant
  Dim strControl As String
  Dim lngID As Long

  'If parameters exist, use them
  If Len(Me.OpenArgs) > 0 Then
    'Split creates a zero-based array from the input string
    x = Split(Me.OpenArgs, "|")
    strControl = x(0)
    lngID = x(1)

    Me(strControl) = lngID
  End If
End Sub

Private Sub Form_Load()
  'Use this version if the ID is text
  Dim x As Variant
  Dim strControl As String
  Dim strID As Long

  'If parameters exist, use them
  If Len(Me.OpenArgs) > 0 Then
    'Split creates a zero-based array from the input string
    x = Split(Me.OpenArgs, "|")
    strControl = x(0)
    strID = x(1)

    Me(strControl) = strID
  End If
End Sub

See here for a way to use OpenArgs to pass a SQL statement, so that a form or listbox is filtered when it opens.

  Home | About | Links | Contact | Excel | Access