FMS Site Search

Passing Multiple Values to Microsoft Access Forms and Reports with the OpenArgs Parameter

Provided by: FMS Development Team

Tip Usage: Microsoft Access - Beginner to Intermediate VBA Developer

Passing a Value to a Microsoft Access Form or Report with its OpenArgs Parameter

The OpenArgs parameter is one of the values you can pass to a form or report when you open it
with the DoCmd command. Rather than creating global variables, assigning them and having the
form/report use it, passing the information through the OpenArgs parameter makes your code
cleaner and easier to support and understand. Global variables can quickly get confusing if
they are assigned in multiple places.

Parameter Definitions

Name

Required?

Data Type

Description

FormName

Yes

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

No

AcFormView

The AcFormView constant specifies the view in which the form
will open. The default value is acNormal.

FilterName

No

Variant

A string expression that's the valid name of a query in the current database.

WhereCondition

No

Variant

A string expression that's a valid SQL WHERE clause without the word WHERE.

DataMode

No

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

No

AcWindowMode

A AcWindowMode constant that specifies the window mode in which
the form opens. The default value is acWindowNormal.

OpenArgs

No

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.

Unfortunately, it's not possible to pass multiple OpenArgs parameters to a Microsoft
Access form or report, or pass an array of values. Only one OpenArgs string can be used in the OpenForm or OpenReport command.

By combining your values into one string separated by a character that would not be in
your string, you can overcome the OpenArgs limitation and still use DoCmd to pass them.
The OpenArgs parameter is a string that the form can read once it is opened.
Calling the form, add the string to the OpenArgs parameter like this:

A real-life example would be to set default values for controls on a form
being opened. Suppose that you have a form of Product Categories, and the
user needs to enter a new product for the category. You could open the
Product entry form and then default the category value for them. With the
following parameter, you could default the Category combo box to the current value.

For example:

DoCmd.OpenForm "frmName", OpenArgs:="cboCategory|" & txtCategoryID

which may result in a value such as "cboCategory|123"

Rather than explicitly specifying the parameter, you can also pass it as the last parameter in the list:

In the Load event of the Form, add some VBA code to parse the
information that is being passed in OpenArgs. In this example, we are
using two values, separated by the 'Pipe' (|) character. The code in the
form finds the existence of the Pipe character, then extract the first and
second values to make the assignment.

Example

Private Sub Form_Load()
Dim intPos As Integer
Dim strControlName As String
Dim strValue As String
If Len(Me.OpenArgs) > 0 Then
' Position of the pipe
intPos = InStr(Me.OpenArgs, "|")
If intPos > 0 Then
' Retrieve Control Name from the first part of the string
strControlName = Left$(Me.OpenArgs, intPos - 1)
' Retrieve Value to Assign from the end of the string
strValue = Mid$(Me.OpenArgs, intPos + 1)
' Assign the value to the control
Me(strControlName) = strValue
End If
End If
End Sub