Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query - Variables as Criteria (2003)

    I would like to create a Public Variable ("placeHolder as Integer") and be able to pass its value to the criteria on a Query. Is that possible? Thanks in advance for your usual help!

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

    Re: Query - Variables as Criteria (2003)

    Queries don't "know" about variables, so you must create a function that returns the value of the variable. For example:

    Public Function GetPlaceHolder() As Integer
    GetPlaceHolder = PlaceHolder
    End Function

    You can then use

    GetPlaceHolder()

    in the Criteria line of a query.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query - Variables as Criteria (2003)

    HansV ... placed this on a form:

    - - - - - - - - - - - - - - - - - - - - - - -
    Public Function GetPlaceHolderCS() As Integer
    GetPlaceHolderCS = placeHolderCS
    End Function

    Public placeHolderCS As Integer
    - - - - - - - - - - - - - - - - - - - - - - -

    Then I did the following:

    - - - - - - - - - - - - - - - - - - - - - - -
    Private Sub lstSearchByCS_DblClick(Cancel As Integer)

    placeHolderCS = Me.lstSearchByCS.Column(0) 'This actually gets the value I need.
    DoCmd.Close acForm, "frmCS_Name_CSSort"
    DoCmd.OpenForm "frmSubjectEditScreenFromCS"

    End Sub
    - - - - - - - - - - - - - - - - - - - - - - -
    Finally, entered GetPlaceHolderCS in the query's Criteria field.

    I'm getting an error message that says: "Undefined function 'GetPlaceHolderCS' in expression". Honestly, have no idea what's happening. I even tried '=GetPlaceHolderCS' and still get the same error message. Hope you can figure it out. Thanks!

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

    Re: Query - Variables as Criteria (2003)

    You should put the code

    Public Function GetPlaceHolderCS() As Integer
    GetPlaceHolderCS = placeHolderCS
    End Function

    Public placeHolderCS As Integer

    in a standard module, not in a form module.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query - Variables as Criteria (2003)

    HansV ... placed the following code in a standard module ...

    - - - - - - - - - - - - - - - - - - - - - - - - - -
    Option Compare Database

    Public Function GetPlaceHolderCS() As Integer
    GetPlaceHolderCS = placeHolderCS
    End Function

    Option Explicit

    Public placeHolderCS As Integer
    - - - - - - - - - - - - - - - - - - - - - - - - - -

    However, when I execute the double click action in a form, I get the following error ...

    - - - - - - - - - - - - - - - - - - - - - - - - - -
    only comments may appear after end function
    - - - - - - - - - - - - - - - - - - - - - - - - - -

    Tried moving the Option Explicit statement after the first option statement but got the same result. I'm sure I'm doing something wrong. Hope you can help again ... Thanks!

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

    Re: Query - Variables as Criteria (2003)

    Sorry, should have seen that.

    - The line Option Explicit must be at the very top of the module, immediately above or below Option Compare Database.
    - Declarations such as Public placeHolderCS As Integer must be below all Option ... lines, and above all Subs and Functions.

    So the code should be in this order:

    Option Compare Database
    Option Explicit

    Public placeHolderCS As Integer

    Public Function GetPlaceHolderCS() As Integer
    GetPlaceHolderCS = placeHolderCS
    End Function

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sunrise, Florida
    Posts
    324
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query - Variables as Criteria (2003)

    Got it! The Options go first and the Public Variable declaration goes before the code for the Function. Thanks a lot, HansV!

Posting Permissions

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