Calling a function in a Form module from a Query - is this possible

  • Thread starter Thread starter Peter R. Fletcher
  • Start date Start date
P

Peter R. Fletcher

I have been trying, in the process of calculating the value of a
column in a query, to call a function which was (for good and
sufficient reasons) defined in VBA code in a Form Module in my
application. It was defined as Public in the Form Module, it was duly
accessible and worked (when the Form was open) if called from VBA
elsewhere in the application, and the Form was open (minimised) when I
was trying to design and run the Query.

The first problem was that the Build Wizard for the calculated column
did not find the Public Function as an attribute of the Form. The
second problem was that I could not, using any syntax I could think of
(Forms!frmMyForm.MyFunction(), Form_frmMyForm.MyFunction(), etc.)
entered manually, persuade the query, when run, to recognise the
function. Is this something that one cannot do, or am I simply being
stupid?

I have now developed an inelegant but effective workaround which does
what I was trying to do by handing off the Form data that the Function
uses (effectively an array of Keys) to code in a General Module and
then having the Query call it there, but I would really like to be
able to call a Function in a Form module.
 
Peter R. Fletcher said:
I have been trying, in the process of calculating the value of a
column in a query, to call a function which was (for good and
sufficient reasons) defined in VBA code in a Form Module in my
application. It was defined as Public in the Form Module, it was duly
accessible and worked (when the Form was open) if called from VBA
elsewhere in the application, and the Form was open (minimised) when I
was trying to design and run the Query.

The first problem was that the Build Wizard for the calculated column
did not find the Public Function as an attribute of the Form. The
second problem was that I could not, using any syntax I could think of
(Forms!frmMyForm.MyFunction(), Form_frmMyForm.MyFunction(), etc.)
entered manually, persuade the query, when run, to recognise the
function. Is this something that one cannot do, or am I simply being
stupid?

I have now developed an inelegant but effective workaround which does
what I was trying to do by handing off the Form data that the Function
uses (effectively an array of Keys) to code in a General Module and
then having the Query call it there, but I would really like to be
able to call a Function in a Form module.
Click to expand...

It may not be very efficient, but you could do something like this:

SELECT
tblMyTable.MyField,
Eval("Forms!MyForm.MyFormFunction(" & [MyField] & ")") AS Result
FROM tblMyTable;
 
I'm glad it wasn't just me being stupid, though I'm not sure that I
see why you shouldn't be able to do this.
 
Peter R. Fletcher said:
I have been trying, in the process of calculating the value of a
column in a query, to call a function which was (for good and
sufficient reasons) defined in VBA code in a Form Module in my
application. It was defined as Public in the Form Module, it was duly
accessible and worked (when the Form was open) if called from VBA
elsewhere in the application, and the Form was open (minimised) when I
was trying to design and run the Query.

The first problem was that the Build Wizard for the calculated column
did not find the Public Function as an attribute of the Form. The
second problem was that I could not, using any syntax I could think of
(Forms!frmMyForm.MyFunction(), Form_frmMyForm.MyFunction(), etc.)
entered manually, persuade the query, when run, to recognise the
function. Is this something that one cannot do, or am I simply being
stupid?

I have now developed an inelegant but effective workaround which does
what I was trying to do by handing off the Form data that the Function
uses (effectively an array of Keys) to code in a General Module and
then having the Query call it there, but I would really like to be
able to call a Function in a Form module.
Click to expand...

It may not be very efficient, but you could do something like this:

SELECT
tblMyTable.MyField,
Eval("Forms!MyForm.MyFormFunction(" & [MyField] & ")") AS Result
FROM tblMyTable;
Click to expand...

Thanks! When I started to believe that you couldn't do this the
straightforward way, I recalled that the Eval() Function existed and
thought that it could provide a workaround, but I could not, for the
life of me, remember its name! As usual, Help didn't. Since the
numbers of records in the Tables I am dealing with in this application
are invariably well under 1000 and mostly 50-200, the inefficiency of
this approach should not be a major problem in practice.
 
Back
Top