Using MS Access Forms VBA: Passing Values Using OpenArgs
Just getting to grips with Access Forms VBA? A very popular keyword is the DoCmd object and this command calls one of several actions within the database application including how to open a form using the sub keyword OpenForm.
For example, having a command button on one form to open another form called frm_RecipientForm would look like:
On closer inspection to the above example VBA code, you will find more optional parameters (also known as arguments) that handle additional actions and workflows and to see the full syntax (which just means structure) of the OpenForm keyword will reveal how flexible VBA can be:
DoCmd.OpenForm FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs
Notice each argument is separated by a comma which after the first argument are all optional.
MS Access Forms VBA: How To Use The Argument OpenArgs
Take a look at the last argument parameter called OpenArgs which is a neat little trick to pass a value (or an array of values) from one form to another within the calling procedure.
If I’m only interested in using the first argument and the last one missing out all arguments in between, you simply repeat a comma separator to skip an argument. Therefore, to open the above form again and pass the value “WOW!” would look like:
DoCmd.OpenForm "frm_RecipientForm", , , , , , "WOW!"
This is only half of the equation and in order to successfully see the fruits of your labour, you will need to utilise the passing value in the receiving form by calling the OpenArgs parameter in the Access form VBA too at the time of loading (event) to this form. For example:
Me.txt_Value = Me.OpenArgs
Where the Me represents itself (the opening form) and in this case a TextBox called txt_Value receiving the value “WOW!” from the OpenArgs parameter.
Now take a look at the illustration below to see a very simplistic example of passing a value (my name) between forms.
As you can see when I type my name into the form (on the left) and click the command button ‘Open Recipient Form…’ the procedure cmd_OpenForm_Click (event) is executed calling the second Access form VBA (to the right) calling it’s Form_Load event and populating my name in the TextBox shown.
There are other optional arguments that were not used in this simple example and I’ll leave that for you to explore in your own time (perhaps using Microsoft’s database help files).
Finally, consider a macro instead as with later versions of Microsoft Access, embedded macros are now the default procedure when automating your database application.
If you need any help or have questions, please contact me and I will reply within hours. Why not add comments below and share your experiences with others.