Passing Multiple Values to Access Forms with the OpenArgs Parameter in Microsoft Access

Provided by: FMS Development Team

Tip Usage: Microsoft Access - Beginner to Intermediate VBA Developer

If you would like to pass one or more parameters to a form (or report) in Microsoft Access, you can use the OpenArgs parameter of the DoCmd Object to send 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)"

One 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"

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

For example:

Private Sub Form_Load()
  Dim intPos As Integer 'Position of the Pipe
  Dim strControlName As String 'Controlname passed
  Dim strValue As String 'Value to assign

  If Me.Len(OpenArgs) > 0 Then
    intPos = InStr(Me.OpenArgs, "|")

    If intPos > 0 Then

      'Retrieve Control Name
      strControlName = Left$(Me.OpenArgs, intPos - 1)

      'Retrieve Value to Assign
      strValue = Mid$(Me.OpenArgs, intPos + 1)

      'Make the Assignment
      Me(strControlName) = strValue

    End If
  End If
End Sub

Return to the tips page