Global variables in Access VBA - How?

*************
* Background *
*************

I need to store and update data in a load of global variables:
- When I load up forms I pre-populate the fields with this data.
- When controls are changed the global fields I update the globals

I have sucessfully defined my variables in a module like this:
    ' Setup globals
    Global gblUser As Integer
    Global gblSampler As Integer
    Global gblProject As Integer
    Global gblExportPath As String

*************
* The problem *
*************

When I hit an error all the globals are lost (reset to default non-initiated values - "" or null or something).

********
* Ideas *
********

- Everytime I call a global I check it's not blank - if it is I reset globals to defaults.
- Save the values of globals in a db table.
- Open a form and set controls in this form to the global values. Grab / update the data straight from here.

***************
* My question!!! *
***************

What is the best way to store "globals" in Access?

Please feel free to add your own ideas - preferably really good ones :oD
LVL 11
Angelp1ayAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
1) - I have never noticed a significant delay.

2) - No.  One way you can get around this is to throw this line in the top of every sub/function that uses a global

If gsSomeValue Is Nothing Then Call GetAllGlobals
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>When I hit an error all the globals are lost
Likely, when you have to break execution of code, all global variables are wiped out.

In my apps I have a table VAR_VARIABLES, with an ID and VALUE column, and use it to store variable-like values that I wish to be retained across user sessions.
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
<When I hit an error all the globals are lost>

how do you handle the error?

do you have error handling routine?

the values of the global variables should still be intact. (AFAIK)
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Angelp1ayAuthor Commented:
<< In my apps I have a table VAR_VARIABLES, with an ID and VALUE column, and use it to store variable-like values that I wish to be retained across user sessions. >>

I will have multiple users logging in so if I did use the db table method it would have the advantage that I could maintain globals per user if I wished. I'm a little worried about the extra load  caused by continually updating a globals table, and also (although less so) about unnecessarily increasing db size.


<< how do you handle the error? >>
Very badly - at the moment I just let it die!

I'd prefer not to rely on having error code everywhere that catches my globals. Obviously I'll try my best to stop all errors but if I can maintain my globals even when I fail to trap an error that would be better.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I'm a little worried about the extra load  caused by continually updating a globals table
For the most part I load the ones I care about on startup, and will get / set variables whenever I need to do any form-to-form gymnastics and don't wish to deal with form references.

>I will have multiple users logging in
I also have a VARU_VARIABLES_USER table set up to handle user-specific variables, but I have yet to impliment this.
0
 
Angelp1ayAuthor Commented:
So for the db stored globals I would have something like this:

Form:
    Public Sub Form_Load()
        Me.cbo_project.Value = GetGbl('project')
    End Sub

    Public Sub cbo_project_AfterUpdate()
        SetGbl('project', Me.cbo_project.Value)
    End Sub

Global Module:
    Public Function GetGbl(type AS String)
        GetGblProject = CurrentProject.Connection.Execute("SELECT globals.val FROM globals WHERE globals.type = '" & type & "';").Collect(0)
    End Function

    Public Sub SetGbl(type AS String, value As String)
        CurrentProject.Connection.Execute("UPDATE globals SET globals.val = '" & value & "' WHERE globals.type = '" & type & "';")
    End Sub

Looking pretty neat. Could be extended easily enough to have a value per user.

I have two questions!!!!
1) Will there be nasty load on setting the global value - I will be setting globals VERY frequently
2) Does Form_Load fire after an error when the form is recovered? I want fields to be restored after any error.
0
 
Angelp1ayAuthor Commented:
<< 2) - No.  One way you can get around this is to throw this line in the top of every sub/function that uses a global

If gsSomeValue Is Nothing Then Call GetAllGlobals >>

Perhaps something like this:

    Public Sub Form_Activate()
        If Nz(Me.cbo_project.Value,"") = "" Then Me.cbo_project.Value = GetGbl('project')
        If Nz(Me.cbo_user.Value,"") = "" Then Me.cbo_user.Value = GetGbl('user')
    End Sub

Infact, taking this a little further, say I tag up all the controls pre-populated from globals with "gbl". I could then have something like this:

    Public Sub Form_Activate()
        Dim ctl AS Control
        For Each ctl In Me.Controls
            If ctl.Tag = "gbl" Then
                If Nz(ctl.Value,"") = "" Then ctl.Value = GetGbl(special_trim(ctl.Name))
            End If
        Next ctl
    End Sub

    Public Function special_trim(control_name AS String)
        special_trim = Some code to snip the control name down to give a global "type"
    End Function

Got a bit carried away there!!! Enough of that.

****************
Is the db method better than using traditional VBA global variables or using a hidden form populated with the global variables? Are there any advantages / disadvantages for each? Are there any other cunning ideas for globals?
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>Is the db method better than using traditional VBA global variables or using a hidden form populated with the global variables?
I highly recommend against the hidden form method, as that requires that that form be open in order to grab the variable.
DB vs. VBA global variables, no real difference, whichever floats your boat will be okay.
0
 
Angelp1ayAuthor Commented:
Well I'm certainly leaning towards the DB table method at the moment. I think the tag method in the activate trigger above is a good way to be checking that fields are not empty post errors.

The problem with the db method is that it maintains the value between instances of access. Values used in a previous session are not necessarily of use in the following session, but they also won't be "" / null and trip the previous check.

I suppose I could date stamp any updates to the globals and only keep them for a period of time - not great... what length of time is enough, but not too much.

Is there a trigger fired each time Access starts? I could record the most recent Access instance's start time and only recognise globals set since then!!!
0
 
Angelp1ayAuthor Commented:
Ok, I'm going to just run something in the startup form. If the user bypasses it then they'll have to deal with the first set of globals being old!

Cheers for the help guys, points on the way :o)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw I finally got around to publishing an article on Table Based Access Variables that demonstrates how to use a VARIABLES table to pull this off, with downloadable code.
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.