Report asks for Parameter and Won't Display Subreport

M

MIG

I have a report which is fine, and I created another report to act as
subreport, which contains a field linked to the ID of the main
report. The subreport also displays fine when opened independently.

When I insert a subreport control in the main report and link the
master and child fields appropriately, the main report demands a
paramater value and, regardless of what is entered, will not display
the subreport. The parameter name is the name of the table that the
main report is based on, and is not a field that exists anywhere.

There is definitely no sorting or grouping or order by or any
reference to a field or parameter of that name.

I've tried creating simpler subreports, and anything I insert in the
main report has the same result: a parameter is demanded which is the
name of the main table (not a field) and no subreport will display.

Any ideas? It is driving me up the wall, because I am doing nothing
different from what I've ever done before.
 
M

MIG

I have a report which is fine, and I created another report to act as
subreport, which contains a field linked to the ID of the main
report.  The subreport also displays fine when opened independently.

When I insert a subreport control in the main report and link the
master and child fields appropriately, the main report demands a
paramater value and, regardless of what is entered, will not display
the subreport.  The parameter name is the name of the table that the
main report is based on, and is not a field that exists anywhere.

There is definitely no sorting or grouping or order by or any
reference to a field or parameter of that name.

I've tried creating simpler subreports, and anything I insert in the
main report has the same result: a parameter is demanded which is the
name of the main table (not a field) and no subreport will display.

Any ideas?  It is driving me up the wall, because I am doing nothing
different from what I've ever done before.

A slight update to this. The main report is based on a query of more
than one table.

For that reason, the key of the main report shows as [table name].
[key] in the field list.

As soon as I link a subreport, [table name] is taken as a parameter
value and the subreport won't display.

There has to be a way round this ...
 
M

MIG

I have a report which is fine, and I created another report to act as
subreport, which contains a field linked to the ID of the main
report.  The subreport also displays fine when opened independently.
When I insert a subreport control in the main report and link the
master and child fields appropriately, the main report demands a
paramater value and, regardless of what is entered, will not display
the subreport.  The parameter name is the name of the table that the
main report is based on, and is not a field that exists anywhere.
There is definitely no sorting or grouping or order by or any
reference to a field or parameter of that name.
I've tried creating simpler subreports, and anything I insert in the
main report has the same result: a parameter is demanded which is the
name of the main table (not a field) and no subreport will display.
Any ideas?  It is driving me up the wall, because I am doing nothing
different from what I've ever done before.

A slight update to this.  The main report is based on a query of more
than one table.

For that reason, the key of the main report shows as [table name].
[key] in the field list.

As soon as I link a subreport, [table name] is taken as a parameter
value and the subreport won't display.

There has to be a way round this ...


I think I may be able to get round it by selecting fields one by one,
instead of *, and then using an alias for the ID in order to avoid the
square brackets.

This has got to be a bug though. I shouldn't have to do this.
 
D

Douglas J Steele

I don't agree with you that it's a bug. You should never use SELECT *,
especially when your query joins multiple tables. How is Access supposed to
know which field to use if fields with the same name exist in more than one
table? You should always select the specific fields of interest to you, and
use appropriate aliases if a given name is not unique.

"MIG" wrote in message
A slight update to this. The main report is based on a query of more
than one table.

For that reason, the key of the main report shows as [table name].
[key] in the field list.

As soon as I link a subreport, [table name] is taken as a parameter
value and the subreport won't display.

There has to be a way round this ...


I think I may be able to get round it by selecting fields one by one,
instead of *, and then using an alias for the ID in order to avoid the
square brackets.

This has got to be a bug though. I shouldn't have to do this.
 
M

MIG

I don't see what's wrong with SELECT * if there are lots of fields and
no convenient way of spotting that you've missed one.

Access knows which field to use by putting [table name].[field name],
and many people recommend doing that even when there's no ambiguity.

The bug, as I would describe it, is that Access evidently can't
distinguish between a table name and a parameter.


I don't agree with you that it's a bug. You should never use SELECT *,
especially when your query joins multiple tables. How is Access supposed to
know which field to use if fields with the same name exist in more than one
table? You should always select the specific fields of interest to you, and
use appropriate aliases if a given name is not unique.

"MIG"  wrote in message

A slight update to this.  The main report is based on a query of more
than one table.
For that reason, the key of the main report shows as [table name].
[key] in the field list.
As soon as I link a subreport, [table name] is taken as a parameter
value and the subreport won't display.
There has to be a way round this ...

I think I may be able to get round it by selecting fields one by one,
instead of *, and then using an alias for the ID in order to avoid the
square brackets.

This has got to be a bug though.  I shouldn't have to do this.
 
D

Douglas J Steele

I believe the issue is that you've got more than one field with the same
name in the query, so Access disambiguates by using [tablename].[fieldname]
in the query. (Run the query by itself: you'll see that most of the fields
will have just the field name returned. Only the fields with duplicate names
will be returned as [tablename].[fieldname])

I'll grant that it's an inconsistency that Access is content with using
[tablename].[fieldname] for uniqueness in queries, but control sources can't
be compound like that. The solution is to use aliases in your query so that
each field has a unique name.

"MIG" wrote in message

I don't see what's wrong with SELECT * if there are lots of fields and
no convenient way of spotting that you've missed one.

Access knows which field to use by putting [table name].[field name],
and many people recommend doing that even when there's no ambiguity.

The bug, as I would describe it, is that Access evidently can't
distinguish between a table name and a parameter.


I don't agree with you that it's a bug. You should never use SELECT *,
especially when your query joins multiple tables. How is Access supposed
to
know which field to use if fields with the same name exist in more than
one
table? You should always select the specific fields of interest to you,
and
use appropriate aliases if a given name is not unique.

"MIG" wrote in message

A slight update to this. The main report is based on a query of more
than one table.
For that reason, the key of the main report shows as [table name].
[key] in the field list.
As soon as I link a subreport, [table name] is taken as a parameter
value and the subreport won't display.
There has to be a way round this ...

I think I may be able to get round it by selecting fields one by one,
instead of *, and then using an alias for the ID in order to avoid the
square brackets.

This has got to be a bug though. I shouldn't have to do this.
 
M

MIG

Yes indeed; using an alias has fixed the problem, but there is
something a bit odd about the way that Access takes it upon itself to
use a form of notation which it then can't interpret in certain
situations.

Before I linked the subreport, there was no problem with the control
source for the key in the main report, which was specified as [table
name].[field name].

It was only when the field was used as a master field for the
subreport relationship that Access treated it as a parameter.

That's why I called it a bug. If it was simply an illegal form of
notation, it wouldn't work when there was no subreport.


I believe the issue is that you've got more than one field with the same
name in the query, so Access disambiguates by using [tablename].[fieldname]
in the query. (Run the query by itself: you'll see that most of the fields
will have just the field name returned. Only the fields with duplicate names
will be returned as [tablename].[fieldname])

I'll grant that it's an inconsistency that Access is content with using
[tablename].[fieldname] for uniqueness in queries, but control sources can't
be compound like that. The solution is to use aliases in your query so that
each field has a unique name.

"MIG"  wrote in message


I don't see what's wrong with SELECT * if there are lots of fields and
no convenient way of spotting that you've missed one.

Access knows which field to use by putting [table name].[field name],
and many people recommend doing that even when there's no ambiguity.

The bug, as I would describe it, is that Access evidently can't
distinguish between a table name and a parameter.

I don't agree with you that it's a bug. You should never use SELECT *,
especially when your query joins multiple tables. How is Access supposed
to
know which field to use if fields with the same name exist in more than
one
table? You should always select the specific fields of interest to you,
and
use appropriate aliases if a given name is not unique.
"MIG"  wrote in message
A slight update to this.  The main report is based on a query of more
than one table.
For that reason, the key of the main report shows as [table name].
[key] in the field list.
As soon as I link a subreport, [table name] is taken as a parameter
value and the subreport won't display.
There has to be a way round this ...
I think I may be able to get round it by selecting fields one by one,
instead of *, and then using an alias for the ID in order to avoid the
square brackets.
This has got to be a bug though.  I shouldn't have to do this.- Hide quoted text -

- Show quoted text -
 
P

phil hunt

Access had this issue since day 1. If you have a control on a form that has
the same name as a db field, you have a very confusing error.


Yes indeed; using an alias has fixed the problem, but there is
something a bit odd about the way that Access takes it upon itself to
use a form of notation which it then can't interpret in certain
situations.

Before I linked the subreport, there was no problem with the control
source for the key in the main report, which was specified as [table
name].[field name].

It was only when the field was used as a master field for the
subreport relationship that Access treated it as a parameter.

That's why I called it a bug. If it was simply an illegal form of
notation, it wouldn't work when there was no subreport.


I believe the issue is that you've got more than one field with the same
name in the query, so Access disambiguates by using
[tablename].[fieldname]
in the query. (Run the query by itself: you'll see that most of the fields
will have just the field name returned. Only the fields with duplicate
names
will be returned as [tablename].[fieldname])

I'll grant that it's an inconsistency that Access is content with using
[tablename].[fieldname] for uniqueness in queries, but control sources
can't
be compound like that. The solution is to use aliases in your query so
that
each field has a unique name.

in message


I don't see what's wrong with SELECT * if there are lots of fields and
no convenient way of spotting that you've missed one.

Access knows which field to use by putting [table name].[field name],
and many people recommend doing that even when there's no ambiguity.

The bug, as I would describe it, is that Access evidently can't
distinguish between a table name and a parameter.

I don't agree with you that it's a bug. You should never use SELECT *,
especially when your query joins multiple tables. How is Access supposed
to
know which field to use if fields with the same name exist in more than
one
table? You should always select the specific fields of interest to you,
and
use appropriate aliases if a given name is not unique.
A slight update to this. The main report is based on a query of more
than one table.
For that reason, the key of the main report shows as [table name].
[key] in the field list.
As soon as I link a subreport, [table name] is taken as a parameter
value and the subreport won't display.
There has to be a way round this ...
I think I may be able to get round it by selecting fields one by one,
instead of *, and then using an alias for the ID in order to avoid the
square brackets.
This has got to be a bug though. I shouldn't have to do this.- Hide
quoted text -

- Show quoted text -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top