Global variables in Access VBA - How?

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

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

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.

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

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!!!