By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 733 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,241 IT Pros & Developers. It's quick & easy.

unexpected prompt in report

P: n/a
I have a report that lists File-B. My macro runs this
report using a query as a filter. The query uses two files,
the parent File-B and with a 0-many relationship to File-C.
The query selects for a specific field value on one field in
File-C.

The query works fine alone and the report is fine if run by
itself. However when I run the macro using the query as a
filter I get a parameter prompt to enter a value for the
field in File-C.

Any comments on this? I am at a loss on how to solve this
without creating a new report based on both files.

Thanks
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Br
David Horsman <ma************@MUNGEtelus.net> wrote:
I have a report that lists File-B. My macro runs this
report using a query as a filter. The query uses two files,
the parent File-B and with a 0-many relationship to File-C.
The query selects for a specific field value on one field in
File-C.
So by file you mean table :)
The query works fine alone and the report is fine if run by
itself. However when I run the macro using the query as a
filter I get a parameter prompt to enter a value for the
field in File-C.
You can't use the query as the filter. A filter is something else
entirely... (check the help)
Any comments on this? I am at a loss on how to solve this
without creating a new report based on both files.

Thanks


--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #2

P: n/a
Rog
David,

Unless I misunderstand what you're trying to do you need use your query
as the report's record source, not as a filter.

Rog

Nov 13 '05 #3

P: n/a
Hi Bradley,

I am doing an OpenReport in the macro with the Query in the
Filter Name property/attribute/field/column ;-) From the
documentation, this seems to be the correct way to allow
different record sources for the same report but I may be
well be incorrect.

Seems to work in every case except where the Query has a
child table and the report only uses the parent table. I
suspect this is problem and that I need to set some other
properties, use the WHERE expression of the macro
(correctly) or that I simply cannot feed an inner join to a
report that does not require the child table.

I used the term filter in a quite misleading way in that I
am not using the WHERE property nor filters within the
report. My Query is the record source for the report
Excuse the confusion.

Regards, Dave

"Br@dley" <n0****@4u.com> wrote in message
news:ez*****************@news-server.bigpond.net.au...
David Horsman <ma************@MUNGEtelus.net> wrote:
I have a report that lists File-B. My macro runs this
report using a query as a filter. The query uses two
files,
the parent File-B and with a 0-many relationship to
File-C.
The query selects for a specific field value on one field
in
File-C.


So by file you mean table :)
The query works fine alone and the report is fine if run
by
itself. However when I run the macro using the query as
a
filter I get a parameter prompt to enter a value for the
field in File-C.


You can't use the query as the filter. A filter is
something else entirely... (check the help)
Any comments on this? I am at a loss on how to solve
this
without creating a new report based on both files.

Thanks


--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

Nov 13 '05 #4

P: n/a
Br
The "help" says...

"the query must include all the fields in the report you are opening or
have its OutputAllFields property set to Yes"

David Horsman <ma************@MUNGEtelus.net> wrote:
Hi Bradley,

I am doing an OpenReport in the macro with the Query in the
Filter Name property/attribute/field/column ;-) From the
documentation, this seems to be the correct way to allow
different record sources for the same report but I may be
well be incorrect.

Seems to work in every case except where the Query has a
child table and the report only uses the parent table. I
suspect this is problem and that I need to set some other
properties, use the WHERE expression of the macro
(correctly) or that I simply cannot feed an inner join to a
report that does not require the child table.

I used the term filter in a quite misleading way in that I
am not using the WHERE property nor filters within the
report. My Query is the record source for the report
Excuse the confusion.

Regards, Dave

"Br@dley" <n0****@4u.com> wrote in message
news:ez*****************@news-server.bigpond.net.au...
David Horsman <ma************@MUNGEtelus.net> wrote:
I have a report that lists File-B. My macro runs this
report using a query as a filter. The query uses two
files,
the parent File-B and with a 0-many relationship to
File-C.
The query selects for a specific field value on one field
in
File-C.


So by file you mean table :)
The query works fine alone and the report is fine if run
by
itself. However when I run the macro using the query as
a
filter I get a parameter prompt to enter a value for the
field in File-C.


You can't use the query as the filter. A filter is
something else entirely... (check the help)
Any comments on this? I am at a loss on how to solve
this
without creating a new report based on both files.

Thanks


--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response


--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #5

P: n/a
Thanks Bradley, I did indeed read the help and carefully
checked that but I will repeat the process and more
carefully check the report and query again. I thought that
was the problem but could not find any discrepancies.
Regards, Dave H.
"Br@dley" <n0****@4u.com> wrote in message
news:SZ******************@news-server.bigpond.net.au...
The "help" says...

"the query must include all the fields in the report you
are opening or have its OutputAllFields property set to
Yes"

David Horsman <ma************@MUNGEtelus.net> wrote:
Hi Bradley,

I am doing an OpenReport in the macro with the Query in
the
Filter Name property/attribute/field/column ;-) From the
documentation, this seems to be the correct way to allow
different record sources for the same report but I may be
well be incorrect.

Seems to work in every case except where the Query has a
child table and the report only uses the parent table. I
suspect this is problem and that I need to set some other
properties, use the WHERE expression of the macro
(correctly) or that I simply cannot feed an inner join to
a
report that does not require the child table.

I used the term filter in a quite misleading way in that
I
am not using the WHERE property nor filters within the
report. My Query is the record source for the report
Excuse the confusion.

Regards, Dave

"Br@dley" <n0****@4u.com> wrote in message
news:ez*****************@news-server.bigpond.net.au...
David Horsman <ma************@MUNGEtelus.net> wrote:
I have a report that lists File-B. My macro runs this
report using a query as a filter. The query uses two
files,
the parent File-B and with a 0-many relationship to
File-C.
The query selects for a specific field value on one
field
in
File-C.

So by file you mean table :)

The query works fine alone and the report is fine if
run
by
itself. However when I run the macro using the query
as
a
filter I get a parameter prompt to enter a value for
the
field in File-C.

You can't use the query as the filter. A filter is
something else entirely... (check the help)

Any comments on this? I am at a loss on how to solve
this
without creating a new report based on both files.

Thanks

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response


--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

Nov 13 '05 #6

P: n/a
Thanks Bradley, I did indeed read the help and carefully
checked that but I will repeat the process and more
carefully check the report and query again. I thought that
was the problem but could not find any discrepancies.
Regards, Dave H.
"Br@dley" <n0****@4u.com> wrote in message
news:SZ******************@news-server.bigpond.net.au...
The "help" says...

"the query must include all the fields in the report you
are opening or have its OutputAllFields property set to
Yes"

David Horsman <ma************@MUNGEtelus.net> wrote:
Hi Bradley,

I am doing an OpenReport in the macro with the Query in
the
Filter Name property/attribute/field/column ;-) From the
documentation, this seems to be the correct way to allow
different record sources for the same report but I may be
well be incorrect.

Seems to work in every case except where the Query has a
child table and the report only uses the parent table. I
suspect this is problem and that I need to set some other
properties, use the WHERE expression of the macro
(correctly) or that I simply cannot feed an inner join to
a
report that does not require the child table.

I used the term filter in a quite misleading way in that
I
am not using the WHERE property nor filters within the
report. My Query is the record source for the report
Excuse the confusion.

Regards, Dave

"Br@dley" <n0****@4u.com> wrote in message
news:ez*****************@news-server.bigpond.net.au...
David Horsman <ma************@MUNGEtelus.net> wrote:
I have a report that lists File-B. My macro runs this
report using a query as a filter. The query uses two
files,
the parent File-B and with a 0-many relationship to
File-C.
The query selects for a specific field value on one
field
in
File-C.

So by file you mean table :)

The query works fine alone and the report is fine if
run
by
itself. However when I run the macro using the query
as
a
filter I get a parameter prompt to enter a value for
the
field in File-C.

You can't use the query as the filter. A filter is
something else entirely... (check the help)

Any comments on this? I am at a loss on how to solve
this
without creating a new report based on both files.

Thanks

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response


--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

Nov 13 '05 #7

P: n/a
Hi Group,

Although I can not locate the reference in the help files,
the problem was related to the RecordSource in the REPORT.
I had to change this to USE A default QUERY (NOT A TABLE)
that included the child relationship. Having done this I
could then run the report using the other queries that had
the same parent child relationships.

I had tried changing the RecordSource from the macro (a
no-no once the report is open). My goal was to avoid
creating a number of duplicate reports for various selection
criteria on the child table. This was a fair compromise in
the end because there were only a few default (no selection
criteria or prompted) queries to create for several reports.

The moral here is to not expect Access to be completely
flexible towards sloppy database design and of course, don't
ignore the obvious when matching source data to output. On
the other hand, it took for ever to find the help reference
stating that the query had to match the report in terms of
tables. My error was in thinking that having all of the
fields present in the query that appeared in the report, I
could get away with having additional fields present in the
query as a child table relationship.

I am still wondering if I am not mistaken about this. I
would welcome correction on any of the above comments.

Regards, Dave H

"David Horsman" <ma************@MUNGEtelus.net> wrote in
message news:50HAe.148583$on1.64662@clgrps13...
I have a report that lists File-B. My macro runs this
report using a query as a filter. The query uses two
files, the parent File-B and with a 0-many relationship to
File-C. The query selects for a specific field value on one
field in File-C.

The query works fine alone and the report is fine if run
by itself. However when I run the macro using the query
as a filter I get a parameter prompt to enter a value for
the field in File-C.

Any comments on this? I am at a loss on how to solve this
without creating a new report based on both files.

Thanks

Nov 13 '05 #8

P: n/a
Hi Group,

Although I can not locate the reference in the help files,
the problem was related to the RecordSource in the REPORT.
I had to change this to USE A default QUERY (NOT A TABLE)
that included the child relationship. Having done this I
could then run the report using the other queries that had
the same parent child relationships.

I had tried changing the RecordSource from the macro (a
no-no once the report is open). My goal was to avoid
creating a number of duplicate reports for various selection
criteria on the child table. This was a fair compromise in
the end because there were only a few default (no selection
criteria or prompted) queries to create for several reports.

The moral here is to not expect Access to be completely
flexible towards sloppy database design and of course, don't
ignore the obvious when matching source data to output. On
the other hand, it took for ever to find the help reference
stating that the query had to match the report in terms of
tables. My error was in thinking that having all of the
fields present in the query that appeared in the report, I
could get away with having additional fields present in the
query as a child table relationship.

I am still wondering if I am not mistaken about this. I
would welcome correction on any of the above comments.

Regards, Dave H

"David Horsman" <ma************@MUNGEtelus.net> wrote in
message news:50HAe.148583$on1.64662@clgrps13...
I have a report that lists File-B. My macro runs this
report using a query as a filter. The query uses two
files, the parent File-B and with a 0-many relationship to
File-C. The query selects for a specific field value on one
field in File-C.

The query works fine alone and the report is fine if run
by itself. However when I run the macro using the query
as a filter I get a parameter prompt to enter a value for
the field in File-C.

Any comments on this? I am at a loss on how to solve this
without creating a new report based on both files.

Thanks

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.