Query Parameters in IIF or Function

[rubbernilly]

Hello all,

I posted this over in the VBA forum as well, but this might be the better venue for this question...

I have a query with a criteria parameter prompting the user to enter a value for the field. However, if a particular form is open, I want that field to pull from a particular field on that form.

I know how to tell if a particular form is open through code, but how do I still prompt the user if the form is not open?

IIF(IsFormOpen("MyFormName"),forms!MyForm.myControl,[Enter Value])

That sort of verbage prompts me for the value of the control if the form is not open, and still prompts me for [Enter Value] even if it is open.

I even looked at declaring a Function of type "parameter," but I don't know if this is right. I haven't been able to make that work.

Is there a general way to do what I'm looking for? General enough so that a function or syntax could handle various data types?

Form Open = take value from control
Form Not Open = prompt

 

[PHV]

Why not simply this as criteria ?
[Forms]![MyForm]![myControl]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BoxHead]

rubbernilly,

I can't find the IsFormOpen function in Access VBA help. Have you tried IsLoaded?

If this is a custom function, is it returning a boolean?

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[PHV]

and still prompts me for [Enter Value] even if it is open
The IIf function always evaluates both the True and False parts.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BoxHead]

PHV

You're absolutely right. Thanks for that.

Bad news for those of us who have users who don't respond well to prompts reading

Forms!frmRpt01!t12






John

Use what you have,
Learn what you can,
Create what you need.

 

[rubbernilly]

Thanks for the responses...

Yes IsFormOpen() is a custom procedure returning a boolean.

I can use the reference to the Form in the criteria, I guess...

Forms!Form!Control

But, like BoxHead hinted at, I am trying to mask the prompt for Forms!Form!Control if the user opens the query outside of the circumstance where the form would be open.

Is there anyway to mask a parameter with another parameter?

 

[BoxHead]

Where I have Forms!frmRpt01!t12
referring to the 12th textbox on my report form, I added an invisible textbox and named it: " PLEASE ENTER THE DATE "

I set it's control source to =t12 and changed the query criteria to be

Forms!frmRpt01!" PLEASE ENTER THE DATE "

Now if the form is not open, the prompt includes some sensible info for the user.

However, I'm thinking that I may just set the property for the query to Hidden so that it won't be seen without the form open. Just have to think through if there are any repercussions to this.




John

Use what you have,
Learn what you can,
Create what you need.