Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to make a constant or global variable (Access 2000)

    In different expression i use one and same conditione namely

    Dim city As Long
    city = Forms![FOrderInformation]![office] - 1

    I do not want to refer to this condition many times and wanted to make this condition as Constant like that"

    Publiv Const city As Long = Forms![FOrderInformation]![office] - 1

    However i received the message " Constant expression required "


    Since i cannot make it a constant, is there any other way to refer to the word city in all my expressions like the folowing
    & " WHERE (((products.branch" & city & ") > 0)) " &

    Is it possible to express the city as a constant or global variable?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: how to make a constant or global variable (Access 2000)

    You must use a variable for this. A variable can be declared at different levels:
    <UL><LI>Within a procedure or function. For example:

    Sub Test()
    Dim strSQL As String
    ...
    End Sub

    The variable strSQL is "known" only within the procedure Test. You can't refer to it in other procedures. You can declare a variable strSQL within several procedures; they will be independent of each other.

    A variable declared with Dim will be initialized each time the procedure is executed. If you want the variable to keep its value as long as the module is loaded, declare it with Static:

    Sub Test()
    Static lngCount As Long
    lngcount = lngCount + 1
    End Sub

    The value of lngCount will be increased by 1 each time this procedure is executed.

    <LI>As a private variable at module level. You must put the declaration at the beginning of the module, before all procedures and functions. For example:

    Option Explicit

    Private strSQL As String

    Sub Test()
    strSQL = "..."
    End Sub

    By declaring a variable as private, it is available to all procedures within the module. If you change its value in one procedure, all other procedures within the same module will see the change. The variable is invisible to procedures in other modules. You can declare a private variable of the same name in several modules.

    <LI>A public variable at module level. You must put the declaration at the beginning of the module, before all procedures and functions. For example:

    Option Explicit

    Public strSQL As String

    Sub Test()
    strSQL = "..."
    End Sub

    By declaring a variable as public, it is available to all procedures within all modules. If you change its value in one procedure, all other procedures will see the change. You can't declare more than one public variable with the same name.[/list]Look up Variables, Scope in the help index for more information.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: how to make a constant or global variable (Access 2000)

    You could create a public function fncity as follows:

    Public function fncity() as long
    fncity = Forms![FOrderInformation]![office] - 1
    end function

    You would still need to Dim city as long each time you want a city, but you could set its value by using:
    city = fncity()

    You probably should add some error handling code to the function, in particular to check that the form is loaded.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •