Solved

How do you pas a variable between forms in excel VB

Posted on 2004-08-30
16
134 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 49

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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

929 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now