Public Boolean variable not retaining value (AC2007) (1 Viewer)

AOB

Registered User.
Local time
Today, 15:05
Joined
Sep 26, 2012
Messages
613
Hi guys,

Struggling with some odd behaviour...

I have a backup subroutine which automatically triggers when the front-end is closed down (it just takes the back-end, makes a copy and compacts it)

It's driven off a hidden form which I use to track who is connected to the BE at any given time. This form is opened as part of the AutoExec when the FE is opened and writes some basic user info to a table. When the form is closed, it updates the table and fires the backup process before quitting Access. Works a charm, no problems with that.

Part of that user tracking process checks to see if the same user is already connected - either elsewhere (i.e. on a different machine) or on the same machine (i.e. opening a second instance of the same FE) - which is undesirable (and, frankly, unlikely, but not impossible) A brief prompt appears to explain that they can only be connected once, at which point the application is quit (to enforce the rule) This also works fine.

However - it doesn't really make sense to fire the backup process in these situations, as the user is only briefly 'in' before being kicked straight back out again. So I figured I would add a public boolean variable, set it to True by default, then switch it to False if the same user is already logged on before quitting Access.

Trouble is, the variable doesn't seem to be holding its value?

Here is the Load event for the tracking form :

Code:
Private Sub Form_Load()
 
  On Error GoTo ErrorHandler
 
  Dim dbs As Database
  Dim rst As Recordset
  Dim strSQL As String
 
  Call InterfaceInitialise
 
[COLOR=red]  blnPerformBackup = True[/COLOR]
 
  ' Check if this is the first time this user has connected
 
  Set dbs = CurrentDb
 
  With dbs
 
    strSQL = "SELECT [tblConnections].* " & _
             "FROM [tblConnections] " & _
             "WHERE [tblConnections].[UserID] = " & Chr(34) & UCase(cSysInfo.UserName) & Chr(34)
 
    Set rst = .OpenRecordset(strSQL)
 
    With rst
 
      Select Case .RecordCount
 
        Case Is > 0      ' Returning user
 
          ' Check if already logged on elsewhere
 
          If .Fields("Connected") = True And .Fields("Hostname") <> UCase(cSysInfo.ComputerName) Then
 
            MsgBox "You are already logged in on another machine : " & vbCr & vbCr & _
                    .Fields("Hostname") & " in " & LCase(.Fields("Domain")) & vbCr & vbCr & _
                    "You must log off the above machine first", vbCritical, "Already Logged On?"
 
            [COLOR=red]blnPerformBackup = False[/COLOR]
            Application.Quit
 
          ' Check if already connected on same machine
 
          ElseIf .Fields("Connected") = True Then
 
            MsgBox "You already have this DB open?", vbCritical, "Already Logged On?"
 
            [COLOR=red]blnPerformBackup = False[/COLOR]
            Application.Quit
 
          Else
 
            ' Update record
 
            .Edit
            .Fields("Connected") = True
            .Fields("Hostname") = UCase(cSysInfo.ComputerName)
            .Fields("Domain") = UCase(cSysInfo.ComputerDomain)
            .Fields("LastLogon") = Now
            .Update
 
          End If
          .....

And then in the UnLoad event for the same form :

Code:
If [COLOR=red]blnPerformBackup[/COLOR] Then
 
  If Nz(BackupBackEnd, "") = "" Then MsgBox "Backup process has failed", vbCritical, "Backup Failed"
 
End If

For some reason, blnPerformBackup is False every time, even though I set it to True at the very start of the Load event (and it is a Public variable, defined in a separate module where I store all my Public constants and variables)

Any suggestions as to why it is not retaining its value from the Load event? I've checked and it does get set to True - and when I debug, it remains True - but at runtime it reverts back to False by the time it reaches the decision whether to backup or not?

Thanks

Al
 

JHB

Have been here a while
Local time
Today, 16:05
Joined
Jun 17, 2012
Messages
7,732
...and it is a Public variable, defined in a separate module where I store all my Public constants and variables
You need to set and get the value from it, in that module.
 

AOB

Registered User.
Local time
Today, 15:05
Joined
Sep 26, 2012
Messages
613
You need to set and get the value from it, in that module

Why, exactly?

That module is purely used to keep all my Public variables and constants together in one place. It only has definitions, it doesn't contain any working code. I set the variables as I go, as and when they need to be initialised. Because they are public, they are portable.

Hence I only declare blnPerformBackup in that module; its value is set where it is needed (in the Form_Load event)

I could just as easily add...

Code:
Public blnPerformBackup As Boolean

...at the top of the Form component instead; it doesn't change anything?
 

JHB

Have been here a while
Local time
Today, 16:05
Joined
Jun 17, 2012
Messages
7,732
As I understand you, you've declared blnPerformBackup in a seperat module, outside the form, correct?
Therefore to use it you need to set and get the value in the module where you declare blnPerformBackup.
Below is an example for the module.
Code:
Dim blnPerformBackup As Boolean

Public Function SetValueblnPerformBackup(BVariable As Boolean)
  blnPerformBackup = BVariable
End Function

Public Function GetValueblnPerformBackup() As Boolean
  GetValueblnPerformBackup = blnPerformBackup
End Function
To set the value in your form use:
Code:
SetValueblnPerformBackup (False)
To get the value in your form use:
Code:
  aVariable = GetValueblnPerformBackup
Use "Option Explicit" then you'll get a error message if you use some variables which isn't declared.
 

stopher

AWF VIP
Local time
Today, 15:05
Joined
Feb 1, 2006
Messages
2,396
A public variable should work fine in a module without any further code. The fact that it is public in a module makes it public to the application.

However, I have read about the volatility of public variables when unhandled errors fire. You might want to google this for more info. To avoid you could save the variable in a table instead.

hth
Chris
 

JHB

Have been here a while
Local time
Today, 16:05
Joined
Jun 17, 2012
Messages
7,732
Sorry - yes ofcause you're right.
 

AOB

Registered User.
Local time
Today, 15:05
Joined
Sep 26, 2012
Messages
613
Hmmm, interesting, thanks guys

To be honest I use public variables quite a bit (I don't mean I use a lot of them; I use them frequently in a lot of projects) and have never had problems with their portability. I think the problem in this case could be that I'm setting it as part of the FormLoad, but then quitting the application entirely and jumping straight to the FormUnLoad. So the form isn't fully loaded before it is subsequently unloaded. Perhaps this is causing the variable to fail to take on the value in a stable manner. I don't know, it's just a thought.

Anyway - to stopher's point regarding storing the value in a table rather than holding it in a variable - this is a bit tricky really! I can't use an existing BE table (i.e. tblConnections) as, by definition, there is a possibility of a user / host combination already being in place (and I don't want to turn off the backup for their 'original' login)

So I'd have to use a local table - not a huge deal but it's yet another table to maintain (albeit a very basic one) Was just about to plough on with that when I thought of an alternative.

As part of the startup process, once the tracking form is loaded and the relevant information recorded, a navigation form also loads. It's the central form from which all other forms are opened. Kind of like a switchboard only it's my own custom built version. So rather then using a boolean variable, I can just test to see if this form has been opened yet. If it has, it's a 'good' login, and the backup can proceed as normal. If it's not, I know that my startup process has decided to kick the user before they started, and I can skip the backup process.

Hence, this works :

Code:
If Application.SysCmd(acSysCmdGetObjectState, acForm, "frmNavigation") = acObjStateOpen Then
 
  If Nz(BackupBackEnd, "") = "" Then MsgBox "Backup process has failed", vbCritical, "Backup Failed"
 
End If

It's the same basic Boolean value, only derived at runtime based on a different criteria rather than set and stored.

That said, appreciate the info on public variable volatility, will File > Save As.. for future reference! :D

Many thanks!
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:05
Joined
Jan 20, 2009
Messages
12,849
However, I have read about the volatility of public variables when unhandled errors fire.

This is a persistent myth. Unhandled exceptions cause code to halt. Variables are not cleared. Variables are reset in this situation only when the user presses the Reset button in the VBA editor.

In the case of the original question, the variables are lost at Application.Quit.

The first thing that happens when the Quit is issued is all variables are cleared. The running code then continues to execute, followed by the Close and Unload events of the forms as they are closed on the the way to quitting.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:05
Joined
Jan 20, 2009
Messages
12,849
One solution that avoids tables would be to use a database property. These are not lost in a reset or Quit.
 

AOB

Registered User.
Local time
Today, 15:05
Joined
Sep 26, 2012
Messages
613
Thanks Galaxiom, that makes a lot more sense - although I wasn't aware that variables were cleared on .Quit - this is extremely useful information to have, thank you!
 

Users who are viewing this thread

Top Bottom