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.

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