MS Access: Pass Variable Values From One Form to Another

• Download demo project - 15.2 Kb

Introduction

A few days back, I was creating a small prototype for my project using MS Access. I was required to pass values to other forms. I searched everywhere to find out a way to pass values but couldn't succeed. The only hint I got was using Me.OpenArgs argument variable it is possible to pass values across forms.

Example of passing a single variable using the Me.OpenArgs argument

To pass the value "2" from a form frmOne to frmTwo, we use the following code:

DoCmd.OpenForm "frmTwo", acNormal, , , , acWindowNormal, "2"

In the PageLoad event of frmTwo, we can get this value using the following code:

Dim i as Integer
i = CInt(Me.OpenArgs)

The problem with this simple method is that we can pass only one value at a time. If we want to pass more values, we must have some kind of a delimiter and make a string of all values, but it requires the arguments to be passed in a specific order.

Solution

I wrote a small function that extracts values from the passed value string without requiring the values to be passed in a particular order.

This function requires that we pass values in a format like "Var1=Val1:Var2=Val2". Here each value passed will have a corresponding variable name and the variable name/value pair will be delimited by ":". The function will split the argument using the delimiter and extract the value of a particular variable. The code for the function is given below:

Public Function GetTagFromArg(ByVal OpenArgs As String, _
                               ByVal Tag As String) As String
    Dim strArgument() As String
    strArgument = Split(OpenArgs, ":")
    Dim i As Integer
    For i = 0 To UBound(strArgument)
     If InStr(strArgument(i), Tag) And _
                 InStr(strArgument(i), "=") > 0 Then
       GetTagFromArg = Mid$(strArgument(i), _
                        InStr(strArgument(i), "=") + 1)
       Exit Function
     End If
   Next
   GetTagFromArg = ""
End Function

Now to pass the value "2" from form frmOne to frmTwo we need to write the following code:

DoCmd.OpenForm "frmTwo", acNormal, , , , acWindowNormal, "Count=2"

and in frmTwo we can get the value in the FormLoad event:

Dim i as Integer
i = CInt(GetTagFromArg(Me.OpenArgs, "Count" ))

Using this trick, we can pass any number of values and the order of the values is not important.

Conclusion

This is an extremely simple method that came to my mind at that time. If you have any better solution, please let me know.