Constants and Variables (1 Viewer)

Thales750

Formerly Jsanders
Local time
Today, 16:36
Joined
Dec 20, 2007
Messages
2,061
Hello All,
I have a Loader written by one of our old alumni, KenHigg.


The loader was originally written to Load a new Front-end into a local drive. Enter a new world where my Users are logging into a Remote Desktop with a shared C: Drive.
So the loader will be on the C: Drive, but the files it copies will be on the User\Drive.

I can verify the existence, and make directories, in the User Drive with the following code. This code runs from the On Load Event of the Display Form I added.

Code:
Private Sub Form_Load()
Dim strExistinPath As String
On Error Resume Next   
        strExistinPath = "C:\Users\" & getComputerUser & "\TrecoraClient"   
     If Dir(strExistinPath, vbDirectory) = "" Then    'MkDir strDirectoryPath
        MkDir strExistinPath
    End If
DoCmd.RunMacro "autoexec1"

DoCmd.Close
End Sub

Once the form closes, it runs the Autoexec1 Macro that was previously named Autoexec, so it would open at launch. The Display Form loads now and then the macro Runs. As you can see. This Macro calls a Function

In the Module Constants are given a value.

Both of the following Codes Works:

Code:
'Const cstrClientFEPath = "C:\TrecoraClient\"

'Const cstrClientFEPath = "C:\Users\jsanders\TrecoraClient\"


These do not:
Code:
'Const cstrClientFEPath = "C:\Users\" & Environ("username") & "\TrecoraClient\"
Const cstrClientFEPath = "C:\Users\" & getComputerUser & "\TrecoraClient"

The get User Function is defined as:

Code:
Public Function getComputerUser()
    pubComputerUser = Environ("username")
    getComputerUser = pubComputerUser
End Function

Any Ideas?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 13:36
Joined
Oct 29, 2018
Messages
21,358
Sorry. Any ideas about what? I may have missed the question or description of the problem.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:36
Joined
Feb 19, 2002
Messages
42,975
I don't know. What value is the function returning? It may not be retrieving the correct value.

I use a batch file rather than a loading app. This is the one I use for a Citrix installation. I think RD might be similar.

md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" %USERPROFILE%\DwgLog
%USERPROFILE%\DwgLog\DrawingLog.accdb
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:36
Joined
Oct 29, 2018
Messages
21,358
Both of the following Codes Works:

Code:
'Const cstrClientFEPath = "C:\TrecoraClient\"

'Const cstrClientFEPath = "C:\Users\jsanders\TrecoraClient\"


These do not:
Code:
'Const cstrClientFEPath = "C:\Users\" & Environ("username") & "\TrecoraClient\"
Const cstrClientFEPath = "C:\Users\" & getComputerUser & "\TrecoraClient"

Any Ideas?
Oh, I think I get it now...

When you declare a constant, you have to use a literal value. After all, that's what constant mean, right? You can't use a variable to declare a constant, as that would defeat the purpose.
 

isladogs

MVP / VIP
Local time
Today, 20:36
Joined
Jan 14, 2017
Messages
18,186
Constants are just that. They require a constant expression so a variable won't work
For that reason both of these will cause a compile error
Code:
Const dteDate = Date
Const StrUserName = Environ("UserName")
However, it is easy enough to work around this. For example
a) Declare a public variable and assign its value in a function
b) use a TempVar
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:36
Joined
Feb 28, 2001
Messages
27,001
There is an issue with string constants. See this thread:

 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:36
Joined
May 7, 2009
Messages
19,169
so a variable won't work
it will, but not with Functions.

Code:
Private Sub t()
Const j As Integer = 100
Const m = j
Debug.Print m
End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:36
Joined
Feb 28, 2001
Messages
27,001
There are various cases where constants won't work correctly. Functions may well be one, particularly if the function's argument is ByRef. Never tried the ByVal case. I know that expressions that try to build a constant in an expression from another symbolically named constant will often fail - but will work for literal constants.

I cannot prove this because of course we don't have visibility of the generated code for VBA, but I suspect that the constants are stored in a read-only area, so anything that requires read-write access would fail. Then again, since we can't see inside the code, that's just a guess.
 

isladogs

MVP / VIP
Local time
Today, 20:36
Joined
Jan 14, 2017
Messages
18,186
A constant based on another constant will obviously work ... precisely because it isn't a variable
For example

Code:
Const j = 100
Const m = 2 * j

Sub test()
   Debug.Print j
   Debug.Print m
End Sub
 

Isaac

Lifelong Learner
Local time
Today, 13:36
Joined
Mar 14, 2017
Messages
8,738
Back to the original problem: Just don't use a Constant. Use a variable.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:36
Joined
Feb 28, 2001
Messages
27,001
A constant based on another constant will obviously work ... precisely because it isn't a variable
For example

Code:
Const j = 100
Const m = 2 * j

Sub test()
   Debug.Print j
   Debug.Print m
End Sub
Yeah, but now try to build a string constant that way from another string constant.
 

isladogs

MVP / VIP
Local time
Today, 20:36
Joined
Jan 14, 2017
Messages
18,186
Yeah, but now try to build a string constant that way from another string constant.
Not sure I see your point. Do you mean something like this?

1625740241534.png

It also works if you use + instead of & :
Code:
Const y = x + "World Forums"

So whilst both that and the previous example I gave in post #11 do work, I wouldn't ever use this approach in a real database
 

Thales750

Formerly Jsanders
Local time
Today, 16:36
Joined
Dec 20, 2007
Messages
2,061
I've never used a constant in 25 years of Access Development. Why did Ken use them in this Loader?

Here is a link to Ken's Loader, Iv'e used it for 12 years now, hundreds of user stay updated to the latest version.
Ken's Example Files.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:36
Joined
Feb 28, 2001
Messages
27,001
Not sure I see your point. Do you mean something like this?

View attachment 92876
It also works if you use + instead of & :
Code:
Const y = x + "World Forums"

So whilst both that and the previous example I gave in post #11 do work, I wouldn't ever use this approach in a real database
Some combination of string constant usage has caused errors, as I reported in my referenced post, which in turn reported other errors. If this has been somehow fixed in a newer version of Access, great.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:36
Joined
May 21, 2018
Messages
8,463
I've never used a constant in 25 years of Access Development. Why did Ken use them in this Loader?
Same reason anyone uses a constant. Makes code more readable and easier to update the code. He clearly specifies if you change the constants to your paths then it works. Imagine a constant not used and instead that path is in the code many times. It would be difficult to customize the code. Change in one place and changes everywhere.
 

isladogs

MVP / VIP
Local time
Today, 20:36
Joined
Jan 14, 2017
Messages
18,186
Some combination of string constant usage has caused errors, as I reported in my referenced post, which in turn reported other errors. If this has been somehow fixed in a newer version of Access, great.
I hadn't noticed your earlier post, nor had I read the linked thread previously.

Anyway, I have now checked the code in post #1 of that thread in both Access 365 & Access 2010.
Nobody had commented on it, but the last line of the code snippet is wrong in both post #1 and your modified version in post #10.
It should of course be End Sub not Exit Sub.

Once I had corrected the last line, as I expected that code worked perfectly in both versions of Access
 

Thales750

Formerly Jsanders
Local time
Today, 16:36
Joined
Dec 20, 2007
Messages
2,061
Same reason anyone uses a constant. Makes code more readable and easier to update the code. He clearly specifies if you change the constants to your paths then it works. Imagine a constant not used and instead that path is in the code many times. It would be difficult to customize the code. Change in one place and changes everywhere.
I've known that it works for 12 years, thank you. It does not work with variables, as several people have previously stated.

Variable make code easier to read, why use a constant instead of a variable?

When exactly is a constant better than a variable?
 

Users who are viewing this thread

Top Bottom