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.