By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,222 Members | 1,132 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,222 IT Pros & Developers. It's quick & easy.

Troubles with global variables

RobH
P: 56
I know its probably been asked a million times - however I can't seem to get any examples to work..

In Module1 I have a line: Global gvarMyEmpID As Integer

I then have an opening splash page for the logon process that has a drop down of the employees and an Enter Button

The Enter Button has some code that includes....
gvarMyEmpID = Me!AgentID.Value
SQLtxt = "UPDATE [Version Control] SET [Version Control].[Last Agent] = " & gvarMyEmpID & ";"
DoCmd.RunSQL SQLtxt

I then have the Opening Splash Page close and then the Menu appears - With a text box that has the gvarMyEmpID in the control source however all I get when I run the screen in Form View is "#Name?" in the box.

How can I use this variable in textboxes, Queries - in rowsources, and Event Procedure Code on other pages?
Aug 16 '07 #1
Share this Question
Share on Google+
17 Replies


ADezii
Expert 5K+
P: 8,669
I know its probably been asked a million times - however I can't seem to get any examples to work..

In Module1 I have a line: Global gvarMyEmpID As Integer

I then have an opening splash page for the logon process that has a drop down of the employees and an Enter Button

The Enter Button has some code that includes....
gvarMyEmpID = Me!AgentID.Value
SQLtxt = "UPDATE [Version Control] SET [Version Control].[Last Agent] = " & gvarMyEmpID & ";"
DoCmd.RunSQL SQLtxt

I then have the Opening Splash Page close and then the Menu appears - With a text box that has the gvarMyEmpID in the control source however all I get when I run the screen in Form View is "#Name?" in the box.

How can I use this variable in textboxes, Queries - in rowsources, and Event Procedure Code on other pages?
  1. The Keyword Global is only used for backward compatability - use Public instead:
    Expand|Select|Wrap|Line Numbers
    1. Public gvarMyEmpID As Integer
  2. Public Variables cannot be used as a Control Source for a Control.
  3. The areas in which they can use used are numerous and range from SQL Statements to code blocks, to Property values, etc.
Aug 16 '07 #2

RobH
P: 56
  1. The Keyword Global is only used for backward compatability - use Public instead:
    Expand|Select|Wrap|Line Numbers
    1. Public gvarMyEmpID As Integer
  2. Public Variables cannot be used as a Control Source for a Control.
  3. The areas in which they can use used are numerous and range from SQL Statements to code blocks, to Property values, etc.

I tried that and then on the Menu page entered the following on the "On Load"

Me!Text22.value = gvarMyEmpID

And all i got was a 0 when it should have been a 2 - bing the ID of the staff member I logged in with.

Do I have to call the Module somehow??
Aug 16 '07 #3

FishVal
Expert 2.5K+
P: 2,653
I tried that and then on the Menu page entered the following on the "On Load"

Me!Text22.value = gvarMyEmpID

And all i got was a 0 when it should have been a 2 - bing the ID of the staff member I logged in with.

Do I have to call the Module somehow??
Hi, RobH.

I've tried an example and everything works fine with the following.

In public module "Module1"
Expand|Select|Wrap|Line Numbers
  1. Public intVar As Integer
  2.  
in form module
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Me.Text0 = intVar
  3. End Sub
  4.  
  5. Private Sub Text0_AfterUpdate()
  6.     intVar = Me.Text0
  7. End Sub
  8.  
Aug 16 '07 #4

RobH
P: 56
Hi, RobH.

I've tried an example and everything works fine with the following.

In public module "Module1"
Expand|Select|Wrap|Line Numbers
  1. Public intVar As Integer
  2.  
in form module
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Me.Text0 = intVar
  3. End Sub
  4.  
  5. Private Sub Text0_AfterUpdate()
  6.     intVar = Me.Text0
  7. End Sub
  8.  

Ok I only have 1 module in modules and I have changed to as you have indicated..

In the Logon form 'Logon Button' I have

Expand|Select|Wrap|Line Numbers
  1.         intVar = Me.AgentID
  2.         SQLtxt = "UPDATE [Version Control] SET [Version Control].[Last Agent] = " & intVar & ";"
  3.         DoCmd.RunSQL SQLtxt
  4.  
Now this does update the Vesion Control tbl correctly.. so the variable is working with-in the logon form..

In the Menu Form I have a text box which should display the ID no..
Expand|Select|Wrap|Line Numbers
  1.   Private Sub Form_Current()
  2.     Me.Text22 = intVar
  3.   End Sub
  4.  
I have tried on this form both 'Private Sub Form_Load()' and 'Private Sub Text0_AfterUpdate()'
Aug 17 '07 #5

ADezii
Expert 5K+
P: 8,669
Ok I only have 1 module in modules and I have changed to as you have indicated..

In the Logon form 'Logon Button' I have

Expand|Select|Wrap|Line Numbers
  1.         intVar = Me.AgentID
  2.         SQLtxt = "UPDATE [Version Control] SET [Version Control].[Last Agent] = " & intVar & ";"
  3.         DoCmd.RunSQL SQLtxt
  4.  
Now this does update the Vesion Control tbl correctly.. so the variable is working with-in the logon form..

In the Menu Form I have a text box which should display the ID no..
Expand|Select|Wrap|Line Numbers
  1.   Private Sub Form_Current()
  2.     Me.Text22 = intVar
  3.   End Sub
  4.  
I have tried on this form both 'Private Sub Form_Load()' and 'Private Sub Text0_AfterUpdate()'
It seems that you are experiencing some very peculiar behavior with your Public Variable. Try changing the Declaration to:
Expand|Select|Wrap|Line Numbers
  1. Public intVar As Variant
NOTE: Let me know how you make out.
Aug 17 '07 #6

RobH
P: 56
Actually - I found the problem ...

I had further down in the coding another IF statement - That was allocating the variable properly..

It was while I was exporting the required modules to a seperate database so i could attach it that i can accross it.

Thanks Guys.

Hey If I want to store the Value of a Yes/No in a variable should I use Variant or String?

Eg I have tbl-UserSecurity which has a field "Admin" being yes/no format.

If I want to Dlookup that value into a public variable so that i can use it for IF statements later. What 'As' should I use in the Public intAdmin as ?????

Thanks Again - already this has the potential to cut so much text from coding and lead off the database from doing these lookup's all of the time..

Rob.
Aug 17 '07 #7

ADezii
Expert 5K+
P: 8,669
Actually - I found the problem ...

I had further down in the coding another IF statement - That was allocating the variable properly..

It was while I was exporting the required modules to a seperate database so i could attach it that i can accross it.

Thanks Guys.

Hey If I want to store the Value of a Yes/No in a variable should I use Variant or String?

Eg I have tbl-UserSecurity which has a field "Admin" being yes/no format.

If I want to Dlookup that value into a public variable so that i can use it for IF statements later. What 'As' should I use in the Public intAdmin as ?????

Thanks Again - already this has the potential to cut so much text from coding and lead off the database from doing these lookup's all of the time..

Rob.
Hey If I want to store the Value of a Yes/No in a variable should I use Variant or String?
The answer is neither. The value would be stored in a Boolean Type Variable, as in:

Expand|Select|Wrap|Line Numbers
  1. 'Public/Global Declaration
  2. Public blnAdmin As Boolean
Expand|Select|Wrap|Line Numbers
  1. 'Initialization
  2. blnAdmin = True
Expand|Select|Wrap|Line Numbers
  1. 'Evaluates to True and 'blnAdmin = True' appears in the Message Box
  2. If blnAdmin Then
  3.   MsgBox "blnAdmin = True"
  4. Else
  5.   MsgBox "blnAdmin = False"
  6. End If
  7.  
Aug 17 '07 #8

RobH
P: 56
The answer is neither. The value would be stored in a Boolean Type Variable, as in:

Expand|Select|Wrap|Line Numbers
  1. 'Public/Global Declaration
  2. Public blnAdmin As Boolean
Expand|Select|Wrap|Line Numbers
  1. 'Initialization
  2. blnAdmin = True
Expand|Select|Wrap|Line Numbers
  1. 'Evaluates to True and 'blnAdmin = True' appears in the Message Box
  2. If blnAdmin Then
  3.   MsgBox "blnAdmin = True"
  4. Else
  5.   MsgBox "blnAdmin = False"
  6. End If
  7.  


If I want to store the colour for Fore Colour ie #887433 so that i can use it to effectively make a template for colours across the app would I use String?

I've tried
Expand|Select|Wrap|Line Numbers
  1.  
  2. Public intBlue as String 
  3.  
then in the form On Open

Expand|Select|Wrap|Line Numbers
  1.     Me!Label1.ForeColor = intBlue
  2.  
But I get a "Type Mismatch' error when the page loads.
Aug 17 '07 #9

ADezii
Expert 5K+
P: 8,669
If I want to store the colour for Fore Colour ie #887433 so that i can use it to effectively make a template for colours across the app would I use String?

I've tried
Expand|Select|Wrap|Line Numbers
  1.  
  2. Public intBlue as String 
  3.  
then in the form On Open

Expand|Select|Wrap|Line Numbers
  1.     Me!Label1.ForeColor = intBlue
  2.  
But I get a "Type Mismatch' error when the page loads.
Color values as you indicate would have to be LONG Integers - you cannot assign a String Value to a Control's ForeColor Property.
Aug 17 '07 #10

RobH
P: 56
Color values as you indicate would have to be LONG Integers - you cannot assign a String Value to a Control's ForeColor Property.

So how can one find out what can be assigned where?

eg What can be assigned to Source Doc?
Aug 18 '07 #11

ADezii
Expert 5K+
P: 8,669
So how can one find out what can be assigned where?

eg What can be assigned to Source Doc?
  1. A String would be assigned to Source Doc.
  2. If you are not sure about the Variable Type assignments, check the Help Files. You will typically see information such as: Read/write String, returns an Integer, etc.
Aug 18 '07 #12

RobH
P: 56
  1. A String would be assigned to Source Doc.
  2. If you are not sure about the Variable Type assignments, check the Help Files. You will typically see information such as: Read/write String, returns an Integer, etc.

Is there a simple way of setting up a Template at all - instead of having to copy the same text to all of the On Open in every form?

Sorry these are probably stupid questions.
Aug 18 '07 #13

FishVal
Expert 2.5K+
P: 2,653
So how can one find out what can be assigned where?

eg What can be assigned to Source Doc?
I recommend you to use object browser to view object properties/methods syntax. This also helps to understand object model better.
Aug 18 '07 #14

ADezii
Expert 5K+
P: 8,669
I recommend you to use object browser to view object properties/methods syntax. This also helps to understand object model better.
Good suggestion, FishVal. The only problem is that the Object Browser can be very intimidating and confusing to a Newbie.
Aug 18 '07 #15

FishVal
Expert 2.5K+
P: 2,653
Good suggestion, FishVal. The only problem is that the Object Browser can be very intimidating and confusing to a Newbie.
:) Not sure. For me it was very helpful from the very start.
Despite I had been rather experienced programmer when started designing db's in Access, I was complete Null in OOP. Viewing object model in Object browser gave me a sense of OOP.
Aug 18 '07 #16

ADezii
Expert 5K+
P: 8,669
:) Not sure. For me it was very helpful from the very start.
Despite I had been rather experienced programmer when started designing db's in Access, I was complete Null in OOP. Viewing object model in Object browser gave me a sense of OOP.
Maybe you do have the right approach, afterall (LOL).
Aug 18 '07 #17

FishVal
Expert 2.5K+
P: 2,653
Maybe you do have the right approach, afterall (LOL).
Oh, maybe.
To have an approach is better than to have right one. Bkz to have a right approach means to have no approach at all. LOL.
Aug 18 '07 #18

Post your reply

Sign in to post your reply or Sign up for a free account.