[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Global variables in Access VBA - How?

Posted on 2007-10-04
11
Medium Priority
?
15,566 Views
Last Modified: 2013-12-26
*************
* 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
0
Comment
Question by:Angelp1ay
  • 5
  • 5
11 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 20014253
>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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 20014258
<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
 
LVL 11

Author Comment

by:Angelp1ay
ID: 20014331
<< 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 20014378
>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
 
LVL 11

Author Comment

by:Angelp1ay
ID: 20014543
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
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1600 total points
ID: 20014569
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
 
LVL 11

Author Comment

by:Angelp1ay
ID: 20014864
<< 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
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1600 total points
ID: 20015063
>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
 
LVL 11

Author Comment

by:Angelp1ay
ID: 20015286
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
 
LVL 11

Author Comment

by:Angelp1ay
ID: 20020618
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39741060
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

872 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