Help w/ static variable in a query

[Guest]

I'm having a little trouble with the function below. When use it in a query,
the query only accesses the function once and returns the one value for all
of records. How do I get it to call the function for each record and return
a counter? BTW, I reset the static variable before I call the function.

Thanks,
Chris

Public Function OrderLineCounterA()

Static lngLineCounterA As Long

If lngLineCounterA < lngCounterStart Then lngLineCounterA =
lngCounterStart

lngLineCounterA = lngLineCounterA + 1

OrderLineCounterA = lngLineCounterA

End Function

 

[Guest]

Well, its the first time I noticed that.
The function will run only once in the query incase you are not passing any
value to it.

Passing a parameter to the function of one of the fields in the query will
run the function every record, even if you are not using it in the function

Public Function OrderLineCounterA(MyParam As Variant)

Static lngLineCounterA As Long

If lngLineCounterA < lngCounterStart Then lngLineCounterA =
lngCounterStart

lngLineCounterA = lngLineCounterA + 1

OrderLineCounterA = lngLineCounterA

End Function
====================
In the query:
Select TableName.* , OrderLineCounterA([SomeFieldName]) As Something From
TableName