Knowledge.ToString()

MS Access: Pass Variable Value from One Form to Another Form

Few days back, I was creating a small prototype for my project using MS Access. It required to pass values from one form to the other form. I searched online to find out away 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)

Problem with this simple method is we can pass only one value at a time. If we want to pass multiple 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.

Download MS Access Example

Share

Comments

8 responses to “MS Access: Pass Variable Value from One Form to Another Form”

  1. Derek Wallace Avatar
    Derek Wallace

    Hello
    In my first form I put the code.
    DoCmd.OpenForm “frm_Screen_2”,,,, “Caller_ID = ” & ID.Value
    In my second form in the On Load ….
    Caller_ID.value = Forms!frm_Screen_1!Caller_ID.Value
    Caller_ID.setfocus
    It worked !

  2. Sampath Avatar
    Sampath

    Hai.,
    It worked super,
    fantastic.
    but explain some deeply.

    Thanks

  3. Sampath Avatar
    Sampath

    Hai.,
    It worked super,
    fantastic.

    Thanks

  4. Ravikumar Avatar
    Ravikumar

    Ok. I’m not as good as the rest. I have been working with the above. I imported the forms into my project and created a “utility” module for the public function. The issue is that when I submit the variable, it continues to pass a previous string and not the new entry. is there a way to flush any previous variables first? Am i doing something to cause this?Also, my end goal is to have just a cmd button which will pass the values of displayed record (uneditable view form) to a editable view form when the button is pressed opening the editable form with the record just viewed (form 1).thoughts?

  5. Tom Avatar
    Tom

    Ok. I’m not as good as the rest. I have been working with the above. I imported the forms into my project and created a “utility” module for the public function. The issue is that when I submit the variable, it continues to pass a previous string and not the new entry. is there a way to flush any previous variables first? Am i doing something to cause this?

    Also, my end goal is to have just a cmd button which will pass the values of displayed record (uneditable view form) to a editable view form when the button is pressed opening the editable form with the record just viewed (form 1).

    thoughts?

  6. Vasanth Avatar
    Vasanth

    Very useful for me.
    It worked Good.

  7. jainomathew Avatar
    jainomathew

    please explain deeply

  8. Seema Avatar
    Seema

    Thanks!
    I am just beginner and I exactly had the same problem.

    It worked very well!!

Leave a Reply

Your email address will not be published. Required fields are marked *