Passing Multiple Values via the OpenArgs Parameter of DoCmd.OpenForm

The DoCmd.OpenForm command is a useful tool for the Access for Office 365 database developer wanting to control the manner in which a form opens during runtime.  I have written about it previously in my post on how to "Control Form Opening".  However, in today's post, we are going to focus on a specific area of this command's use - that is, how we can pass multiple values via its OpenArgs property.

The OpenArgs parameter of DoCmd.OpenForm gives us the means to pass a single string value to the OpenArgs property of new form being opened.  After the form has opened, this value can then be read and used by any code able to reference the new forms OpenArgs property. As things stand, the limitation of this is that we can only pass one string value as the command executes.  So let's see how we can get around this, and pass any number of strings values in one go.

To demonstrate how this works, I have created an Access for Office 365 form containing three text boxes and a submit button.  When the user populates the text boxes and clicks SUBMIT during runtime, the values contained in each of the three text boxes are passed over to a new form by means of the OpenArgs parameter of the DoCmd.OpenForm command.

Above: Form to pass multiple values via the OpenArgs parameter of DoCmd.OpenForm.

You can download the full OpenArgsPost.accdb database used in the this exercise from here. 

(NB I have tested the download process using the Chrome browser which works OK.  The download process may fail using other browsers such as Microsoft Edge. If you experience issues please try using a different browser). 

---

Part One: Passing the Values

Let's look at what happens behind the scenes when the SUBMIT button is clicked.  First of all, clicking SUBMIT (referred to as btnSubmit in DESIGN VIEW) results in the control button's ON CLICK event firing.  This, in turn, starts the following VBA event procedure (see screenshot below):

Above: The VBA event procedure for btnSubmit.

The code begins by declaring a string variable called strOpenArgs:

Dim strOpenArgs AS String

The next two lines of code reference the value entered by the user in the first text box.  The value is stored in the strOpenArgs string:

txtValue1.SetFocus
strOpenArgs = txtValue1.Text

The next two lines of code after that reference the value of the second text box, and append it to the strOpenArgs string preceded by a semi-colon (the significance of the semi-colon will be explained in part 3 of this post):

txtValue2.SetFocus
strOpenArgs = strOpenArgs + ";" + txtValue2.Text

This is then repeated for the third text box:

txtValue3.SetFocus
strOpenArgs = strOpenArgs + ";" + txtValue3.Text

So at this point in the program flow, the value of strOpenArgs is as follows:

"one;two;three"

This is the final value of the DoCmd.OpenForm OpenArgs parameter which will be passed to the new form in the following line of code:

DoCmd.OpenForm "frmReceiveOpenArgs", , , , , , strOpenArgs

---

Part Two: Retrieving the Values

Let's take a look at the new form which opens after the SUBMIT button has been clicked, and the event procedure explained above has finished executing:

Above: the new form which opens in response to clicking the SUBMIT button.

As you can see in the screenshot above, the new form consists of three empty text boxes (for displaying retrieved values) and a RETRIEVE button.  When the user clicks RETRIEVE the text boxes will be populated with the values submitted from the first form.  In our case, Retrieved Value 1 will display the value "one", Retrieved Value 2 will display the value "two", and Retrieved Value 3 will display the value "three".

Let's look at what happens behind the scenes after the RETRIEVE button is clicked.  Like the first form, clicking the command button, which in this form is called btnRetrieve, results in it's ON CLICK event firing.  This starts the following VBA event procedure (see screenshot below):

Above: The VBA event procedure for btnRetrieve.

The code begins by declaring a string variable called strOpenArgs before referencing the forms OpenArgs property and storing its value in the string.  (Just to recap, the value of the OpenArgs property (ie "one;two;three") was determined by the OpenArgs parameter of the  DoCmd.OpenForm command invoked when the user clicked the SUBMIT button on the previous form):

dim strOpenArgs As String
strOpenArgs = Me.OpenArgs

The next couple of lines of code are very important. A string array called strValues is declared and has its elements populated with data returned from a function called getOpenArgsArray.  This function is located in a separate module called Utilities which is global in scope.  This is where the core of the processing is done for this exercise:

Dim strValues() As String
strValues = getOpenArgsArray(strOpenArgs)

The purpose of the getOpenArgsArray is to split the value of its parameter, strOpenArgs (which in our case is "one;two;three"), into three separate strings, with each array element containing an individual string.  So for us, the strValues array is populated with the following values:

Array Element	Value
strValues(0)	"one"
strValues(1)	"two"
strValues(2)	"three"

These values are assigned to each of the three text boxes in the next six lines of code:

Me!txtRetValue1.SetFocus

Me!txtRetValue1.Text = strValues(0)

Me!txtRetValue2.SetFocus
Me!txtRetValue2.Text = strValues(1)

Me!txtRetValue3.SetFocus
Me!txtRetValue3.Text = strValues(2)

The end result can be seen in the screenshot below:

Above: the end result after the user clicks RETRIEVE.

In this way, we have used the DoCmd.OpenForm OpenArgs parameter to pass multiple values to a new form, thereby overcoming the limitation of only being able to pass a single string within the statement.

---

Part Three: The getOpenArgsArray Function

However, this post would not be complete without an explanation of the getOpenArgsArray function, as this is where the core of the processing was done for this exercise.  Let's take a look at the code used in the getOpenArgsArray function:

Above: code for the getOpenArgsArray function.
NB: in this screenshot the strArgs array is declared with 7 elements.
This is because the function is global in scope and other event procedures using
 the function may need addtional elements.  This means that the OpenArgs
 parameter of DoCmd.OpenForm can pass up to 7 values with this current setup.
If more elements are needed, the array can be declared with an adjusted number as necessary.

As mentioned in section 2 above, the purpose of the getOpenArgsArray function is to split the value of the strOpenArgs parameter passed to the function, into individual sections, and to save each section as a separate array element. So in our case, the function receives a string containing the value "one;two;three", and returns an array with three elements ie 

Array Element	Value
Element 1	"one"
Element 2	"two"
Element 3	"three"

This is acheived by iterating through the strOpenArgs string in a FOR NEXT loop and appending each individual character to a temporary string called strBuildString.  Then when the current character of strOpenArgs in the loop is one of the semi-colons, the value of strBuildString is saved to a new array element. 

Lets break this down further.

The following three lines of code are concerned with iterating through each  character contained in the strOpenArgs string.  Each character is temporarily stored in a string variable called strChr:

For i = 1 To Len(strOpenArgs)
    Dim strChr As String
    strChr = Mid(strOpenArgs, i, 1)

The value of strChr is then checked to see whether or not it is a semi-colon.  If it is NOT a semi-colon, the character is appended to the strBuildString variable :

    If Not strChr = ";" Then
          strBuildString = strBuildString + strChr

If, on the other hand, the character IS a semi-colon, the current value of strBuildString is saved as a  new array element in strArgs().  The strBuildString variable is then cleared ready to build the value of the next array element:

         Else    
             strArgs(argCounter) = strBuildString

             strBuildString = ""

The element number of the array is kept track of in an integer variable called argCounter.  This is incremented by one after the strBuildString variable is cleared:

              argCounter = argCounter + 1
     End If

Next i

After the last iteration of strOpenArgs has completed, the final array element is saved, and the return value of the getOpenArgsArray function is set to the value of the strArgs() array:

strArgs(argCounter) = strBuildString
getOpenArgsArray = strArgs
Exit Function

Evaluating Conditions with the If...Then...Else Statement - Learning Access VBA. Tutorial 2.

This the second post in the Learning Access VBA series.  Last week I introduced you to the VBA Development Environment where we we also learnt a little about variables.  We completed a simple exercise which referenced the values entered by the user into two text boxes, stored the values in variables, and added the values contained therein together. The result was then displayed in a third text box.  This week we are going to move on, and look at how VBA deals with Conditions using the If ... Then ... Else Statement.

So what do we mean by the term conditions or the Conditional?  Basically as our VBA code is executed (that is to say, as the code runs, line by line, processing each statement in turn) the program flow may well reach various points where it needs to branch according to whether a given condition has or has not been met.   For example, we may want our code to run a short block of nested code if the value of a specified variable is greater than a given number.  If the condition is met, the program flow runs the statement contained in the nested code below; whereas, if the condition is not met, the program flow may branch to the next statement after the nested code - the nested code is, in effect, skipped.

Lets take a look at how the If ... Then ... Else Statement is constructed:

IF conditon is met THEN

        nested code1

ELSE

        nested code2

END IF
 
The IF keyword tests whether the condition is true or not.  If it is true, the THEN keyword directs the program flow to the code contained in nested code1.  ELSE is optional. If we use it, ELSE directs the program flow to that contained in nested code2 if the condition after the IF keyword had not been met.  Had we not used the ELSE keyword (with nested code2), the program flow would have skipped nested code1 and gone straight to the END IF keyword.

So, for example, our code may read: 

IF varFirstNumber > varSecondNumber THEN

        MsgBox("First Number is Greater than Second Number")

ELSE

        MsgBox("First Number is not Greater than Second Number")

END IF

This tests whether the number contained in variable varFirstNumber is greater than that contained in varSecondNumber.  If the condition was met, the nested code below the IF keyword would run.  This displays a message box saying "First Number is Greater than Second Number".  If the condition was not met, the nested code below the ELSE statement would have run instead.  This would have displayed a message box saying "First Number is Not Greater than Second Number".  The END IF keyword closes the If ... Then ... Else Statement.


There is one other optional keyword which is well worth mentioning - that is ELSEIF.  When we only use IF/THEN and ELSE, we have just two possible branches in the program code.  However, suppose there are three or more possible conditions?  For example, suppose we want to test whether the variable varFirstNumber is (a) greater than, (b) less than, or (c) equal to, the second variable?  ELSEIF is a good way to do this.  Lets see how we would code this scenario:

IF varFirstNumber > varSecondNumber THEN

        msgbox("First Number is Greater than Second Number")

ELSEIF varFirstNumber < varSecondNumber THEN

        msgbox("First Number is Less than Second Number")
ELSE
        msgbox("First Number is Equal to Second Number")

END IF

As we can see, the ELSEIF keyword has allowed us to enter a second specified condition to test, in the event of the initial condition proving false.  


As such, when the program flow reaches the If ... Then ... Else Statement, the initial condition is tested by the first IF keyword; if this is true, the nested code in the line directly below is executed, and then the program flow branches to the END IF keyword at the bottom of the statement.  However, if the first condition had been false, the first block of nested code is skipped, and the program flow goes to the ELSEIF keyword and the second conditional expression is then tested; if this proves true, the second block of nested code is executed.  However, if the 2nd expression had also proven false, then the 2nd nested code would also be skipped, and the program flow would move down to the ELSE keyword.  There is no condition to test here, so the 3rd block of nested code is executed unconditionally.


(NB In the above scenario there were three possible outcomes.  However, it is also worth mentioning that by adding additional ELSEIF keywords into the statement, the number of possible conditions to test is potentially endless.)


Compare Numbers Exercise


Let's end this tutorial by applying what we have learnt above to the following exercise.    

We are going to create a form with two text boxes named txtFirstNumber and txtSecondNumber.  Then we will add a command button, ctlCompare, with some code attached to it's ON CLICK event.  We learnt how to do this in last weeks Learning Access VBA tutorial. However this time our code will compare the numbers entered into the two text boxes by the user at runtime, and display a message box informing the him/her whether the First Number is (a) Greater than, (b) Less than, or (c) Equal to, the Second.

This is a screenshot of our form in action:

Here the user has entered 10 in txtFirstNumber and 20 in txtSecondNumber.
After the user clicks the Compare command button, Access runs the VBA code
that compares the two numbers and displays the appropriate message.

Instructions

I won't go into great detail regarding instructions - we should have leant all the required skills from last weeks post on Learning Access VBA.  There is also more information about creating an unbound Access Form in the post about Creating an Access Form from Scratch.  I advise you to check out these posts if you need a reminder:

1. Create a new unbound Access Form.
2. Add two text boxes called txtFirstNumber and txtSecondNumber.
3. Add a Command Button called ctlCompare.
4. Select the Command Button and open the PROPERTIES SHEET.
5. Click the Events tab of the PROPERTY SHEET.
6. Click the three dots symbol  (...) on the right of the ON CLICK property cell.
7. Open the VBA Editor by clicking CODE BUILDER from the CHOOSE BUILDER menu.
8. Copy and paste the code listed below (excluding the first and last line which should have been added to the VBA editor automatically).
9. Click the save Icon and close the VBA Editor.

Private Sub ctlQuestion_Click()

    Dim varFirstNumber As Integer

    Dim varSecondNumber As Integer

    

    varFirstNumber = Me!txtFirstNumber

    varSecondNumber = Me!txtSecondNumber

    

    If varFirstNumber > varSecondNumber Then

        MsgBox "First Number is Greater than Second Number"

    ElseIf varFirstNumber < varSecondNumber Then

        MsgBox "First Number is Less than Second Number"

    Else

        MsgBox "First Number is Equal to Second Number"

    End If

    

End Sub

To test out the code, open your form in FORM VIEW, enter a number in each of the text boxes, and click the COMPARE button.  NB for clarity there is no code to validate whether two numbers have been entered in the text boxes.  An error message will result if one or both of the text boxes is empty when the compare button is clicked.

The code begins by declaring two variables, and then assigns values to each one by referencing the two respective text boxes (see Learning Access VBA for more information about this process).  The If ... Then ... Else statement then tests whether the first number is greater than, less than or equal to, the second number.  A message box opens containing the result.