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


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
Who is Participating?
Andrew_WebsterConnect With a Mentor Commented:
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".

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.
'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?
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.

bill201Author Commented:
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
Nick67Connect With a Mentor Commented:
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

bill201Author Commented:
thanks for your both you give my excellent and very clear  examples that learn me a lot
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.