Using return value from form as expression in Query

[Guest]

Hi,

I have a situation in which I need to be able to open a form and use it's
"return value" as the field in one of my queries. Iin other words, I would
like to be able to prompt the user for a particular value (but I'd prefer to
use my own form rather than an InputBox).

At first I wrote a function ScoreByHand that invoked DoCmd.OpenForm ... but
when it tried to call DoCmd.OpenForm I get run-time error '2486' "You can't
carry out this action at the present time."

The idea was to do something like:
SELECT userAnswer, correctAnswer,ScoreByHand(userAnswer, correctAnswer)

However, even when I do something like:
SELECT userAnswer, correctAnswer, InputBox("Score") ...

I get a "Function is not available in expressions"

I thought I might be able to work around the issue by opening the form
myself (without DoCmd). This is what I tried:

Dim frm As New [Form_Grade By Hand]

frm.Visible = True
Do While Not frm.Done
frm.Repaint
DoEvents
Loop

I thought I had done something like that in the past and had it work, but
this time, even though the window pops up, it never draws the control and I
get an "application not responding" when I click anywhere.

Any ideas on how to make this work????

You may wonder why I'm doing the query like this and I'll explain why upon
request, but so far it seems to be the best option if I can get it to work.

Thanks in advance!

 

[Van T. Dinh]

The usual way is to open your custom Form "frmParam" first (by code if
applicable). On the Form, you have (at least) a TextBox for the user to
enter the Param value AND a CommandButon to run the Query (or to run other
action(s) related to the Query).

The reason is that the Queries all ready has in-built Parameter dialog so
the Expression Service (a sort of interface between Access
Queries/Forms/Reports ... and VBA) does not include functions such as
InputBox. Also, a function should only returns a value and no side-effects
/ actions. My guess is that the Expression Service also prevents these
side-effects / actions ... so you cannot use the OpenForm in a UDF when the
UDF is called by the Query ...

 

[Guest]

That's what I was afraid of. Unfortunately, I cannot directly implement the
usual form -> query way of doing things that you suggested. However, I can
probably use vb -> form -> create table query and use the values in this new
table instead of the ones I was trying to get in real-time as the query ran.
Obviously much less convenient, but I guess it'll have to do.

The usual way is to open your custom Form "frmParam" first (by code if
applicable). On the Form, you have (at least) a TextBox for the user to
enter the Param value AND a CommandButon to run the Query (or to run other
action(s) related to the Query).

The reason is that the Queries all ready has in-built Parameter dialog so
the Expression Service (a sort of interface between Access
Queries/Forms/Reports ... and VBA) does not include functions such as
InputBox. Also, a function should only returns a value and no side-effects
/ actions. My guess is that the Expression Service also prevents these
side-effects / actions ... so you cannot use the OpenForm in a UDF when the
UDF is called by the Query ...

--
HTH
Van T. Dinh
MVP (Access)

Hi,

I have a situation in which I need to be able to open a form and use it's
"return value" as the field in one of my queries. Iin other words, I
would
like to be able to prompt the user for a particular value (but I'd prefer
to
use my own form rather than an InputBox).

At first I wrote a function ScoreByHand that invoked DoCmd.OpenForm ...
but
when it tried to call DoCmd.OpenForm I get run-time error '2486' "You
can't
carry out this action at the present time."

The idea was to do something like:
SELECT userAnswer, correctAnswer,ScoreByHand(userAnswer, correctAnswer)

However, even when I do something like:
SELECT userAnswer, correctAnswer, InputBox("Score") ...

I get a "Function is not available in expressions"

I thought I might be able to work around the issue by opening the form
myself (without DoCmd). This is what I tried:

Dim frm As New [Form_Grade By Hand]

frm.Visible = True
Do While Not frm.Done
frm.Repaint
DoEvents
Loop

I thought I had done something like that in the past and had it work, but
this time, even though the window pops up, it never draws the control and
I
get an "application not responding" when I click anywhere.

Any ideas on how to make this work????

You may wonder why I'm doing the query like this and I'll explain why upon
request, but so far it seems to be the best option if I can get it to
work.

Thanks in advance!