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

Introduction to VBA Loops - Learning Access VBA Tutorial 3

This is the third tutorial in the Learning Access VBA series. In the first tutorial we learnt how to create a simple VBA Sub Procedure and set up up Variables; and in the second, we looked at Evaluating Conditions with the IF THEN ELSE statement.  Today we are going to move on to VBA Loops.

Loops are are common feature of most programming languages, and VBA is no exception.  Loops enable lines within in a defined section of code to be repeated as many times as is necessary, until a specific point is reached when the program flow may move on to the next section of code.  There are different kinds of loops in VBA.  The FOR ... NEXT loop, for example, repeats the loop a set number of times.  We would use  this if we wanted the loop to repeat, say, 10 times, and then move on.  With the DO ... UNTIL loop, on the other hand, the defined section of code would repeat indefinitely, until a specified condition arises.  This may be something to use if you want to repeat the loop until a user enters a specific value into an input box.


The FOR ... NEXT Loop

Let take a look at how the FOR ... NEXT loop is constructed.

For I = 1 To 10
    lines of code to be repeated
Next I

The FOR NEXT loop has a counter which is referred to above as I.  This is basically a variable name - we could have called it a different name if we had wanted.  The first line of the statement (For I = 1 to 10) sets the counter at 1, and tells the procedure to continue repeating the lines of code until the counter reaches 10.  Each time the program flow executes the NEXT keyword at the end of the statement, the counter is increased by 1.  As it does so, it also checks whether the counter has reached 10 (or whatever level it had been set).  If it has not, the lines of code repeat another time until the Next keyword executes again, increasing the count by another 1.  Once it reaches the level set, the program flow continues past the NEXT keyword into the next section of code.

So let's try an exercise to see how the FOR ... NEXT loop works in practice.

1. Create a new unbound form in Design View.
2. Add a Command Button.
3. Add the VBA code (listed below) to the button's OnClick Event (please see the first Learning VBA Tutorial if you need help with this, or any of the previous steps):
4. Click the SAVE icon.

Dim I As Integer

Dim x As Integer    

Stop

For I = 1 To 10    

    Randomize

    x = (9 * Rnd() + 1)

    Debug.Print "Counter = " &  I  & ";  Random Number = " &  x        

Next I

Before we try out the code, we are going to open the VBA Editor's Immediate Window.  To do this,  click the VBA VIEW menu, and then click IMMEDIATE WINDOW from the menu list.  When we run the code, the STOP statement will re-open the VBA editor during runtime, to allow us to Step Into each

line of code as it executes.  Then when the program flow reaches the Debug.Print line, the Counter value and the value of variable x (a random number generated by the code) is displayed in the immediate window.

When you are ready, open the Access form and click the command button we created earlier.  The VBA editor will then open.  The current line being executed is highlighted in yellow.  To advance to the next line we click Step Into from the DEBUG menu (alternative use the F8 key which is far more convenient).  Then watch what happens as the program flow progresses through the loop.  Also take note of the counter value which is displayed before the random number in the immediate window.

Figure 1: The VBA Editor with Immediate Window.  The editor was opened during
run time due to the STOP command which acted as a breakpoint.
Another way of adding a break point is to click on a line
and then select TOGGLE BREAKPOINT from the DEBUG menu.

The DO ... UNTIL Loop

Let's take a quick look at the how the DO ... UNTIL loop is constructed.

Do Until variableName = thisValue

    lines of code to be repeated

Loop

The first line of the DO ... UNTIL loop is testing whether the specified condition is true or not.  If it is not true, the lines of code within the loop are executed until the LOOP keyword is reached at the end of the statement. Then the program flow returns to the first line of the statement where the condition is tested again.  If the lines of code within the loop had changed the value of the variable being tested so the condition becomes true, the program flow exits the loop at this top line.

Here is an example of a DO ... UNTIL Loop that you may  like to try for yourself.  Just change the code from the first exercise so that this runs in its place.

Dim varNumber As Integer
Dim varResponse As Integer
    
Randomize
varNumber = 9 * Rnd() + 1


Do Until varResponse = varNumber
        
    varResponse = InputBox("Enter a Number Between 1 and 10", "Guess A Number")
        
    If varResponse > varNumber Then
        MsgBox "Your Guess is too High"
    ElseIf varResponse < varNumber Then
        MsgBox "Your Guess is too Low"
    Else
        MsgBox "Congratulations: Your Guess is Correct."
    End If
        
Loop
    
    MsgBox "Game Over!!!"

(NB for the sake of clarity, this code does not deal with the possibility that the user may click cancel or enter an empty value in the input box.  If this happens an error results, and the procedure crashes!)

This code is for a simple Guess the Number Game.  The user clicks the command button to begin.  The code then generate a random number and stores it in a variable called varNumber.  Once the program flow gets to the loop, the variable varResponse is tested to determine whether it is equal to the random number stored in varNumber.  If it is not, the lines of code within the loop are executed.  The user is asked to guess the number in a pop up input box.  The code then enters an IF ... THEN ... ELSE statement where the user is informed whether his guess is too high, too low, or is indeed correct.  When the LOOP keyword is reached, the program flow returns to the DO UNTIL keyword where the value of the guess is tested against the random number to determine whether the lines of code should be repeated, or whether the program flow can continue past the LOOP keyword.

When you try this code, please feel free to enter a BREAKPOINT so you can follow the program flow for yourself.  You can do this by entering the STOP statement as we did in the first exercise.  Alternatively, click the line where you want to insert the breakpoint, and then click TOOGLE BREAKPOINT from the DEBUG menu.  The line is then highlighted in red., but the action is the same.