it is possible to use a dlookup on a public const in vba?

Question

I have about 10 forms in my access and in all of them I need to put some basic information like, date and user, to do that I use the same code on all forms, I'm searching a form to set this information and just call

vba now

Private sub Form_Load()

Dim userId As String
userId = Environ("Username")

Dim uSer As String
uSer = DLookup("User", "Adm_User", "UserId='" & Environ$("Username") & "'")

Dim dtNow as string
dtNow = Format(Now, "dd/mm/yyyy hh:mm")

me.text_userId.Caption = userId
me.text_uSer.Caption = uSer
me.text_dtNow.Caption = dtNow

vba that i want

Public Const userId As String = Environ("Username")
Public Const uSer As Variant = DLookup("User", "Adm_User", "UserId='" & Environ$("Username") & "'")
Public Const dtNow As String = Format(Now, "dd/mm/yyyy hh:mm")

Private sub Form_Load()

me.text_userId.Caption = userId
me.text_uSer.Caption = uSer
me.text_dtNow.Caption = dtNow

`Public userId As String` etc as Global variables, and set these in the `Form_Load` method — Tim Williams, Dec 20 '22 at 18:28
Did you try it? Did you put those Const declarations in a general module? I expect it won't work and will have to use global variable or TempVars. — June7, Dec 20 '22 at 18:47
If I use Public userId As String in the Global, I have to inform in all forms the value, there is a way to just call userId ? — Tejota, Dec 20 '22 at 18:51
This isn't possible through a `Const`. As said in your previous question, you can use a predeclared class object instead — Erik A, Dec 20 '22 at 18:52
Yes, i put on general module, but on open the form apresent an error: Compile error: Constant expression required — Tejota, Dec 20 '22 at 18:53
It's called a constant for a reason. Can't be set with a dynamic input, only static value. Use Global, TempVars, or class object. Global and TempVars can be set from one form and then are available to any. Issue with Global is loses value with unhandled runtime error. I have never used custom class object. — June7, Dec 20 '22 at 19:04
Change the constants into functions returning the desired type. Then just call them when the form loads or use them as the control source. — Kostas K., Dec 20 '22 at 19:12

score 1 · Accepted Answer · answered Dec 21 '22 at 07:05

The simple method is to keep a set of functions in a "Common" module:

Public Function GetUserId() As String

    Dim UserId  As String

    UserId = Environ("Username")

    GetUserId = UserId

End Function


Public Function GetUser() As String

    Static User As String

    If User = "" Then 
        User = Nz(DLookup("User", "Adm_User", "UserId = '" & GetUserId & "'"))
    End If

    GetUser = User

End Function


Public Function FormatNow() As String

    Dim Text    As String

    Text = Format(Now, "dd/mm/yyyy hh:nn")

    FormatNow = Text

End Function

Then call these at the form load:

Private Sub Form_Load()

    Me!text_userId.Caption = GetUserId
    Me!text_user.Caption = GetUser
    Me!text_dtNow.Caption = FormatNow

End Sub

it is possible to use a dlookup on a public const in vba?

1 Answers1