Solved

How do you pas a variable between forms in excel VB

Posted on 2004-08-30
16
136 Views
Last Modified: 2010-05-02
I am trying to pass the value of a variable betweens two forms in excel's vb. How do you do this?
Thanks
whiwex
0
Comment
Question by:whiwex
  • 8
  • 7
16 Comments
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 11938634
Try create a Module, then declare your variables As Public there.. like:

In the module:

Public myVar As String
...
0
 
LVL 10

Expert Comment

by:anv
ID: 11938679
while u open a form in VBA..
u have one parameter in the docmd.openform frmname command..
where u can pass opening parameters... there u can pass the variable and in the other form retrieve the value of the parameter using openArgs method of that form...

e.g.

'from form1 one i want to pass the current id to form2
docmd.openform form2,,idVal

'in form2
dim id1 as string

id1=form2.openArgs
0
 

Author Comment

by:whiwex
ID: 11938750
it doesn't like the openArgs
whiwex
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:whiwex
ID: 11938768
Here's what I have
Userform1
docmd.openform UserForm2, , row
UserForm2.Show

Userform2
row1 = UserForm1.openArgs
0
 
LVL 10

Expert Comment

by:anv
ID: 11938787
why r u using the statement UserForm2.Show

the statement docmd.openform UserForm2, , row is enough.

to open the form
0
 

Author Comment

by:whiwex
ID: 11938829
I removed the userform2 show.  It still dies on the arg. It's to a compile error: method or data member not found.
0
 
LVL 10

Expert Comment

by:anv
ID: 11938858
use following

docmd.openform UserForm2, , row
UserForm2.Show

Userform2
row1 = UserForm2.openArgs
0
 

Author Comment

by:whiwex
ID: 11938879
it still errors on the openarg. It doesn't knowmthis command.
whiwex
0
 
LVL 10

Expert Comment

by:anv
ID: 11938915
here's the correct  syntax

'stDocName stores the name of the form to open
DoCmd.OpenForm stDocName, , , , , , "openArgs"

in form load event of the other form use

row1=form2.openargs

hope this time it will work
u r working in VBA right?
0
 

Author Comment

by:whiwex
ID: 11938943
it doesn'tlike the stdocname command.

i am writing a macro in excel. I have the excel visual basic editor opened and I am adding this code to the two forms I created. both forms work except for trying to pass value of row from one form to the other.
whiwex
 
0
 

Author Comment

by:whiwex
ID: 11938950
in form1 it looks like this.

DoCmd.OpenForm stuserform2, , , , , , "openArgs"
UserForm2.Show


inform two it looks like this

Public Sub UserForm_Initialize()
Dim row1 As Integer
row1 = UserForm2.Openargs
UserForm2.TextBox6 = Date
UserForm2.TextBox7 = Time
End Sub

thanks
0
 
LVL 10

Expert Comment

by:anv
ID: 11938976
whiwex
stDocName is not a command..
its just a variable that stores the form name to open..
read clearly my last comment..

0
 

Author Comment

by:whiwex
ID: 11939022
in form1 it looks like this.

DoCmd.OpenForm tuserform2, , , , , , "openArgs"
UserForm2.Show


inform two it looks like this

Public Sub UserForm_Initialize()
Dim row1 As Integer
row1 = UserForm2.Openargs
UserForm2.TextBox6 = Date
UserForm2.TextBox7 = Time
End Sub

it errors on the openargs. the error says method or data member not found
whiwex

thanks
0
 
LVL 10

Expert Comment

by:anv
ID: 11939554
whiwex

in this case, now, the best method would be to declare a global variable in a module..
from UserForm1 store the value in that variable

and from UserForm2 fetch its value on form load or initialize..
0
 

Author Comment

by:whiwex
ID: 11941246
thats what I have been trying to do. i can't get it to work.
how do you do this?
what are the commands because everything i try fails to pass the variable
whiwex

0
 
LVL 10

Accepted Solution

by:
anv earned 250 total points
ID: 11948951
open a module, if one exists, else add a new module
in the module declare a varaible as public,
say if i want name that variable as glbParam then in the module declare it as

public glbParam as string (or whatever datatype u want to provide)

in form1 before u open form2 add a statement to pass the value to this varaible like
glbParam="Something"
userform2.show

now in userform2 u can use that variable.

its a good practice if u provide option explicit statement in all ur modules or forms..
do this and u'll get the desired result..
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question