?
Solved

Use of global veriable in access 2003

Posted on 2012-08-28
23
Medium Priority
?
613 Views
Last Modified: 2012-09-03
Hi,

I have access form with 10 command button and each button has a unique name.
I want to pass the information to other form which form has been pressed. To do that On click event I want to use a Global veriable and initialize the button name to that global veriable for example "A2345"
While a button pressed it will open another form and check which button has been pressed from that global variable do some action then clear the veriable for next use.

Please advise how to set up the global veriable for this purpose, example VBA code would be highly appreciated.

Thanks for prompt advise.
0
Comment
Question by:alam747
  • 10
  • 8
  • 2
  • +2
23 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38344031
Rather than use a variable, you can just pass the button's caption to the form you open with the OpenArgs parameter. For example:
Private Sub btnNameOfButton_Click()
    DoCmd.OpenForm "NameOfForm", , , , , , Me.btnNameOfButton.Caption
End Sub

Open in new window

Then in the form that opens, Me.OpenArgs will have the button caption.
You can use it as you would any variable.  For instance,
If Me.OpenArgs = "A2345" Then
...
Endif
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 38344041
To declare a global variable, at the top of any code module type
Global VariableName As Datatype

, then on button click you assign it a value


Kelvin
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38344443
Just a semantic point ..
For most of its life Access has used 'Public' rather than 'Global'  (both work ).
I think Access v2 was the last version that only used 'Global'.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:alam747
ID: 38346329
Hi peter57r,

If I want to use Public variable where exactly I need to define it.
It would be great if you can provide exaple VBA code.

Thanks
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38346398
Kelvin has already provided that information in his response above.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38346920
Here's a snippet of code from a module I named GlobalVariables

Public gloDate1 As Date
Public gloDate2 As Date
Public gloStoreCd As String

'Functions to return global variables
Public Function GetDate1() As Date
    GetDate1 = gloDate1
End Function

Public Function GetDate2() As Date
    GetDate2 = gloDate2
End Function

Public Function GetStoreCd() As String
    GetStoreCd = gloStoreCd
End Function

Open in new window

You only need the functions above if you intend to use the globals in a query or a calculated text box.  Otherwise you just need the declarations at the top.

Here's how you would use it in your form:
Private Sub btnNameOfButton_Click()
    gloNameOfGlobalVariable = Me.btnNameOfButton.Caption
    DoCmd.OpenForm "NameOfForm"
End Sub

Open in new window

0
 

Author Comment

by:alam747
ID: 38348810
Hi Irogsinta,

Thanks a lot for your example VBA code for global or public variable.
I tried to do the way you wrote.may be still I am clear enough. I declare as below at the top as :
Option Compare Database
Option Explicit
Public strButtonPressed As String

Then assign the button caption as below:

Private Sub IN_Click()
   
    Dim stDocName As String
    Dim stLinkCriteria As String
   
    strButtonPressed = Me.IN.Caption
   
End Sub

I want to use this to another form not function

Private Sub PassWord_AfterUpdate()

           If (strButtonPressed = "IN") Then
                ws_Target.Cells(2, 2) = strCurrentTime
           End If
           If (strButtonPressed = "OUT") Then
               ws_Target.Cells(2, 5) = strCurrentTime
           End If

Its giving me compile error "variable not defined"

Would you advise whats the problem.

Thanks
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38348830
Where are you defining strCurrentTime?
0
 

Author Comment

by:alam747
ID: 38349841
Private Sub IN_Click()
   
    Dim stDocName As String
    Dim stLinkCriteria As String

    Dim strCurrentTime As String

I defined it in the same function as above.
0
 

Author Comment

by:alam747
ID: 38349856
Please ignore my previous comment.

I defined strCurrentTime in the same function as below where I am using the public variable strButtonPressed and its givinig me compile error. I am not sure where I need define it again as I already defined it at the top.

Private Sub PassWord_AfterUpdate()

Dim strCurrentTime As String

strCurrentTime = Format(Now, "HH:MM:SS")
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38350310
You can only use your variable strCurrentTime in the procedure you defined it in.  Since you defined it in your IN_Click procedure, it is not visible in your PassWord_AfterUpdate procedure.  If you want it to be available to both procedures inside your form, then you need to declare it at the top of your form module.
0
 

Author Comment

by:alam747
ID: 38352670
Hi IroSigna,
The problem is not strCurrentTime variable, problem is the public variable that I defined at the top strButtonPressed . While I remove the option explicite the error gone but the public variable is null though I assighned a caption in it.

Please advice how to fix or whats the problem

Thanks
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38352676
Perhaps you could upload a sample database with just the objects you have problems with.
0
 

Author Comment

by:alam747
ID: 38352804
below are the VBA code:
Option Compare Database
Option Explicit

Public strButtonPressed As String
Main form with two buttons IN and OUT where I assigned button caption accordingly to the Public variable 'strButtonPressed'
Private Sub IN_Click()
   
    Dim stDocName As String
    Dim stLinkCriteria As String
   
    strButtonPressed = Me.IN.Caption
    'DoCmd.OpenForm stDocName, , stLinkCriteria, Me.IN.Caption
    DoCmd.OpenForm "SubSwitch"
End Sub

In the 'SubSwithch' form I did'nt use the Public variable value assigned but use in a form named Passform after update event procedure as below :

Private Sub PassWord_AfterUpdate()

Dim xl As Object
Dim wb_Target As Object
Dim ws_Target As Object
Dim stDocName As String
Dim stDocName1 As String
Dim strPassword  As String
Dim strCurrentTime As String
Dim blnEXCEL As Object

strCurrentTime = Format(Now, "HH:MM:SS")

If (Me.PassWord = strPassword) Then

    Select Case strPassword     'verify entered password
        Case "123321"
           If (strButtonPressed = "IN") Then
                ws_Target.Cells(2, 2) = strCurrentTime
           End If
           If (strButtonPressed = "OUT") Then
               ws_Target.Cells(2, 5) = strCurrentTime
           End If
        Case "234432"
           If (strButtonPressed = "IN") Then
                ws_Target.Cells(3, 2) = strCurrentTime
           End If
           If (strButtonPressed = "OUT") Then
               ws_Target.Cells(3, 5) = strCurrentTime
           ' End If
        Case "345543"
           If (strButtonPressed = "IN") Then
                ws_Target.Cells(4, 2) = strCurrentTime
           End If
           If (strButtonPressed = "OUT") Then
               ws_Target.Cells(4, 5) = strCurrentTime
           End If
        Case "456654"
           If (strButtonPressed = "IN") Then
                ws_Target.Cells(5, 2) = strCurrentTime
           End If
           If (strButtonPressed = "OUT") Then
                ws_Target.Cells(5, 5) = strCurrentTime
           End If
        Case "456654"
           If (strButtonPressed = "IN") Then
                ws_Target.Cells(5, 2) = strCurrentTime
           End If
           If (strButtonPressed = "OUT") Then
                ws_Target.Cells(5, 5) = strCurrentTime
           End If
           
    End Select
But here is the problem is strButtonPressed is Empty though I assigned button Caption in it.

Please advise how to fix it or where the problem is ?

Thanks for your prompt response.
Thanks
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 1000 total points
ID: 38352854
If you want strButtonPressed to be visible by other forms you need to declare it in a Module by itself and not at the top of your Form's module like you have it.

Also, here's another way to do your Select Case statement
Dim intRow As Integer, intCol As Integer
    Select Case strPassword     'verify entered password
        Case "123321"
            intRow = 2
        Case "234432"
            intRow = 3
        Case "345543"
            intRow = 4
        Case "456654"
            intRow = 5
    End Select
    
    If (strButtonPressed = "IN") Then
        intCol = 2
    ElseIf strButtonPressed = "OUT" Then
        intCol = 5
    End If
    ws_Target.Cells(intRow, intCol) = strCurrentTime

Open in new window

0
 

Author Comment

by:alam747
ID: 38352872
Hi IrogSinta,
Thanks for the advises.
Would you please give some example code which can be use for same purposes.
As you mentioned I have to define public variable in a module. how can fix my problem using the public variable in a module.
Or any way to use the public variable to entire project then it can be visiable from anywhere within database, I am not clear how to declaire then?

Thanks
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38352911
Check out this sample db.  Also strCurrentTime should be declared as a Date.
SampleDb.accdb
0
 

Author Comment

by:alam747
ID: 38353011
Hi Irogsinta,
Sorry , I am using access 2003. I cant open the example db you sent.
If you can send .mdb that would be great. Sorry for any kind of inconvenience.

Thanks
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38353020
Here you go.
SampleDb.mdb
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 38353684
You can also use the "hidden form" trick, where you build a form that contains textboxes, and you fill those textboxes as needed. For example, if I have a form named frmVars with a Textbox named "CallingForm", I'd open it Hidden when the app starts, and then use that textbox to store my data:

In the click event of your button:

Forms("frmVars").CallingForm = "MyFormName"

Now in your other functions:

If Forms("frmVars").CallingForm = "SomeFormName" Then
  '/ do something here
End IF

The trouble with global variables is they can be lost at times. Storing the data on a hidden form prevents that.
0
 

Author Comment

by:alam747
ID: 38357137
Hi LSMConsulting,
I tried your trick using a form, it works but would you please tell how to hide the open form.
I am using access 2003.

Thanks
0
 
LVL 85
ID: 38358701
When you open the form, you can specify that flag:

DoCmd.OpenForm "YourForm", acNormal, , , , acHidden
0
 

Author Closing Comment

by:alam747
ID: 38361720
Thanks to both for your help.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

840 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