Using a form field as a criteria in a query

[Dennisf1]

Hi:

I searched through many answers in group and I'm still not clear on
how to accomplish this with a reasonable level of effort and within my
coding capabiities. I am not a VBA programmer though I have extensive
IT experience and can noodle my way through things.

I would like to take a value from a text or number field on a form and
use it as a criteria for reducing the number of rows returned on a
simple query such as

Select Category, Description from MyTable where Category = FormField.

FormField is the criteria coming from the form. Nothing exotic .

Is there a simple way to accomplish this or must I code a VBA
routine.

Your input and advice would be most appreciated.

Regards,

Dennis

 

[Guest]

In the criteria use this ---
[Forms]![YourFormName]![YourTextBoxName]

Substitute the correct names in the above.

 

[Dennisf1]

In thecriteriause this ---
[Forms]![YourFormName]![YourTextBoxName]

Substitute the correct names in the above.

Hi:

I searched through many answers in group and I'm still not clear on
how to accomplish this with a reasonable level of effort and within my
coding capabiities. I am not a VBA programmer though I have extensive
IT experience and can noodle my way through things.

I would like to take a value from a text or numberfieldon aformand
use it as acriteriafor reducing the number of rows returned on a
simplequerysuch as

Select Category, Description from MyTable where Category = FormField.

FormField is thecriteriacoming from theform. Nothing exotic .

Is there a simple way to accomplish this or must I code a VBA
routine.

Your input and advice would be most appreciated.

Dennis- Hide quoted text -

- Show quoted text -

Karl:

I placed the form name as suggested in the criteria of the query
builder and when I run the query it thinks the criteria is a parmeter
and is asking for input. This is what I encountered when I tried that
solution initiallly

Further suggeestions would be appreicated.

Thanks,

Dennis

 

[Guest]

It is a parameter. You have to have the form open first, then enter data in
the text box. Then you envoke the query.

In thecriteriause this ---
[Forms]![YourFormName]![YourTextBoxName]

Substitute the correct names in the above.

Hi:

I searched through many answers in group and I'm still not clear on
how to accomplish this with a reasonable level of effort and within my
coding capabiities. I am not a VBA programmer though I have extensive
IT experience and can noodle my way through things.

I would like to take a value from a text or numberfieldon aformand
use it as acriteriafor reducing the number of rows returned on a
simplequerysuch as

Select Category, Description from MyTable where Category = FormField.

FormField is thecriteriacoming from theform. Nothing exotic .

Is there a simple way to accomplish this or must I code a VBA
routine.

Your input and advice would be most appreciated.

Dennis- Hide quoted text -

- Show quoted text -

Karl:

I placed the form name as suggested in the criteria of the query
builder and when I run the query it thinks the criteria is a parmeter
and is asking for input. This is what I encountered when I tried that
solution initiallly

Further suggeestions would be appreicated.

Thanks,

Dennis

 

[John W. Vinson]

I placed the form name as suggested in the criteria of the query
builder and when I run the query it thinks the criteria is a parmeter
and is asking for input. This is what I encountered when I tried that
solution initiallly

Further suggeestions would be appreicated.

Have the form open BEFORE you run the query. The query won't open the
form for you!

It's often handy to base a Form (for onscreen) or Report (for
printing) on the query, and put a command button on the search form to
open said Form or Report. This ensures that the search form is open to
provide the criterion.

John W. Vinson [MVP]

 

[Dennisf1]

Have the form open BEFORE you run the query. The query won't open the
form for you!

It's often handy to base a Form (for onscreen) or Report (for
printing) on the query, and put a command button on the search form to
open said Form or Report. This ensures that the search form is open to
provide the criterion.

John W. Vinson [MVP]

John:

Thanks for your reply.

The query is the rowsource for a list box on the form so i would
assume that the form is open and active. FormHoldProt is a text field
which gets its value from another listbox selection as it is that
listbox's controlsource. Not sure how or if I would implement the
change to reflect your suggestion.

I have attached the sql generated by the Query Builder to see if that
would help.

SELECT CtrlLookupTab.CtrlID, CtrlLookupTab.PROTCat,
CtrlLookupTab.CtrlType, CtrlLookupTab.CtrlDescript
FROM CtrlLookupTab
WHERE (((CtrlLookupTab.PROTCat)=[Forms]![AppMitigatingControls]!
[FormHoldProt]))
ORDER BY CtrlLookupTab.PROTCat, CtrlLookupTab.CtrlType,
CtrlLookupTab.CtrlDescript;

AppMitigatingControls is the form name (I know a little long but self
explanatory)

The issue at end is that when the above form is used I am asked for
entry of a parameter Forms!AppMitigatingControls!FormHoldProt instead
of having it take it from the form.

Please do help if you can

Thx again