How To Call Form VBA Function From a Query?

[johnywhy]

How can a sql query call a vba function inside an open form?

The form is called "Flex2 Hospitals" and the VBA function inside it is called "TJCs".

I've tried the following syntaxes without success:

[Forms]![Flex2 Hospitals]!TJCs
 
[Form_Flex2 Hospitals].[TJCs]

For example:

SELECT *
FROM Hospitals
WHERE [Flex2 Hospitals].[TJCs] Like [Hospitals].[TJC]

thx

[NeoPa]

I've tried the following syntaxes without success:

It would help to know what that means in these circumstances.

I would expect that a function reference within SQL would expect parentheses after it :

Form_Flex2_Hospitals!TJCs()

If that's not right you could try posting the function code itself along with your answer to my earlier question.

PS. Your attempts all look good. You clearly understand that a function procedure from an object module cannot be referenced without the reference to the module as function procedures of standard modules can.

[ADezii]

If the Function is 'Public' and in a Standard Code Module, it can reference an Open Form and return a Value that can be used as Criteria in a Field, as in:

SELECT Hospitals.ID, Hospitals.LastName, Hospitals.FirstName
FROM Hospitals
WHERE Hospitals.ID=fGetHospCode();

[topher23]

By way of explanation...
In your first syntax version:

[Forms]![Flex2 Hospitals]!TJCs

your code is looking for the default collection of the Forms object, which is Controls, not code procedures, so that definitely wouldn't work.
In the second instance:

[Form_Flex2 Hospitals].[TJCs]

the period (.) prompts Access to look for a property of the Form_Flex2 Hospitals module. In order to specify a member of the default collection (code procedures), you do need to use NeoPa's syntax with the exclamation point (!). However, ADezii's answer provides a more standardized solution.

[zmbd]

Post the actual function within the form.

PLEASE: Include the entire function,from header to "end function"

PLEASE: Select the posted code and format it useing the [CODE/] button.


ADezii, had that part correct in "Public" and "Standard" module. I however, suspect that that that the function is "Private" to the "Class Module" of the form; thus, unless one exposes it either by using a calculated control and refering to that control, or by declairing the function "Public" no amount of mixing nomenclature is ever going to get the function's results outside of the scope of the form.
(^-^)