Form Fields as Query Parameter

[webfaktor]

I have a report built on a query, with this query having a parameter that is provided by a selection from a combo box on a form.

I am in need of instruction upon how to have this parameter query look to more than one form/combobox to acquire its parameter. Only one form will be open at a time, in which case one of the parameters will be NULL. I've gathered that the Nz Function might work for me - but I cannot find an example or directions on how to implement it with the sample parameter query criteria I've written below.

[Forms]![FormNameA]![SubFormNameA]![FieldName1] Or [Forms]![FormNameB]![FieldName1]

I would like to learn to do this in order to keep down the size/complexity of my database, i.e., I would then not need to duplicate a query/report for each place within my database that the report is to be launched from.

So much to learn!

Web

 

[KenReay]

Hi

You cannot reference a form using the Forms! syntax, unless the form is open. Forms is a collection of open forms.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[wemeier]

You can't set up the query the way you want because it will cause an error whenever it refers to the form that is NOT open. Access won't allow you to refer to a form field if the form is not open.

Instead, try creating a "criteria" form that will hold the criteria for the query. In each form that is to call the query, add coding to open the "criteria" form as a hidden form, populate the form's OpenArgs parameter with the criteria value, then call the query, which will take its criteria from this hidden form.

For example:

DoCmd.OpenForm &quot;CriteriaForm&quot;,WindowMode:=acHidden,OpenArgs:=<criteria you want to use>
DoCmd.OpenQuery &quot;<query that uses the criteria>&quot;
DoCmd.Close acForm,&quot;CriteriaForm&quot;

In the query's criteria box (in the QBE grid), put the following:

=Forms!CriteriaForm!OpenArgs

 

[webfaktor]

Wemeier,

Excellent suggestion.

Thank you very much!

WF

 

[webfaktor]

Wemeier,

Unfortunately, OpenArgs, is a new concept to me. Any direction you could offer me for implementing OpenArgs in this circumstance would be much appreciated.

Regards,

WF

 

[wemeier]

In Access97 (and above?) each form has a built-in property called OpenArgs. It is used for passing arguments to the form that can be referred to in the FormOpen event.

If you are in the form itself, you refer to it as Me!OpenArgs. If you are outside of the form, you refer to it as Forms![formname]!OpenArgs.

When you open a form using the OpenForm method, one of the parameters that can be passed is the OpenArgs parameter. You can specify it using a positional parameter (counting commas) or a keyword parameter (using :=).