Problem with Function

[Mark A. Sam]

Hello,

In Access 2007 I created the following function in a form:

Public Function CompanyChoice()
'Used to filter lstChoices rowsource on form
CompanyChoice = TempVars("Company")
End Function

When I first tried to use it I got a message something like Subroutine or
Function undefined.

So I decompiled and compacted the database. Now I get a message:

Run-Time error '9': Subscript out of range.

This is in the immediate window while the function is open.

I thought that the form may be the problem, so I created a new form to fun
the function with the same problem.

If I use the Function as the criteria of a query (which is what I need to
do) I get a message:

Undefined function 'CompanyChoice' in expression.

But this in the Click event of a button on a form:

MsgBox CompanyChoice()

Returns the correct value without a problem.

Thanks for any help and God Bless,

Mark A. Sam

[Mark A. Sam]

I was going to use the function to filter the Rowsource of a listbox on the
form. I set the rowsource on the Open event of the form using the value of
Tempvars("Company").

[Klatuu]

To use a function in a query it has to be a Public function in a Standard
Module. Jet can't find it in a Form Module even if it is Public.
--
Dave Hargis, Microsoft Access MVP

[Mark A. Sam]

Thank you. Good point. I was running the query from code and that didn't
occur to me.

"Klatuu" <Kla...@discussions.microsoft.com> wrote in message
news:6BB3C087-7AEE-4F5F...@microsoft.com...

[José Ricardo Furlanetto]

"Mark A. Sam" <Mark...@EmEssEn.Com> escreveu na mensagem
news:uHmpbiGO...@TK2MSFTNGP06.phx.gbl...