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.
DoCmd.OpenForm Definition and Parameters
Here's the complete definition of the OpenForm
command from the Microsoft Access Help system:
DoCmd.OpenForm
FormName, View, FilterName,
WhereCondition, DataMode,
WindowMode, OpenArgs
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. |
The DoCmd.OpenReport command has similar
parameters.
The Problem: Passing More than One Value to the
Form/Report
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.
The Solution: Concatenate Your Values
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:
DoCmd.OpenForm "frmName", OpenArgs:="Parameter(s)"
Similarly, for reports, you can use this:
DoCmd.OpenReport "rptName", OpenArgs:="Parameter(s)"
Passing Two Values in the OpenArgs Parameter
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:
DoCmd.OpenForm "frmName", , , , , , "cboCategory|" & txtCategoryID
Parsing the OpenArgs Parameter in the Form's Load Event
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
Return to the tips page
|
Email Newsletter
Developer Team Blog
Facebook
Twitter
All Our Microsoft Access Products
