Full Version: Setting a Public Variable
UtterAccess Forums > Microsoft® Access > Access Forms
I know this is probably something easy but I haven't been able to figure it out. I'm just trying to set a variable in my form's module to be used in all the functions of that form. I'm trying to do this.
im dbs as Database
set dbs = Currentdb
How can I get the dbs to set globally in the module, so I don't have to set it in every procedure?
Are you DIMming the dbs variable at the top of the module? I don't think you can Set a variable outside of any procedure, so you'll have to Set it in all the functions. Otherwise, you can just directly refer to CurrentDb. HTH
At the top of the form's code module, outside of any procedure, put the variable declaration:
Private dbs As Database ' I believe Dim will also work
Then in the load or open event of the form, so it is only done one time,
Set dbs = CurrendDb
Public variables are simply defined in your code (class) module before any procedures. Are you aware that CurrentDB is *always* available and need not be Dimmed?
Do you mean you want a module-scope variable representing the (current) Database in the CBF (Class) module?
The declaration is correct but you need to set it in the Form_Open Event with
Set dbs = CurrentDb
Make sure you set dbs to nothing when the Form is unloaded / closed.
But why? You can simply use CurrentDb (which is up-to-date) or DBEngine(0)(0) whenever you want to refer to the Database object.
Since no one has ever convinced me that doing this:
dim db as database
set db = currentdb
db.execute strsql

is better or more efficient than:
currentdb.execute strsql

I just use the latter.
However, if you want to declare an object that is available for use globally in all objects, the process is pretty simple. You create a code module and in the declaration section, you put something like this:
Option Compare Database
Option Explicit
public gCurrentUser as string

I hope this helps!
- Sligo
Hi Slgo
think Dan used the wrong term "global" but he meant module-scope variable.
I used a bad example on that one. I don't necessarily need to set the database object because I could do that without the variable, it would just save a few characters of typing and that's about it, no big deal. What I really want to do is to set an Object.
I'm going to try some of these examples. Thanks for the suggestions.
ither way, it still works. If you declare a variable in the declaration section of a form or report module, that variable is available for all modules in that object. The difference here is the extension of scope to sub-reports or sub-forms. If the variable is declared privately (using Dim or Private), then it is ONLY available within that form, but if it is declared Public, then that objects children can use it, if properly referenced.
- Sligo
Wouldn't Public mean it is available to all other Modules (including the CBF of the children objects of the CBF in which the Public Variable is declared)?
He know that Public will work but aren't we supposed to keep the scope as small as possible?
I am not 100% sure of what Dan wanted but it certainly (to me at least) sounds like he wanted only module-scope variable and not Public / Global ...
Hi all (and Van me alwd mate!)
Oknow there will always be developers who have a preference (or even a belief!) that public variables are either harmless or hideous.
Personally - I view them as pretty innocuous - but I still avoid them when I can be bothered to. ;-)
o - we're on to module level variables.
This has all been well covered in this thread by now (I'd imagine) - but I'll re-iterate for Dan and all and then extend.
The module level (i.e. in the declarations section) variable is directly available to all procedures in that module.
If the variable is declared public then it is available to all modules (except when within a class such as a form module - when it must still be referenced as part of a valid object, e.g. an instance of a form).
If it is declared as Private then it is not directly referencable externally.
However even Private variables can be referenced - as long as they are prefixed by the owning module.
Debug.Pring varVariableName
works anywhere for public variables
Debug.Pring ModuleName.varVariableName
still works for private variables.
To mention an implementation of this (I think the intial example was actually a very good one - and to address a point Sligo makes...)
Using a reference to CurrentDb, as we all know, creates an instance of a database object to the currently running application - refreshing it's child collections first.
Hence it has overhead - compared to the occasionally offered alternative DbEngine(0)(0) (which doesn't refresh).
Repeated use of CurrentDb (for example in a poorly formed loop) adds progressive inefficiency due to this overhead. I once answered a question where I estimated about 10%... I then tested to verify this and was almost exactly right with that gut feeling. :-)
So - IMO a persisted instance of a database object isn't a bad thing.
(If you want to refresh a collection of it you can do so explicitly.)
It also means you can explicitly destroy that single instance when the application closes (handy for connection objects too).
One public object variable holding a DAO.Database object is one object. But you need to have assigned it to an instance of CurrentDb when your application starts. And any code failures and that variable will again be Nothing!
However if you avoid the public reference and just use a module level variable and instead have a public function - then you give yourself the same efficient option... and can introduce a degree of self setup / self-healing to the object.
'In the declarations section of a standard module
Private mDb As DAO.Database
Function fCurrentDB() As DAO.Database
On Error Resume Next
    Set fCurrentDB = mDb
    If fCurrentDB Is Nothing Then
        Set mDb = CurrentDb
        Set fCurrentDB = mDb
    End If
End Function

And during your application just refer to fCurrentDB whenever you would have normally used CurrentDB.
This can then extend to virtually any variable you want to be able to refer to publicly.
You can even hold an array or collection of objects or variables and have a single function which retrieves any of them by name if you're so inclined.
The overall technique isn't dissimilar to a Class module methodology - and wrapping a class or introducing your own Type can allow you to fetch values without having to remember what you called all your "global" variables. :-)
Nice bit of code and even nicer for the term "self set-up / self-healing", Leigh.
actually do that for a global ADO Connection object to a Microsoft SQL Server back-end database in one of my database applications (MDB format) except I am not a wordsmith so I couldn't come up with "self set-up/self-healing". Verbatim from the code window (sorry for all the extra commenting bits):
Public Function fnGetCnnSQL() As ADODB.Connection
' vbaSQLConnection.fnGetCnnSQL
' Purpose: to get gcnnSQL (re-create if required)
' Notes:
' Parameters:
' Called Subs/Functions:
' Calling Subs/Functions:
' Returns:
' Author : Van T. Dinh, 30/11/2004
' Revision History:
' 30/11/2004 (VTD): First-coded
On Error GoTo fnGetCnnSQL_Err
  If (gcnnSQL Is Nothing) Then
    Set gcnnSQL = New ADODB.Connection
    gcnnSQL.Open gMSSQL_OLEDB
  ElseIf (gcnnSQL.State <> adStateOpen) Then
    Set gcnnSQL = New ADODB.Connection
    gcnnSQL.Open gMSSQL_OLEDB
  End If
  Set fnGetCnnSQL = gcnnSQL
  On Error Resume Next
  Exit Function
  Select Case Err.Number
    Case 0
    Case Else
      MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf & _
        "(Programmer's note: vbaSQLConnection.fnGetCnnSQL)", _
        vbOKOnly + vbCritical, "Run-time Error!"
  End Select
  Resume fnGetCnnSQL_Exit
End Function

BTW, congrats on your newly-acquired super-fast typing skills ... sad.gif sad.gif your posts get longer, I think, as it take me so longer to read your posts (or I am getting older faster than I think I should???)
However even Private variables can be referenced - as long as they are prefixed by the owning module.

I would certainly hope not.
Hi Ace
hat's new to me also. However, coming from Leigh, who seems to know numerous bits of minute details that we, mere mortals, are not even aware of, I thought I would do some testing later tonight to ascertain the statement ...
Well - I've only ever used it from the Immediate window for convenience.
I've not been able to simulate the functionality from procedures (using late binding, Eval, CallByName etc).
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.