Public Variables in a module vs Dim variables in a form

[JMongi]

So, I have benefitted greatly from various sample DBs and VBA code posted on this forum. Thanks!
My application is quickly expanding in complexity. It's exciting and daunting...but, such is life sometimes. Philosophizing aside, I'm trying to clean up the underlying VBA code from a variety of modules and code snippets so that it is clearer and more maintainable (and ensuring that even though its not mine, I understand exactly what it is doing or supposed to do).

In the various login code for initiating a user session I am using as a base code from @isladogs that works just fine. In the module there are public variables declared such as "strUserName" and "intAccessLevel". These are used during the use of the login form. Part of the sample package includes an "add user" form for the admin to add a new user. In the add user form VBA there are variables defined using Dim that are also "strUserName" and "intAccessLevel". These variables are used in direct SQL statements to insert the new users into the appropriate table.

I presume the variables that are Dim'ed in the add user form do not interact with the variables in the module.

In summary:
1. In frmNewUser, the Dim variables are used to construct an SQL statement to write a new user record to the users table.
2. In modAuditLog, the public variables are used to construct an SQL statement to write session log information to the appropriate tables. Since these functions may be called via multiple forms, this was structured as a module and variables are public so they can be used by whatever form is calling the module.

I just want to confirm my understanding before I go editing/expanding the user access portions of this code to meet my own needs. Here are code snippets for reference:

modAuditLog Snippet

Option Compare Database
Option Explicit

'Public declarations
Public lngUserId As Long
Public strUserName As String
Public strComputerName As String
Public strPassword As String
Public intAccessLevel As Integer
Public blnChangeOwnPassword As Boolean
Public lngLoginID As Long
Function LogMeIn(strUserName As Long)
'Go to the users table and record that the user has logged in and which computer they have logged in from

CurrentDb.Execute "UPDATE tblUsers SET LoggedIn = True, Computer = GetComputerName()" & _
    " WHERE UserName='" & GetUserName & "' AND tblUsers.Active=True;"
    
End Function
Function LogMeOff(strUserName As Long)
'Go to the users table and record that the user has logged out

CurrentDb.Execute "UPDATE tblUsers SET LoggedIn = False, Computer = ''" & _
    " WHERE UserName='" & GetUserName & "';"

End Function
Function CreateSession(LoginID As Long)
'This function records the login details of the person
'Get the new loginID
'v5 21/11/2018 - added Nz to manage case where no record exists

lngLoginID = Nz(DMax("LoginID", "tblLoginSessions") + 1, 1)

CurrentDb.Execute "INSERT INTO tblLoginSessions ( LoginID, UserName, LoginEvent, ComputerName )" & _
    " VALUES(GetLoginID(), GetUserName(), Now(), GetComputerName());"

End Function
Function CloseSession()

'This closes the open session
    'set logout date/timein tblLoginSessions
    CurrentDb.Execute "UPDATE tblLoginSessions SET LogoutEvent = Now()" & _
        " WHERE LoginID= " & GetLoginID & ";"
    
    'clear user login in tblUsers
    CurrentDb.Execute "UPDATE tblUsers SET LoggedIn = False, Computer = Null" & _
        " WHERE UserName= '" & GetUserName & "';"

End Function
Public Function GetComputerName() 'gets computer name used to log in to database
   GetComputerName = CreateObject("WScript.Network").ComputerName
End Function
Public Function GetUserName()    'gets logged in user name
    GetUserName = strUserName
End Function
Public Function GetLoginID()    'gets loginID for the current session
    GetLoginID = lngLoginID
End Function

frmNewUser Snippet

Option Compare Database
Option Explicit

Dim strUserName As String
Dim intPasswordExpireDays As Integer
Dim blnChangePWD As Boolean
Dim intAccessLevel As Integer
Dim dtePwdDate As Date
Private Function CheckValidUserName() As Boolean

    CheckValidUserName = True
    If Nz(Me.txtUserName, "") = "" Then
            FormattedMsgBox "User name NOT entered" & _
                "@Please try again     @", vbCritical, "You MUST enter a user name!"
            CheckValidUserName = False
    ElseIf Len(txtUserName) > 15 Or InStr(txtUserName, " ") > 0 Then
           FormattedMsgBox "The user name must have a maximum of 15 characters with no spaces" & _
               "@Please try again     @", vbCritical, "User name error"
           CheckValidUserName = False
    End If
    
    If CheckValidUserName = False Then
        cmdAdd.Enabled = False
        Me.txtUserName = ""
        Me.txtExpireDays = 0
        Me.cboChangePWD = "No"
        Me.cboLevel = 1
        Me.txtUserName.SetFocus
    End If
    
End Function

Private Sub cmdAdd_Click()

strUserName = Me.txtUserName
intPasswordExpireDays = Nz(Me.txtExpireDays, 0)
blnChangePWD = IIf(Me.cboChangePWD = "Yes", -1, 0)
intAccessLevel = Nz(Me.cboLevel, 1)
If intPasswordExpireDays > 0 Then dtePwdDate = Date

 'add new user
    If dtePwdDate <> 0 Then
        'v5.3 17/02/2020 - corrected date code to fix error
        CurrentDb.Execute "INSERT INTO tblUsers ( UserName, Active, PWD, ChangePWD, ExpireDays, AccessLevel, PWDDate)" & _
            " SELECT '" & strUserName & "' AS UserName, True AS Active, '" & SetDefaultPwd() & "' AS PWD," & _
                " " & blnChangePWD & " AS ChangePWD, " & intPasswordExpireDays & " AS ExpireDays," & _
                " " & intAccessLevel & " AS AccessLevel, #" & Format(dtePwdDate, "mm/dd/yyyy") & "# AS PWDDate;"
    Else 'omit PWDDate
         CurrentDb.Execute "INSERT INTO tblUsers ( UserName, Active, PWD, ChangePWD, ExpireDays, AccessLevel)" & _
            " SELECT '" & strUserName & "' AS UserName, True AS Active, '" & SetDefaultPwd() & "' AS PWD," & _
                " " & blnChangePWD & " AS ChangePWD, " & intPasswordExpireDays & " AS ExpireDays," & _
                " " & intAccessLevel & " AS AccessLevel;"
    End If
    
    Me.lblInfo.Caption = "New user " & Me.txtUserName & " has been successfully added" & vbCrLf & _
        "A default password 'Not set' has been added" & vbCrLf & _
        Me.txtUserName & " will be required to enter a new password at first login"

End Sub

 

[The_Doc_Man]

In general, there is a thing called "Scope" that is, in essence, the "visibility" of a variable. The rule is "closest match wins." In a case where you have Public variable X in a general module Y, it is visible anywhere inside your VBA code. Anywhere... except if you have a Dim'd variable X in a form, report, or class module Z. From code inside Z, the Dim'd version of X (in the same module) is closer. Public variable X is visible only if you qualify it with something like Y.X - and in this case, the Public version wins because of the qualifier. Outside of module Z, however, that Dim'd variable X is NOT visible because that syntax limits the scope of Dim'd X to module Z. So anywhere else, you see the Public definition of X in module Y.

Does that help? Or did you have a more specific question?

 

[isladogs]

Hi
The code displayed is mostly/entirely(?) taken from my app Password Login - Mendip Data Systems.
I'm not sure whether the post is specifically aimed at me as it appears to be a general question about variable scope.

Anyway, assuming that is what you are asking about ....
Variables defined in form / report procedures only apply to that procedure
The scope of variables defined at form/report level (in the declarations section) is for the entire form/report
By contrast, public variables declared in standard modules are available throughout an application.

Due to the above, I try to NEVER define a variable in a form with the same name as a public variable as you can get a conflict

 

[JMongi]

@The_Doc_Man - Makes perfect sense. That was the gist of my question. I ask, because the last time I started mucking around with this code it broke almost immediately in a non obvious way but that was clearly related to variable definitions. In that case I was trying to eliminate what I thought was an inline definition of a variable that turned out to be a public variable defined somewhere else, so when I defined the variable within the procedure I broke the chain.

@isladogs - This is your password login code. If it's not exactly the same its functionally the same. I agree with your feelings on same variable names and trusting in scope to save you. This appears to have slipped through for whatever reason.

I just wanted to make sure I had the requisite understanding before I started editing it.

 

[isladogs]

Hi
I thought I'd better check the variables in the original version of PasswordLogin_v5.3

ModAuditLog only has 1 variable:
Public lngLoginID As Long

frmNewUser has 5 variables at form level:
Dim strUserName As String
Dim intPasswordExpireDays As Integer
Dim blnChangePWD As Boolean
Dim intAccessLevel As Integer
Dim dtePwdDate As Date

So in my version, I thought I could say there is no overlap / repetition between form & modules ...
BUT then I looked at modFunctions:
Public lngUserID As Long
Public strUserName As String
Public strComputerName As String
Public strPassword As String
Public intAccessLevel As Integer
Public blnChangeOwnPassword As Boolean

OOPS!
Looks like I need to do some tidying up! Thanks for (indirectly) bringing this to my attention

 

[JMongi]

NEWSFLASH! - Isladogs is proven to NOT be perfect....story at 11!

I probably had already combined modFunctions and modAuditLog to simplify, removed unneeded code and to clarify the various declarations from my previous snafu referenced in the first post. Sorry if that made it a little tricker to track down for you. Can't really complain about freely provided template code/forms. That would just be ungrateful!

 

[isladogs]

NEWSFLASH! - Isladogs is proven to NOT be perfect....story at 11!

There are plenty who will thoroughly agree with that statement!
Anyway, thanks to your prompt I've now removed the duplicate declarations from the form in the next version v5.4 of the app.

Unfortunately, due to circumstances beyond my control, I'm currently unable to make any edits to my website (not least fix the broken links that have appeared this week on the home page!)

 

[JMongi]

Quite pleased that my modified code appears to be working correctly and at the very least didn't majorly break anything. A new chapter in my Access development experience.

 

[JMongi]

This particular form that I borrowed from someone else *cough* isladogs *cough* is indeed unbound. Not my typical setup, but it's clear enough for my uses. I don't know enough SQL to mess with unbound forms and crafting the SQL from scratch via VBA unless I have a good reason to do so. I'm just happy enough that I can "read" SQL a lot better than in the past.

My schooling doesn't go back that far but it starts in the late 80s with QBASIC in Dos 6.0 compliments of my grandfather who had an original IBM PS/2 (when they cost as much as a car) because of his job (one of, if not the first EVER CAD administrator). Supplemented that with Pascal in high school in the early 90s. Like so many youth my age, I learned autoexec.bat and config.sys to get hardware drivers loaded into memory properly to run my cool videogames! Ironically, I would have gone into programming had the university curriculum where I was planning on attending not been based on COBOL! Of course, that was because this was now the late 90's not the 70's. So, engineering called, I answered and I've been sucked into doing programming type tasks in just about every job I've ever had!

 

[isladogs]

FWIW, the example app of mine that JMongi used was a major rewrite of an old app by David Crake (RIP) to allow it to work in new versions of Access including 64-bit.
Like most experienced developers, my forms are almost always bound but any exceptions to that are done for good reasons.

The form frmLogin is indeed unbound though the username combo is bound to the list of current users.
The form frmNewUser is completely unbound.
In both cases, that is the correct decision for the purpose of each form

Incidentally, for additional security, many people who use the password login app prefer to change the username combo to an unbound textbox which is exactly how my login forms are designed in all my own apps.

FWIW, I started programming in Fortran but I haven't used that in almost 50 years and can't remember it at all though I know BASIC was derived from it.

 

[JMongi]

@Pat Hartman - I actually agree in principle. 18 year old me, may have thought differently. But, he was cantankerous and probably would have been contrarian just to be contrarian. Also, I wasn't plugged in then, but I suspect that the late 90s was the pinnacle of COBOL as "old people" programming and object oriented programming was the shiny, new toy. Don't learn COBOL, no one will take you seriously. I think my life has turned out fine, of course. But, could have been a little different

 

[NauticalGent]

There are plenty who will thoroughly agree with that statement!

 

[The_Doc_Man]

Came back to this and - sorry - can't leave well enough alone. Pat, Access CAN twiddle bits as long as it has binary datatypes like BYTE, WORD, and LONG and as long as the AND, OR, XOR, and NOT operators exist, plus the ability to multiply or divide by powers of 2. OK, wouldn't be my first choice - but it IS doable.

 

[Cotswold]

Instead of using Public Variables in Access, I store variables on an always-open form with Visible=F and Locked=T.
I'll also store variables as the software runs on current open forms. I can then compare, or use them with other
similar named variables on any other open form without conflict. I can then ignore any issues arising with scope.
Using Access2019 now and done that from Access97 without any problems.