Module level variable declaration.

[davegri]

I totally agree that you cannot set variable values in a form/report module outside a procedure
However, I do routinely set global variable values in a table e.g.

Colin, this is a perfect example where I like to create a User Defined Type. At the BOJ (perhaps via the autoexec macro call to function startup()):
In a code module:

Option Compare Database
Option Explicit


Type SchoolConstants
   SMTPUseSSL as string
   SPSManagerEmail as string
   SPSEmailSupport as string
   SchoolEmail as string
   EmailUseOutlook as boolean
   WebUpdateURL as string
   ADUserName as string
   ADUserPassword as string
          .
          .
          .
End Type


Global School As SchoolConstants


'load the UDT values
Public function Startup()
    Dim rs as DAO.Recordset
    'Table ConstantID contains only one record
    Set rs = Currentdb.openrecordset("ConstantID", dbOpenSnapshot)
    With rs
        .movelast
        .movefirst
        School.SMTPUseSSL = !strSMTPUseSSL
        School.SPSManagerEmail = !strSPSManagerEmail
        School.SPSEmailSupport = !strSPSEmailSupport
        School.SchoolEmail = !strSchoolEmail
        School.EmailUseOutlook = !strEmailUseOutlook
        School.WebUpdateURL = !strWebUpdateURL
        School.ADUserName = !strADUserName
        School.ADUserPassword = !strADUserPassword
            .
            .
            .
    end with
    set rs = nothing
End Function

Then, anywhere in code you can refer to any of the constants such as:


If School.ADUserName = "Smith" then ...




The School. prefix even provides intellisense for allowable completion.

[isladogs]

Hi Dave
Yes I agree with that as another valid approach and these days often use it myself.
However, many of my commercial apps were originally developed around 15 years ago when I made little use of class modules.

[davegri]

However, many of my commercial apps were originally developed around 15 years ago when I made little use of class modules.

Yes, I know what you mean. I use tools today that were mysteries a few years ago. This forum has broadened my knowledge and inspired new explorations.

[accesstos]

Colin, this is a perfect example where I like to create a User Defined Type.

I beg to differ.

The mainly reason of use of objects is the creation of multiple instances of an object with different properties at run time. When there is no reason to create a second instance of UDT SchoolConstants, there is no reason to define it at all. In case of tblProgramConstants, Ι believe that, a public Collection it’s the best choice.
For example:

Public ProgramConstants As Collection

Public Function Startup()
    Dim rs As DAO.Recordset

    Set ProgramConstants = New Collection
    Set rs = CurrentDb.OpenRecordset("tblProgramConstants")
    With rs
        While Not .EOF
            If Not IsNull(!Alias) Then
                ProgramConstants.Add !Value.Value, CStr(!Alias)
            End If
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
    Debug.Print ProgramConstants("strSchoolEmail")
    'Destroy ProgramConstants because this is a demonstration.
    Set ProgramConstants = Nothing
End Function

Cheers,
John

[davegri]

I beg to differ.

To each his own. There are 27 records in the table. The collection requires you to type in exactly the Alias for the desired value. The UDT will supply the entire 27 aliases in the dropdown to choose from.

[isladogs]

To each his own. There are 27 records in the table. The collection requires you to type in exactly the Alias for the desired value. The UDT will supply the entire 27 aliases in the dropdown to choose from.

If you are referring to the table in my screenshot, there are actually almost 60 records. Another larger database for schools has almost 160 records on the equivalent table.
I think its arguable whether your method or John's is less work or more robust...or more easily transferrable.
But all methods work so each developer can use whichever they feel most comfortable with

[accesstos]

To each his own. There are 27 records in the table. The collection requires you to type in exactly the Alias for the desired value. The UDT will supply the entire 27 aliases in the dropdown to choose from.

Hi Dave!

I agree with the useful feature of Intellisense but, for this case, the benefits of the UDT ends here. Your code in StartUp() demands a table structured as spreedsheet with 27 (Colin says up to 160) fields rather than records and you can’t refers to the members of the UDT in loops, so, you need also a large and boring statement of School.MemebrName= !strVarName, and, finaly, you have only one instance of this object for each session.

The purpose of the tblProgramConstants table (I'd prefer tblProgramParameters) is to give to the user the ability to change the parameters of a completed project via the UI, which is a brilliant idea. On the programming level, the "Collection way" simulates the Environ() function, and the notation CollectionName(“MemberName”) is very familiar to programmers. In some cases, this notation could be a big advantage which lacks of VBA: the indirectly reference to a variable.
Imagine this scenario: You have passed all program parameters in a collection named Param and you have a generic procedure that loads the defaults of a form of a multilanguage application:

Public Sub LoadFormDefaults(frm As Form)
    Dim ctrl As Control
    Dim strParam As String

    On Error Resume Next
    If Not Param Is Nothing Then
        For Each ctrl In frm.Controls
            'Set the papameter name to the name of the control without its prefix ("txt","cbo", "lbl" etc).
            'E.g. the textbox [txtUserName] takes the value of Param("UserName").
            'The caption of the label [lblUserName] takes the value of Param("UserNameCaption").
            strParam = Mid(ctrl.Name, 4)
            Select Case TypeName(ctrl)
                Case "TextBox", "ListBox", "ComboBox", "CheckBox", "OptionGroup"    ',...
                    ctrl = Param(strParam)                      'Pass the value of the parameter to control
                Case "Label"
                    ctrl.Caption = Param(strParam & "Caption")    'Set the caption of the label
                Case Else
            End Select
        Next ctrl
        'Set the caption of the form
        If Left(frm.Name, 4) = "sfrm" Then
            strParam = Mid(frm.Name, 5)
        ElseIf Left(frm.Name, 3) = "frm" Then
            strParam = Mid(frm.Name, 4)
        Else
            strParam = ""
        End If
        If Len(strParam) Then
            strParam = strParam & "Caption"
            frm.Caption = Param(strParam)
        End If
        On Error GoTo 0
    End If
End Sub

In any form’s Load event could exists a line of code like this:

Private Sub Form_Load()
    LoadFormDefaults Me
    '...
    '...

Imagine how many lines of code would save with such a practice like this and how flexible is.
An inverse procedure in Unload event of the form, would change the values of the collection (defaults) for the current session, according to user’s preferences.

But all methods work so each developer can use whichever they feel most comfortable with

Sure Colin, that’s generally true, but, in technique, some issues are beyond of our personal preferences.

[bubai]

You can set a constant outside of the procedure.

Thank mate. A useful bit of information.

[bubai]

Declaring a variable is one thing. Assigning a value to it is another. I saw nothing in that link that says you can assign outside of a procedure.
I'm saying you cannot.

Sorry mate. I just misinterpreted your reply.

[bubai]

Just set the value in the procedure

I was under the impression that value of variables are sometimes automatically changed by VBA. And I have seen this happen. That's why I was thinking of assigning them outside the procedure. But it seems that's illegal.

Where there is no error the code automatically moves to the Exit_BeforeUpdate section where you are setting yptr to False. Where an error occurs, the Resume Exit_BeforeUpdate line also ensures that yptr is set to False.

Both Exit_BeforeUpdate and Err_BeforeUpdate are tags. How come they run without an error?

[Micron]

If a variable is unexpectedly altered by code (if that's what you mean) then if you're passing it to a sub or function and don't want that to happen, pass it ByVal not ByRef. If you're not passing it and it's being changed unexpectedly, that's what you call a bug in your code. Even if you could set a variable value outside of a procedure, that is no guarantee that unexpected altering cannot take place. If you need to have a module level variable in a form set before anything else happens, set it in the load event IMO.

Both Exit_BeforeUpdate and Err_BeforeUpdate are tags. How come they run without an error?

I don't understand that question. Those lines are line labels, yes? They don't "run". If you use GoTo or Resume 'line label' code flow is directed to the first code line that follows the line label. If said line doesn't generate an error, then certainly that line will run without raising an error.

[Welshgasman]

@davegri

May I ask why you use movelast and movefirst when there is only one record?

Colin, this is a perfect example where I like to create a User Defined Type. At the BOJ (perhaps via the autoexec macro call to function startup()):
In a code module:

Option Compare Database
Option Explicit


Type SchoolConstants
   SMTPUseSSL as string
   SPSManagerEmail as string
   SPSEmailSupport as string
   SchoolEmail as string
   EmailUseOutlook as boolean
   WebUpdateURL as string
   ADUserName as string
   ADUserPassword as string
          .
          .
          .
End Type


Global School As SchoolConstants


'load the UDT values
Public function Startup()
    Dim rs as DAO.Recordset
    'Table ConstantID contains only one record
    Set rs = Currentdb.openrecordset("ConstantID", dbOpenSnapshot)
    With rs
        .movelast
        .movefirst
        School.SMTPUseSSL = !strSMTPUseSSL
        School.SPSManagerEmail = !strSPSManagerEmail
        School.SPSEmailSupport = !strSPSEmailSupport
        School.SchoolEmail = !strSchoolEmail
        School.EmailUseOutlook = !strEmailUseOutlook
        School.WebUpdateURL = !strWebUpdateURL
        School.ADUserName = !strADUserName
        School.ADUserPassword = !strADUserPassword
            .
            .
            .
    end with
    set rs = nothing
End Function

Then, anywhere in code you can refer to any of the constants such as:


If School.ADUserName = "Smith" then ...




The School. prefix even provides intellisense for allowable completion.

[davegri]

May I ask why you use movelast and movefirst when there is only one record?

Habit. No harm, no foul.

[Welshgasman]

Habit. No harm, no foul.

Thanks. I just thought there might be some subtle reason I was not aware of.

[bubai]

If you need to have a module level variable in a form set before anything else happens, set it in the load event IMO.

What is an IMO? Do you mean on load event?

Those lines are line labels, yes? They don't "run". If you use GoTo or Resume 'line label' code flow is directed to the first code line that follows the line label. If said line doesn't generate an error, then certainly that line will run without raising an error.

I forgot the name of the term ie Label. My understanding is, labels can be directed to by meeting some criteria or on errors. But in case of the procedure in question, all of the code should be part of the label.

yctr = False  'OMIT THIS LINE IS SUPERFLUOUS
Exit_BeforeUpdate: yctr = False
 Exit Sub
Err_BeforeUpdate:
 MsgBox Err.Number & " " & Err.Description
 Resume Exit_BeforeUpdate
End Sub

And since these labels here are directed to only on error, (because there is no conditional criteria to goto a label other than error), how come the resume key word will be executed/(Above code which falls under labels I suppose) if there were no error?