How to do parameter query using a Forms reference

[CRH]

I want a drop down box of valid values on my parameter query. Per online
examples, I created a form, which allows me to see a drop down box of valid
values and to click on one of the values.

The form works. It opens with (choose one) in the drop down box. Clicking
on the box shows me all of my valid choices, in sorted order. Clicking on
one of the choices makes it the selected one on the form.

Form Name: Status Prompt Form
Field Name: StatusDesc
Border Style: Dialog
Default Value: "(choose one)"
Row Source:
SELECT [Status Table].StatusID, [Status Table].StatusDesc FROM [Status
Table] ORDER BY [Status Table].StatusDesc;

I then have a Query, which almost works:

Query Name: Info by Status (prompt) Query
Field Name: StatusDesc
Table: Status Table
Sort: Ascending
Show: [checked]
Criteria: [Forms]![Status Prompt Form]![StatusDesc]

When I run the Query, instead of getting my form, I get the standard
parameter dialog box. like this:

Dialog Box Heading: Enter Parameter Value [?] [X]
Dialog Box Content:
First this line of text:
Forms!Status Prompt Form!StatusDesc
followed by a text box
[ ]
then [OK] and [Cancel] buttons

If I type a known good value into the text box and click [OK], the query
returns the correct records.

But notice that the query defaulted to the standard parameter box, where I
have to know the valid values ahead of time. So putting in a bad value, or
no value at all, returns an empty record set.

I want the query to use my form.

Can someone help tell me what I'm doing wrong, please?

Thank you,
- Cathy H.

 

[Douglas J. Steele]

The form needs to be open for the query to use it. The query will not open
the form for you.

 

[CRH]

Thank you, that worked. I opened the form, made my selection, then clicked
on the report, which used the query that used the form. The report was
correct.

I could change my selection in the open form, click on the report again, and
get the new values.

But I would still like to do it in one click. Since I can't make the
report/query automatically launch the form, can I go the other directions? Is
there any way to tie the "OK" button on the form to have it automatically
launch the report?

- Cathy

The form needs to be open for the query to use it. The query will not open
the form for you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I want a drop down box of valid values on my parameter query. Per online
examples, I created a form, which allows me to see a drop down box of
valid
values and to click on one of the values.

The form works. It opens with (choose one) in the drop down box.
Clicking
on the box shows me all of my valid choices, in sorted order. Clicking on
one of the choices makes it the selected one on the form.

Form Name: Status Prompt Form
Field Name: StatusDesc
Border Style: Dialog
Default Value: "(choose one)"
Row Source:
SELECT [Status Table].StatusID, [Status Table].StatusDesc FROM [Status
Table] ORDER BY [Status Table].StatusDesc;

I then have a Query, which almost works:

Query Name: Info by Status (prompt) Query
Field Name: StatusDesc
Table: Status Table
Sort: Ascending
Show: [checked]
Criteria: [Forms]![Status Prompt Form]![StatusDesc]

When I run the Query, instead of getting my form, I get the standard
parameter dialog box. like this:

Dialog Box Heading: Enter Parameter Value [?] [X]
Dialog Box Content:
First this line of text:
Forms!Status Prompt Form!StatusDesc
followed by a text box
[ ]
then [OK] and [Cancel] buttons

If I type a known good value into the text box and click [OK], the query
returns the correct records.

But notice that the query defaulted to the standard parameter box, where I
have to know the valid values ahead of time. So putting in a bad value,
or
no value at all, returns an empty record set.

I want the query to use my form.

Can someone help tell me what I'm doing wrong, please?

Thank you,
- Cathy H.

 

[CRH]

OK, I figured out how to get the form to trigger the report. In the
properties of the OK button, I entered

DoCmd.OpenReport "Report Name"

which worked, yay! Only it worked too well. Not only did it automatically
triggered the report, it send the report directly to the printer without
giving me a chance to see it first on the screen. Is there a command that
generates the report to the screen, but doesn't print it? That is, I can
click to print it as I would any other report?

(Actually, what I do the most is save the reports in HTML format, and attach
them to emails to the concerned parties).


Thanks,
- Cathy

The form needs to be open for the query to use it. The query will not open
the form for you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I want a drop down box of valid values on my parameter query. Per online
examples, I created a form, which allows me to see a drop down box of
valid
values and to click on one of the values.

The form works. It opens with (choose one) in the drop down box.
Clicking
on the box shows me all of my valid choices, in sorted order. Clicking on
one of the choices makes it the selected one on the form.

Form Name: Status Prompt Form
Field Name: StatusDesc
Border Style: Dialog
Default Value: "(choose one)"
Row Source:
SELECT [Status Table].StatusID, [Status Table].StatusDesc FROM [Status
Table] ORDER BY [Status Table].StatusDesc;

I then have a Query, which almost works:

Query Name: Info by Status (prompt) Query
Field Name: StatusDesc
Table: Status Table
Sort: Ascending
Show: [checked]
Criteria: [Forms]![Status Prompt Form]![StatusDesc]

When I run the Query, instead of getting my form, I get the standard
parameter dialog box. like this:

Dialog Box Heading: Enter Parameter Value [?] [X]
Dialog Box Content:
First this line of text:
Forms!Status Prompt Form!StatusDesc
followed by a text box
[ ]
then [OK] and [Cancel] buttons

If I type a known good value into the text box and click [OK], the query
returns the correct records.

But notice that the query defaulted to the standard parameter box, where I
have to know the valid values ahead of time. So putting in a bad value,
or
no value at all, returns an empty record set.

I want the query to use my form.

Can someone help tell me what I'm doing wrong, please?

Thank you,
- Cathy H.