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

[DianeM]

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.

 

[Ofer Cohen]

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

Function GetActiveArea()
GetActiveArea = UserArea
End Function

 

[Klatuu]

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.

 

[DianeM]

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

Function GetActiveArea()
GetActiveArea = UserArea
End Function

Thanks so much ... that worked.

Cheers,
Diane