in vba access 2010 how can i get a string from a veriable in a  public sub in the same form

Posted on 2011-04-21
Last Modified: 2012-05-11

i'm not succeed to get the contect  from a veriable in the same form from another public sub and when i make msgbox and the veriable name i get a blank msgbox and its seems that the veriable is already empty
Question by:bill201
    LVL 8

    Expert Comment

    Variables declared within a procedure are in scope only within that procedure.  If they are declared within a module's header, their scope defaults to Private, and are available only to procedures within that module.  If they are  declared within a module's header as Public then they are available globally.

    It is good practice to prefix the name of a global variable with "g_" so that it's obvious that is is global.  It's also good practice to avoid global variables whenever possible, as they can be cause problems and be hard to debug.

    I hope this helps.
    LVL 8

    Expert Comment

    'Module Header
    'Defaults to Private so available to the procedures within this module
    Dim m_strString as String
    Public Sub SetTheString(NewValue As String)
        'A way to set the value
        m_strString = NewValue
    End Sub
    Public Sub GetTheString()
       'A way to display the value
        MsgBox m_strString
    End Sub

    Open in new window

    Does that give you something to work with?

    Author Comment

    thanks alot for your answer i try your code but i don't succeed here is a copy from my code:
    Option Compare Database

    Dim Gsqlstatement As String

    Public Sub GetTheSqlStatement(getValue As String)
    getValue = " Test "
    Gsqlstatement = getValue
    End Sub
    and this what i wrote in my form to get a message box this content from getvalue veriable:

    Public Sub Command28_Click()
    MsgBox Gsqlstatement
    End Sub
    LVL 26

    Assisted Solution

    Not Dim
    Dim is Private

    You want public

    <Dim Gsqlstatement As String>  BAD
    Public Gsqlstatement As String

    <Public Sub Command28_Click()
    MsgBox Gsqlstatement
    End Sub >   BAD

    You haven't set Gsqlstatement yet!

    Option Compare Database
    Option Explicit
    Public gMySQL As String

    Public Sub Set_gMySQL(MyInputString As String)
    gMySQL = MyInputString 'set your global to the value you pass in
    End Sub

    Public Sub Command28_Click()
    Call Set_gMySQL("Test") 'set your global variable
    MsgBox gMySQL 'now see what it is
    End Sub

    LVL 8

    Accepted Solution

    Don't try and access the variable directly.  Use procedures to set and get it's value.

    Try this:

    Option Compare Database
    Option Explicit  'Good practice - forces you to declare variables properly'
    'Variable to hold the string value locally within this module'
    Private mSqlStatement As String 'Private is fine for this example - "m" prefix indicates a module level variable ("g" would be used for a public and thus global variable)'
    'Procedure into which the string is passed'
    Public Sub SetSqlStatement (InputStatement As String)
    'Store the value in the variable'
     mSqlStatement = InputStatement
    End Sub
    'Procedure to retrieve the value from the variable'
    Public Function GetSqlStatement () As String
    If Len(mSqlStatement) = 0 then
     GetSqlStatement = "No value set"  'Return something as a default - maybe remove this in production'
     GetSqlStatement = mSqlStatement 
    End If
    End Function

    Open in new window

    You could then test this a couple of ways.
    1. In the Immediate window enter
    Msgbox GetSqlStatement 

    Open in new window

    NOTE: You're retrieving the value using the function, not by trying to access the variable directly.
    This should give you a Msgbox saying "No value set".

    2. Write a test procedure
    Public Sub TestSqlStatement()
    SetSqlStatement "SELECT X FROM Y"
    End Sub
    then test that in the Immediate window simply by entering

    Open in new window

    which should return a message box reading "SELECT X FROM Y".


    Author Comment

    thanks for your both you give my excellent and very clear  examples that learn me a lot

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    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…
    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…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    761 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

    11 Experts available now in Live!

    Get 1:1 Help Now