DataWright Information Services

Consulting and Resources for Excel and Access




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.