Troubleshooting parameter prompts that keep popping up???

[Guest]

I have created a report and a subreport each based on different queries with
'Between [Type beginning date] And [Type ending date] criteria prompts. When
I open the stand alone reports, I only get promted once for the beginning and
ending dates (as expected). However, when I open the report with the
subreport I get prompted many many times for the beginning and ending dates.
So many times that it is difficult to count. I have tried removing the
criteria from the sub report via the underlying query thinking that the main
report's criteria would apply to these records as well, but it did not apply
to these records. Has anyone encountered this and come up with a solution?
Any advice would help.
Thanks!!!

 

[Duane Hookom]

Don't use parameter prompts for criteria. Always allow your users to enter
values into controls on forms.

 

[Guest]

Thank you for your assistance! Will creating a form for entering values apply
to both a main report and a subreport? Also, I'm pretty much a self-taught
Access user, could you explain why it is better to use a form?
Thanks again for the information!

Don't use parameter prompts for criteria. Always allow your users to enter
values into controls on forms.

--
Duane Hookom
MS Access MVP
--

I have created a report and a subreport each based on different queries
with
'Between [Type beginning date] And [Type ending date] criteria prompts.
When
I open the stand alone reports, I only get promted once for the beginning
and
ending dates (as expected). However, when I open the report with the
subreport I get prompted many many times for the beginning and ending
dates.
So many times that it is difficult to count. I have tried removing the
criteria from the sub report via the underlying query thinking that the
main
report's criteria would apply to these records as well, but it did not
apply
to these records. Has anyone encountered this and come up with a solution?
Any advice would help.
Thanks!!!

 

[Duane Hookom]

You can set the criteria in both the main report and subreport to reference
the same controls.

"Why it is better"? How many off-the-shelf programs have you seen where you
get a pop-up parameter dialog to ask you for values? I hope none as this is
a very poor user interface. Controls on a form allows you to set defaults,
use calendars or combo or list boxes, check for validity, persist criteria
values,...

--
Duane Hookom
MS Access MVP
--

Thank you for your assistance! Will creating a form for entering values
apply
to both a main report and a subreport? Also, I'm pretty much a self-taught
Access user, could you explain why it is better to use a form?
Thanks again for the information!

Don't use parameter prompts for criteria. Always allow your users to
enter
values into controls on forms.

--
Duane Hookom
MS Access MVP
--

I have created a report and a subreport each based on different queries
with
'Between [Type beginning date] And [Type ending date] criteria prompts.
When
I open the stand alone reports, I only get promted once for the
beginning
and
ending dates (as expected). However, when I open the report with the
subreport I get prompted many many times for the beginning and ending
dates.
So many times that it is difficult to count. I have tried removing the
criteria from the sub report via the underlying query thinking that the
main
report's criteria would apply to these records as well, but it did not
apply
to these records. Has anyone encountered this and come up with a
solution?
Any advice would help.
Thanks!!!

 

[Guest]

Duane (or another expert),
I set up a form to collect beginning and ending dates according to MS Office
Online "Create a form to enter report critieria". It worked! BUT, now I am
prompted with like 12 dialog boxes before the report and subreport will open.
Any suggestions???
Thanks in advance for your continued assistance!
Brandye

Don't use parameter prompts for criteria. Always allow your users to enter
values into controls on forms.

--
Duane Hookom
MS Access MVP
--

I have created a report and a subreport each based on different queries
with
'Between [Type beginning date] And [Type ending date] criteria prompts.
When
I open the stand alone reports, I only get promted once for the beginning
and
ending dates (as expected). However, when I open the report with the
subreport I get prompted many many times for the beginning and ending
dates.
So many times that it is difficult to count. I have tried removing the
criteria from the sub report via the underlying query thinking that the
main
report's criteria would apply to these records as well, but it did not
apply
to these records. Has anyone encountered this and come up with a solution?
Any advice would help.
Thanks!!!

 

[Duane Hookom]

I am having trouble seeing your screen to know what the dialog boxes say or
what your report's record source looks like.

--
Duane Hookom
MS Access MVP
--

Duane (or another expert),
I set up a form to collect beginning and ending dates according to MS
Office
Online "Create a form to enter report critieria". It worked! BUT, now I am
prompted with like 12 dialog boxes before the report and subreport will
open.
Any suggestions???
Thanks in advance for your continued assistance!
Brandye

Don't use parameter prompts for criteria. Always allow your users to
enter
values into controls on forms.

--
Duane Hookom
MS Access MVP
--

I have created a report and a subreport each based on different queries
with
'Between [Type beginning date] And [Type ending date] criteria prompts.
When
I open the stand alone reports, I only get promted once for the
beginning
and
ending dates (as expected). However, when I open the report with the
subreport I get prompted many many times for the beginning and ending
dates.
So many times that it is difficult to count. I have tried removing the
criteria from the sub report via the underlying query thinking that the
main
report's criteria would apply to these records as well, but it did not
apply
to these records. Has anyone encountered this and come up with a
solution?
Any advice would help.
Thanks!!!

 

[Guest]

Ha! Good point... I was hoping that it was a common newby error. No such
luck. Ok. Here goes my attempt to summarize the details that may enable you
to pinpoint my error:

PROBLEM: A form/dialog box, tied to the opening of a report with a subreport
prompting for beginning and ending dates for a report with a subreport, comes
ups approximately 12 times (i.e., I enter beginning and ending date and click
on the OK command button and then the form/dialog box comes up again).
Interestingly, if I enter the same beginning and ending dates 12 times, the
expeced data comes up in the report, but when I try to go to pages 2 and so
on, I am again prompted with the form/dialog box for the beginning and ending
dates.
SUMMARY OF UNDERLYING QUERIES AND RECORD SOURCES:
I have a main report with a sub report (see details on field and record
souces below). Each of these reports when opened as stand alone reports are
prompted ONLY ONCE with a form/dialog box containing two fields beginning
date and ending date (which limits the recordes by the datecompleted field)
according to this expression which I have in the query for the main report
and in the query for the sub report in the criteria row for the
completiondate field column:
---"Between [Forms]![Production Dialog]![BeginningDate] And
[Forms]![Production Dialog]![EndingDate]"---

DETAILS:
---main report fields from the underlying query---
[crewchief/text]; [completiondate/where criteria (so it does not actually
appear in the table]; [sumcontractamountcomplete/currency-calculated];
[sumoftotalexpenses/currency-calculated]; [sumprofit/percent-calculated];
[countofID/number]

---sub report fields from the unerlying query---
[crew chief/text]; [jobtype/text]; [salesman/text];
[completiondate/date/time];[customername]; [customeraddress/text];
[contractamountcomplete/currency]; [totalexpenses/currency-calculated];
[profit/percent-calculated]

---report link properties---
Master Field: [crewchief]
Child Field: [crewchief]

I hope this provides enough detail without too much extraneous infor to
offer some solutions or insight that may be causing the repeated dialog
box/forms.
Thanks so much!!!
Brandye

I am having trouble seeing your screen to know what the dialog boxes say or
what your report's record source looks like.

--
Duane Hookom
MS Access MVP
--

Duane (or another expert),
I set up a form to collect beginning and ending dates according to MS
Office
Online "Create a form to enter report critieria". It worked! BUT, now I am
prompted with like 12 dialog boxes before the report and subreport will
open.
Any suggestions???
Thanks in advance for your continued assistance!
Brandye

Don't use parameter prompts for criteria. Always allow your users to
enter
values into controls on forms.

--
Duane Hookom
MS Access MVP
--

I have created a report and a subreport each based on different queries
with
'Between [Type beginning date] And [Type ending date] criteria prompts.
When
I open the stand alone reports, I only get promted once for the
beginning
and
ending dates (as expected). However, when I open the report with the
subreport I get prompted many many times for the beginning and ending
dates.
So many times that it is difficult to count. I have tried removing the
criteria from the sub report via the underlying query thinking that the
main
report's criteria would apply to these records as well, but it did not
apply
to these records. Has anyone encountered this and come up with a
solution?
Any advice would help.
Thanks!!!

 

[Duane Hookom]

Are you opening the form/dialog box with code in the On Open event of the
report? If so, don't.

Open your criteria form prior to opening your report. Enter your criteria
values and then open the report.

--
Duane Hookom
MS Access MVP
--

Ha! Good point... I was hoping that it was a common newby error. No such
luck. Ok. Here goes my attempt to summarize the details that may enable
you
to pinpoint my error:

PROBLEM: A form/dialog box, tied to the opening of a report with a
subreport
prompting for beginning and ending dates for a report with a subreport,
comes
ups approximately 12 times (i.e., I enter beginning and ending date and
click
on the OK command button and then the form/dialog box comes up again).
Interestingly, if I enter the same beginning and ending dates 12 times,
the
expeced data comes up in the report, but when I try to go to pages 2 and
so
on, I am again prompted with the form/dialog box for the beginning and
ending
dates.
SUMMARY OF UNDERLYING QUERIES AND RECORD SOURCES:
I have a main report with a sub report (see details on field and record
souces below). Each of these reports when opened as stand alone reports
are
prompted ONLY ONCE with a form/dialog box containing two fields beginning
date and ending date (which limits the recordes by the datecompleted
field)
according to this expression which I have in the query for the main report
and in the query for the sub report in the criteria row for the
completiondate field column:
---"Between [Forms]![Production Dialog]![BeginningDate] And
[Forms]![Production Dialog]![EndingDate]"---

DETAILS:
---main report fields from the underlying query---
[crewchief/text]; [completiondate/where criteria (so it does not actually
appear in the table]; [sumcontractamountcomplete/currency-calculated];
[sumoftotalexpenses/currency-calculated]; [sumprofit/percent-calculated];
[countofID/number]

---sub report fields from the unerlying query---
[crew chief/text]; [jobtype/text]; [salesman/text];
[completiondate/date/time];[customername]; [customeraddress/text];
[contractamountcomplete/currency]; [totalexpenses/currency-calculated];
[profit/percent-calculated]

---report link properties---
Master Field: [crewchief]
Child Field: [crewchief]

I hope this provides enough detail without too much extraneous infor to
offer some solutions or insight that may be causing the repeated dialog
box/forms.
Thanks so much!!!
Brandye

I am having trouble seeing your screen to know what the dialog boxes say
or
what your report's record source looks like.

--
Duane Hookom
MS Access MVP
--

Duane (or another expert),
I set up a form to collect beginning and ending dates according to MS
Office
Online "Create a form to enter report critieria". It worked! BUT, now I
am
prompted with like 12 dialog boxes before the report and subreport will
open.
Any suggestions???
Thanks in advance for your continued assistance!
Brandye

:

Don't use parameter prompts for criteria. Always allow your users to
enter
values into controls on forms.

--
Duane Hookom
MS Access MVP
--

I have created a report and a subreport each based on different
queries
with
'Between [Type beginning date] And [Type ending date] criteria
prompts.
When
I open the stand alone reports, I only get promted once for the
beginning
and
ending dates (as expected). However, when I open the report with the
subreport I get prompted many many times for the beginning and
ending
dates.
So many times that it is difficult to count. I have tried removing
the
criteria from the sub report via the underlying query thinking that
the
main
report's criteria would apply to these records as well, but it did
not
apply
to these records. Has anyone encountered this and come up with a
solution?
Any advice would help.
Thanks!!!

 

[baxter101]

Sorry for the delay in response, I was not prompted that I had a reply for
some reason... In any event,
Yes. In the properties of the report and the sub report I have the following:
Production Dialog.OpenDialog
Production Dialog.CloseDialog
So if I'm understanding you correctly, I delete both of these, then open the
dialog box form and then open the report. It seems to have worked!!! So,
trying to take away a lesson learned here...if you have a main report and a
sub report with the same dialog box form criteria to retrieve certain records,
you should not use the on open event properties because????
If you don't have time to educate, thanks so much for your help.
Brandye

Are you opening the form/dialog box with code in the On Open event of the
report? If so, don't.

Open your criteria form prior to opening your report. Enter your criteria
values and then open the report.

Ha! Good point... I was hoping that it was a common newby error. No such
luck. Ok. Here goes my attempt to summarize the details that may enable

[quoted text clipped - 95 lines]

 

[Duane Hookom]

I know there are legitimate samples of report code that open a dialog form
to collect criteria. I have never used this method and really don't like
placing dynamic criteria in a query.

Subreports quite often require using Forms!frmYourForm!txtYourText in the
record source query. Never use code in a subreport to open a dialog criteria
form since each instance of the subreport will be a new "open event".

--
Duane Hookom
MS Access MVP
--

Sorry for the delay in response, I was not prompted that I had a reply for
some reason... In any event,
Yes. In the properties of the report and the sub report I have the
following:
Production Dialog.OpenDialog
Production Dialog.CloseDialog
So if I'm understanding you correctly, I delete both of these, then open
the
dialog box form and then open the report. It seems to have worked!!! So,
trying to take away a lesson learned here...if you have a main report and
a
sub report with the same dialog box form criteria to retrieve certain
records,
you should not use the on open event properties because????
If you don't have time to educate, thanks so much for your help.
Brandye

Are you opening the form/dialog box with code in the On Open event of the
report? If so, don't.

Open your criteria form prior to opening your report. Enter your criteria
values and then open the report.

Ha! Good point... I was hoping that it was a common newby error. No such
luck. Ok. Here goes my attempt to summarize the details that may enable

[quoted text clipped - 95 lines]

Any advice would help.
Thanks!!!