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.

 

[Pat Hartman]

These variables are used in direct SQL statements to insert the new users into the appropriate table.

Sounds like you are not using bound forms. I know that isn't your question but Access is RAD tool and avoiding the use of bound forms gives up the biggest benefit of using Access.

As the others have pointed out - Variables have Scope and Scope is hierarchical so make sure you don't cause conflicts. I would go further than that. I very rarely define global variables and that is one of the reasons I don't use TempVars. I have one constant I define in a standard module and I name it QUOTE. I use it when building strings that include embedded quotes. It makes the string easier to interpret.

strWHERE = " WHERE CustomerName = " & QUOTE & Me.txtCustomerName & QUOTE & " AND CityName = " & QUOTE & Me.txtCityName & QUOTE

I use a couple of module level variables that are set and used in different event procedures. For example, if I want to force the user to press my SAVE button prior to saving, that requires a global variable that is set by the Current event and the click event of the SAVE and then checked by the form's BeforeUpdate event and reset there. Otherwise, variables are defined within a procedure or passed as arguments.

This goes back to my earliest years when I was learning structured programming using COBOL in the early 70's. The buzzwords of the time were Coupling and Cohesion and it's too bad that they have gone out of fashion. Coupling refers to the interactions between objects. The Ideal is "Black Box" which means everything inside the box is entirely dependent on whatever arguments are passed in AND the arguments are passed by value so that the box cannot affect anything outside of itself. Cohesion refers to the relevance of all procedures within the application/module. You wouldn't create one application that produced your weekly payroll and include the process to send out your weekly invoices just because the two outputs were produced on the same day. We all know that having one module/procedure to do multiple things is the road to perdition and doesn't save anything.

 

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

 

[Pat Hartman]

Don't look down on COBOL It's like disparaging a Volkswagen because you have an F150. Use the tool that is best for the job. COBOL is excellent for creating large data centric business applications that don't require sophisticated statistical calculations or constant interaction with humans. However, COBOL allows you to call subroutines to do fancy stuff so you can still do the mundane stuff with COBOL. It would still be my first choice today, 50 years later if I were developing data handling applications for the IRS or the phone company or Amazon. If you don't need to twiddle bits, you don't need to use a development platform that twiddles bits even if that tool is the best thing since sliced bread. You need to use the tool that is designed to do the job that needs doing rather than the Swiss Army Knife that will do anything/everything but nothing well. That's why I still have an actual, wait for it - camera.

My husband, the electrical engineer, never worked as an engineer either. He always worked as a programmer.

 

[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

 

[Pat Hartman]

I wasn't suggesting that you were wrong to not learn COBOL. Only that it is important to understand what your tools do and to use the best tool for the project. I left Corporate America in the 90's because the inmates were running the asylum. The push to use C or other languages rather than COBOL was a mistake. Just because something is old doesn't mean you should discard it in favor of something new and shiny. If it still works, there is no need to replace it. In fact I strongly prefer the way COBOL works in a lot of cases and especially because there is only one way to do anything so it is logically far simpler and less prone to the introduction of bugs as enhancements are made to the language. One of the things that did change over the years I used COBOL was how we stored data. We went from ISAM files to VSAM (better direct access capability) to IMS (hierarchical database) to DB2 (relational database still very much in use today, in fact, it was the first relational database I ever used from Access and one of the reasons I switched to Access) The one thing that got added after I stopped taking COBOL assignments was functions. The use of functions can greatly simplify procedural logic.

Access is really old in technical years and yet it is still the premier desktop database on the market. It still does what it was designed to do and it still does it very well. It doesn't do web pages, it doesn't do graphics, it doesn't twiddle bits but that doesn't make it an inferior tool. My camera is far better at photography than my Swiss Army Knife of a "smart" phone and Access is far superior to all of its competitors.

 

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

 

[Pat Hartman]

"Twiddle bits" is a figure of speech. We used it when we were talking about getting closer to the hardware than COBOL provided for. VBA and COBOL are equivalent in what you can do with them.

@Cotswold \I also frequently use a hidden form to store "variables". It is great for testing because you can make the form visible and modify the variables to control test paths. Much easier than doing it in the immediate window.