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

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

CodePudding user response：

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