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.
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:
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:
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:
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 (;)