databasedev.co.uk - database solutions and downloads for microsoft access

Passing Multiple Parameters to a Microsoft Access Form

Using the OpenArgs parameter of the DoCmd.OpenForm object.

We can use the OpenArgs method to pass a parameter to another form. We can also pass multiple parameters to another form using this method, and the following tutorial details how to perform this task.

Syntax:

DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

In the following example form you will notice that we have embedded two subforms into the main form. These subforms contain details of Home and Away team players:

Image showing the form containing the subforms

The combo box list that includes players from the selected teams uses the NotInList event procedure, which allows us to enter a new player details if they do not have a record in the list.

As we wish to add this new players details to a specified team, when we assign the data not only do we need to add the player name but also the player team.

We perform this action by opening the Player form, and populating the Team ID and the Player Name using the data passed in the OpenArgs event.

When we enter a Player name, like in the image below, that is not in the list, the following NotInList event fires:

Message Box informing that the value is Not In the List

Private Sub cboHomePlayerID_NotInList(NewData As String, Response As Integer)

    Dim Result
    Dim Msg As String
    Dim CR As String

    CR = Chr$(13)

    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub

    ' Ask the user if he or she wishes to add the new Player.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
        ' If the user chose Yes, start the Players form in data entry
        ' mode as a dialog form, passing the new Player name in
        ' NewData to the OpenForm method's OpenArgs argument. The
        ' OpenArgs argument is used in Players form's Form_Load event
        ' procedure.
        DoCmd.OpenForm "frmPlayers", , , , acAdd, acDialog, _
        NewData & ";" & Me.Parent.cboHomeTeamID
        
    End If

    ' Look for the Player the user created in the Players form.
    Result = DLookup("[strPlayerName]", "tblPlayers", _
                     "[strPlayerName]='" & NewData & "'")
    If IsNull(Result) Then
        ' If the Player was not created, set the Response argument
        ' to suppress an error message and undo changes.
        Response = acDataErrContinue
        ' Display a customized message.
        MsgBox "Please try again!"
    Else
        ' If the Player was created, set the Response argument to
        ' indicate that new data is being added.
        Response = acDataErrAdded
    End If
End Sub

The DoCmd.OpenForm method opens the specified form and passes the OpenArgs data in the FormLoad event of the form being opened:

DoCmd.OpenForm "frmPlayers", , , , acAdd, acDialog, _
        NewData & ";" & Me.Parent.cboHomeTeamID

Below we see the Players form, which opens, and which has data populated using the parameters passed:

Populating the form with data passed via the OpenArgs parameters.

The FormLoad event uses the following code:

Private Sub Form_Load()

' If Not IsNull(Me.OpenArgs) Then
' If form's OpenArgs property has a value, assign the contents
' of OpenArgs to the strPlayerName field. OpenArgs will contain
' a Player name if this form is opened using the OpenForm
' method with an OpenArgs argument, as done in the sfrmHomePlayers
' and sfrmAwayPlayers sub form's Home/Away PlayerID_NotInList
' event procedure. Also sets the new player team name to
' value in main Fixtures form
    Dim strPlayer As String
    Dim lngTeam As Long

    If Not IsNull(Me.OpenArgs) Then
    ' Split OpenArgs data into separate fields
        strPlayer = Left(OpenArgs, InStr(OpenArgs, ";") - 1)
        lngTeam = Mid(OpenArgs, InStr(OpenArgs, ";") + 1)

        Me![strPlayerName] = strPlayer
        Me.lngTeamID = lngTeam

    End If
End Sub

We use the InStr, Left and Mid functions to split the data to pass to the correct fields. We split the data either side of the special character that we used to pass the multiple OpenArgs parameters (;)

The example database, that demonstrates this functionality can be downloaded from the Microsoft Access Forms or Microsoft Access Downloads pages.