Use of Dialog Box and Reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have used dialog boxes with reports based on queries. However, I have
created a report using a crosstab query and I do not know how to accomplish
this. I want the user to be able to select the paid through date before
running the report.
 
Anne

Not sure what you mean by "dialog boxes" -- are you referring to the
parameter prompts in a parameterized query (or possibly the way Access
prompts you for a value if you set a report's control to a field that isn't
in its list of fields)?

Another approach is to use an Access form as a "report order" form, and put
controls on it that will hold your selection criteria. You'd then need to
modify your queries to point at the form, rather than use "fill-in" prompts.
The new parameter would look something like:

Forms!YourOrderFormName!TheControlWithTheParameter

(suitably modified to match up with your form and control names)
 
Jeff,

I have a form that the user enters requested information in order to open a
report. The user requests the report based upon the PdThrough field e.g.
12/31/2005, 12/31/2004 etc. From reading various posts I have done the
following.

I have a report based on a crosstab query. In the design view I selected
Query, Parameters and entered the following information –
[Forms]![frmDialogPdMemberType]![ValidUntil] Date/Time. I also put the same
information [Forms]![frmDialogPdMemberType]![ValidUntil] in the Criteria
field (PdThrough) of the query underlying the crosstab query.

I am able to retrieve my report but several “boxes†pop up. The referenced
form that request user information, and several others generated by Access.
Now, I don’t know how to get rid of the extra parameter requests.

Thank you for your help.

Anne
 
Anne

If the report opening is "stimulating" these parameter requests, it's like
the report can't find the parameters. This could be due to spelling
differences, and can also be due to not have the form open.

Any help?

--
Regards

Jeff Boyce
<Office/Access MVP>

AnneC said:
Jeff,

I have a form that the user enters requested information in order to open a
report. The user requests the report based upon the PdThrough field e.g.
12/31/2005, 12/31/2004 etc. From reading various posts I have done the
following.

I have a report based on a crosstab query. In the design view I selected
Query, Parameters and entered the following information –
[Forms]![frmDialogPdMemberType]![ValidUntil] Date/Time. I also put the same
information [Forms]![frmDialogPdMemberType]![ValidUntil] in the Criteria
field (PdThrough) of the query underlying the crosstab query.

I am able to retrieve my report but several “boxes†pop up. The referenced
form that request user information, and several others generated by Access.
Now, I don’t know how to get rid of the extra parameter requests.

Thank you for your help.

Anne


Jeff Boyce said:
Anne

Not sure what you mean by "dialog boxes" -- are you referring to the
parameter prompts in a parameterized query (or possibly the way Access
prompts you for a value if you set a report's control to a field that isn't
in its list of fields)?

Another approach is to use an Access form as a "report order" form, and put
controls on it that will hold your selection criteria. You'd then need to
modify your queries to point at the form, rather than use "fill-in" prompts.
The new parameter would look something like:

Forms!YourOrderFormName!TheControlWithTheParameter

(suitably modified to match up with your form and control names)

--
Regards

Jeff Boyce
<Office/Access MVP>
Click to expand...
Click to expand...
 
Jeff, I found my problem. I had used "ValidUntil" in my parameter form and
inserted [Forms]![frmDialogPdMemberType]![ValidUntil] into the PdThrough
field in the underlying query but in the Query Parameter I had inserted
PdThrough instead of "ValidUntil". I had to let it sit. When I came back I
found it right away. Thank you very much for your help

I have another question. In the membership form I have added a field titled
"MemStatus" (text) which equals current, prior, or archive based upon the
PdThrough(date/time) date. Can a case statement or an If-then-else be
written which would evaluate and insert the information into the field
automatically. The purpose is to isolate out former members who have not
renewed their dues for 3 years. Also I want to automate as I depend upon
volunteers for data input. "MemStatus" is in the Membership table and
"PdThrough" in payments table. If so, how would it be written?

Anne

Jeff Boyce said:
Anne

If the report opening is "stimulating" these parameter requests, it's like
the report can't find the parameters. This could be due to spelling
differences, and can also be due to not have the form open.

Any help?

--
Regards

Jeff Boyce
<Office/Access MVP>

AnneC said:
Jeff,

I have a form that the user enters requested information in order to open a
report. The user requests the report based upon the PdThrough field e.g.
12/31/2005, 12/31/2004 etc. From reading various posts I have done the
following.

I have a report based on a crosstab query. In the design view I selected
Query, Parameters and entered the following information –
[Forms]![frmDialogPdMemberType]![ValidUntil] Date/Time. I also put the same
information [Forms]![frmDialogPdMemberType]![ValidUntil] in the Criteria
field (PdThrough) of the query underlying the crosstab query.

I am able to retrieve my report but several “boxes†pop up. The referenced
form that request user information, and several others generated by Access.
Now, I don’t know how to get rid of the extra parameter requests.

Thank you for your help.

Anne


Jeff Boyce said:
Anne

Not sure what you mean by "dialog boxes" -- are you referring to the
parameter prompts in a parameterized query (or possibly the way Access
prompts you for a value if you set a report's control to a field that isn't
in its list of fields)?

Another approach is to use an Access form as a "report order" form, and put
controls on it that will hold your selection criteria. You'd then need to
modify your queries to point at the form, rather than use "fill-in" prompts.
The new parameter would look something like:

Forms!YourOrderFormName!TheControlWithTheParameter

(suitably modified to match up with your form and control names)

--
Regards

Jeff Boyce
<Office/Access MVP>

I have used dialog boxes with reports based on queries. However, I have
created a report using a crosstab query and I do not know how to
accomplish
this. I want the user to be able to select the paid through date before
running the report.
Click to expand...
Click to expand...
Click to expand...
 
Anne

If you are saying that you can "calculate" the value of MemStatus based on
other field(s), you probably don't need to save that calculation in a table.

If you have a query that includes the PdThrough value, you could use the
IIF() statement to "calculate" a value for MemStatus.

By the way, you mentioned PdThrough is a text field -- is there a reason
you're not using a date/time field?

Spelling counts! Glad you found it.

--
Regards

Jeff Boyce
<Office/Access MVP>


AnneC said:
Jeff, I found my problem. I had used "ValidUntil" in my parameter form and
inserted [Forms]![frmDialogPdMemberType]![ValidUntil] into the PdThrough
field in the underlying query but in the Query Parameter I had inserted
PdThrough instead of "ValidUntil". I had to let it sit. When I came back I
found it right away. Thank you very much for your help

I have another question. In the membership form I have added a field titled
"MemStatus" (text) which equals current, prior, or archive based upon the
PdThrough(date/time) date. Can a case statement or an If-then-else be
written which would evaluate and insert the information into the field
automatically. The purpose is to isolate out former members who have not
renewed their dues for 3 years. Also I want to automate as I depend upon
volunteers for data input. "MemStatus" is in the Membership table and
"PdThrough" in payments table. If so, how would it be written?

Anne

Jeff Boyce said:
Anne

If the report opening is "stimulating" these parameter requests, it's like
the report can't find the parameters. This could be due to spelling
differences, and can also be due to not have the form open.

Any help?

--
Regards

Jeff Boyce
<Office/Access MVP>

AnneC said:
Jeff,

I have a form that the user enters requested information in order to
Click to expand...
open
a
report. The user requests the report based upon the PdThrough field e.g.
12/31/2005, 12/31/2004 etc. From reading various posts I have done the
following.

I have a report based on a crosstab query. In the design view I selected
Query, Parameters and entered the following information –
[Forms]![frmDialogPdMemberType]![ValidUntil] Date/Time. I also put
Click to expand...
the
same
information [Forms]![frmDialogPdMemberType]![ValidUntil] in the Criteria
field (PdThrough) of the query underlying the crosstab query.

I am able to retrieve my report but several “boxes†pop up. The referenced
form that request user information, and several others generated by Access.
Now, I don’t know how to get rid of the extra parameter requests.

Thank you for your help.

Anne


:

Anne

Not sure what you mean by "dialog boxes" -- are you referring to the
parameter prompts in a parameterized query (or possibly the way Access
prompts you for a value if you set a report's control to a field
Click to expand...
that
isn't
in its list of fields)?

Another approach is to use an Access form as a "report order" form,
Click to expand...
and
put
controls on it that will hold your selection criteria. You'd then
Click to expand...
need
to
modify your queries to point at the form, rather than use "fill-in" prompts.
The new parameter would look something like:

Forms!YourOrderFormName!TheControlWithTheParameter

(suitably modified to match up with your form and control names)

--
Regards

Jeff Boyce
<Office/Access MVP>

I have used dialog boxes with reports based on queries. However,
Click to expand...
I
have
created a report using a crosstab query and I do not know how to
accomplish
this. I want the user to be able to select the paid through date before
running the report.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top