Few days back, I was creating a small prototype for my project using MS Access. It was requiring to pass values to other form. I searched for finding out the way to pass values but couldn’t succeed.
The only hint I got is of using Me.OpenArgs argument variable to pass the value across the forms.
To pass value “2” from a form frmOne to frmTwo, we are using code like
DoCmd.OpenForm "frmTwo", acNormal, , , , acWindowNormal, "2"
In the PageLoad event of the form frmTwo, we can get this value using following code
Dim i as Integer = CInt(Me.OpenArgs)
Problems with this simple method is we can pass only one value at a time. If we want to pass more values, we must have some kind of delimiter and make a string of all values. but it will require the arguments to be passed in a specific order.
I wrote a small function which will extract the values from the passed value string without requiring the values to be passed in a particular order.
This function requires that we have to pass values in a format like “Var1=Val1:Var2=Val2”. Here each value passed will have a corresponding variable name and Variable name/Value pair will be delimited by “:”. The function will split the argument by the delimiter and extract the value for a particular variable. The code of the function is shown 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 value “2” from form frmOne to frmTwo we need to write code
DoCmd.OpenForm "frmTwo", acNormal, , , , acWindowNormal, "Count=2"
Now in the form frmTwo we can get the value in FormLoad event
Dim i as Integer = CInt(GetTagFromArg(Me.OpenArgs, "Count" ))
Using this trick, we can pass as many value as possible and the order of the values is not important.
This is extremely simple method struck in my mind at that time. If you have any better solution, please let me know.