make a constant that involves tying a variable into itself in Access 97 VBA

Posted on 2005-05-09
Last Modified: 2012-05-05
I'm trying the following to make a constant variable in Access 97 VBA...

Public Const sCPATH = "C:\Documents and Settings\" & fOSUserName & "\My Documents\EC"

...but I keep getting an error Compile error constant expression required:  fOSUsername is actually a function with the following:  How should I arrange the above and below code to make sCPATH a constant for my use.

Public Function fOSUserName() As String
On Error GoTo Err_fOSUserName

' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
        fOSUserName = vbNullString
    End If
    Exit Function
    Call Error_Action(Err, Err.Description, "modCPUName @ fOSUserName", Erl())
    Resume Exit_fOSUserName
End Function

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Question by:stephenlecomptejr
    LVL 65

    Accepted Solution

    If you're using a variable as part of its declaration, then it's not very constant, is it?  

    Likely what happens is that Access sets all constants to memory before you code sets a value in fOSUserName.

    I recommend using a global string variable instead, and only change it when you declare it on startup.  

    Global sPath as String

    ... somewhere in code...
    sPath = "C:\Documents and Settings\" & fOSUserName & "\My Documents\EC"
    LVL 1

    Author Comment

    thanks Jim.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now