Using a Public Variable in a Query - Passing Through a Function

I have read many posts (and found code in a book) referencing how to
used a "dummy" function to call a variable into a query. But it's not
working for me. Can someone tell me what I'm doing wrong?

In a Public module (starting with Option Compare Database), I have
defined a variable called UserArea:


    Public UserArea As Long


I have then written a function to return the value of UserArea


     Function GetActiveArea()


         Dim ActiveArea As Long
         ActiveArea = UserArea


     End Function


Then, in my Query design view, I have the criteria line for the
AreaID
field reading: "GetActiveArea()" (without the quotes). Note that I've
double-checked that my AreaID field is defined in the table as a long
integer.


If I type "1" into the criteria for Area ID, I see all the Area 1
records. If I step through my GetActiveArea() function, it shows that
ActiveArea = 1 before I process the End Function line ... but when I
view my query results, I get no records at all.


Any thoughts are most helpful!


Cheers,
Diane

P.S. I also posted this to the Access VBA programming group, but there
has been no activity on that group, so I thought I'd cross-post it
here in hopes of a response.

0
DianeM
7/12/2007 1:37:07 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1706 Views

Similar Articles

[PageSpeed] 56

You need to apply the value to the function so you can use the function in 
the query

 Function GetActiveArea()
        GetActiveArea = UserArea
 End Function

-- 
Good Luck
BS"D


"DianeM" wrote:

> I have read many posts (and found code in a book) referencing how to
> used a "dummy" function to call a variable into a query. But it's not
> working for me. Can someone tell me what I'm doing wrong?
> 
> In a Public module (starting with Option Compare Database), I have
> defined a variable called UserArea:
> 
> 
>     Public UserArea As Long
> 
> 
> I have then written a function to return the value of UserArea
> 
> 
>      Function GetActiveArea()
> 
> 
>          Dim ActiveArea As Long
>          ActiveArea = UserArea
> 
> 
>      End Function
> 
> 
> Then, in my Query design view, I have the criteria line for the
> AreaID
> field reading: "GetActiveArea()" (without the quotes). Note that I've
> double-checked that my AreaID field is defined in the table as a long
> integer.
> 
> 
> If I type "1" into the criteria for Area ID, I see all the Area 1
> records. If I step through my GetActiveArea() function, it shows that
> ActiveArea = 1 before I process the End Function line ... but when I
> view my query results, I get no records at all.
> 
> 
> Any thoughts are most helpful!
> 
> 
> Cheers,
> Diane
> 
> P.S. I also posted this to the Access VBA programming group, but there
> has been no activity on that group, so I thought I'd cross-post it
> here in hopes of a response.
> 
> 
0
Utf
7/12/2007 1:48:04 PM
How a function is used in a query will determine whether and when it is 
called.  As written, it will execute one time at the beginning of the query.  
For it to execute for every row, you need to pass a field in the source data 
to the function.  That doesn't mean the receiving query has to use it, it 
just has to be included so the query will call it.
-- 
Dave Hargis, Microsoft Access MVP


"DianeM" wrote:

> I have read many posts (and found code in a book) referencing how to
> used a "dummy" function to call a variable into a query. But it's not
> working for me. Can someone tell me what I'm doing wrong?
> 
> In a Public module (starting with Option Compare Database), I have
> defined a variable called UserArea:
> 
> 
>     Public UserArea As Long
> 
> 
> I have then written a function to return the value of UserArea
> 
> 
>      Function GetActiveArea()
> 
> 
>          Dim ActiveArea As Long
>          ActiveArea = UserArea
> 
> 
>      End Function
> 
> 
> Then, in my Query design view, I have the criteria line for the
> AreaID
> field reading: "GetActiveArea()" (without the quotes). Note that I've
> double-checked that my AreaID field is defined in the table as a long
> integer.
> 
> 
> If I type "1" into the criteria for Area ID, I see all the Area 1
> records. If I step through my GetActiveArea() function, it shows that
> ActiveArea = 1 before I process the End Function line ... but when I
> view my query results, I get no records at all.
> 
> 
> Any thoughts are most helpful!
> 
> 
> Cheers,
> Diane
> 
> P.S. I also posted this to the Access VBA programming group, but there
> has been no activity on that group, so I thought I'd cross-post it
> here in hopes of a response.
> 
> 
0
Utf
7/12/2007 2:22:02 PM
On Jul 12, 8:48 am, Ofer Cohen <OferCo...@discussions.microsoft.com>
wrote:
> You need to apply the value to the function so you can use the function in
> the query
>
>  Function GetActiveArea()
>         GetActiveArea = UserArea
>  End Function
>
> --
> Good Luck
> BS"D

Thanks so much ... that worked.

Cheers,
Diane

0
DianeM
7/12/2007 3:42:44 PM
Reply:

Similar Artilces: