Use of global veriable in access 2003

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.
alam747Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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
 
IrogSintaCommented:
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
 
Kelvin SparksCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
peter57rCommented:
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
 
alam747Author Commented:
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
 
peter57rCommented:
Kelvin has already provided that information in his response above.
0
 
IrogSintaCommented:
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
 
alam747Author Commented:
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
 
IrogSintaCommented:
Where are you defining strCurrentTime?
0
 
alam747Author Commented:
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
 
alam747Author Commented:
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
 
IrogSintaCommented:
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
 
alam747Author Commented:
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
 
IrogSintaCommented:
Perhaps you could upload a sample database with just the objects you have problems with.
0
 
alam747Author Commented:
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
 
IrogSintaConnect With a Mentor Commented:
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
 
alam747Author Commented:
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
 
IrogSintaCommented:
Check out this sample db.  Also strCurrentTime should be declared as a Date.
SampleDb.accdb
0
 
alam747Author Commented:
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
 
IrogSintaCommented:
Here you go.
SampleDb.mdb
0
 
alam747Author Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
When you open the form, you can specify that flag:

DoCmd.OpenForm "YourForm", acNormal, , , , acHidden
0
 
alam747Author Commented:
Thanks to both for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.