subreport?

  • Thread starter Thread starter moca
  • Start date Start date
M

moca

I built a drug sales report in which i want to insert a subreport that
contains
the name of the drug, its identification number and its margin.
When I insert the subreport and run it, it asks me for the name of the
drug like it was a parameter. None of my queries on which I based my reports
have a paprameter value, so i would like to know, how is that possible since
the "name" field in the subreport is the same as the one in the main report?

Help me please !
 
moca said:
I built a drug sales report in which i want to insert a subreport that
contains
the name of the drug, its identification number and its margin.
When I insert the subreport and run it, it asks me for the name of the
drug like it was a parameter. None of my queries on which I based my reports
have a paprameter value, so i would like to know, how is that possible since
the "name" field in the subreport is the same as the one in the main report?
Click to expand...


Reports will prompt for any name in a control source
expression that it can't find.

The drug name field in the subreport must be in the
subreport's record source table/query. If you think it is,
then double check that you spelled it exactly the same way
in the text box as in the table/query.
 
I doublechecked but there is no problem with the spelling. I still can't
find why it is asking a value for the drug name field.

I am going to explain to you how I did everything and maybe there's
something wrong with how I did some things.

I created a query on which I based my 2012 Sales report , so the only
parameter in that query was in the refilldate field to specify the period of
time (between 1/1/2012 and 31/12/2012). The fields in this Report and query
are Name, UPN (drug code), Amount (a calculated field) and Refill date

The second query is only based on the table drug, with the following field
Names: Name, UPN and Margin (calculated field). And then I created a report
based on this query called 2012 Margin

So my last step was to use the last report (2012 Margin ) as a subreport for
the 2012 Sales Report by linking them with the "Name" field.

I don't know why it isn't working, maybe should I include the "refilldate"
field in the qry 2012 Margin and put the a parameter too like what I did for
the 2012 Sales qry?

I hope you see what I mean1 I am really desperate now I've tried everything
I could think of.

Thx
 
moca said:
I doublechecked but there is no problem with the spelling. I still can't
find why it is asking a value for the drug name field.

I am going to explain to you how I did everything and maybe there's
something wrong with how I did some things.

I created a query on which I based my 2012 Sales report , so the only
parameter in that query was in the refilldate field to specify the period of
time (between 1/1/2012 and 31/12/2012). The fields in this Report and query
are Name, UPN (drug code), Amount (a calculated field) and Refill date

The second query is only based on the table drug, with the following field
Names: Name, UPN and Margin (calculated field). And then I created a report
based on this query called 2012 Margin

So my last step was to use the last report (2012 Margin ) as a subreport for
the 2012 Sales Report by linking them with the "Name" field.

I don't know why it isn't working, maybe should I include the "refilldate"
field in the qry 2012 Margin and put the a parameter too like what I did for
the 2012 Sales qry?
Click to expand...


I can't see how adding a criteria would make a difference,
but you can try whatever you like. Sometimes we even learn
something from trying odd things ;-)

The popup prompt has the exact spelling the query/report
does not understand. Check it carefully, maybe you are not
looking in the right place (e,g, Sorting and Grouping) for
the offending name.

I'm getting a definite impression that you actually have a
field in a table that is named Name. Since that is probably
the name of the most common property in Access, I can
envision all kinds of things going wrong.

You really should not use built in names for your own
purposes (e,g, Name, Date, and many, many other common
wrods). You could try enclosing the field name in [ ] and
maybe(??) Access will realize its not one of its own items.
Best to change the name of the field to DrugName or
something similar.

Another thing you should try is opening the subreport
directly from the Database window (without using the main
report) to see if the main report's influence is part of the
issue.
 
Hey Marshall,

I finally found the problem, the name field was not the appropriate link
between the report and the subreport. I chose the drug code as the link and
it worked perfectly. I was just wondering, do you have any ideas why is
that ? My only guess is that the field "Name" is a built-in name so Access
got mixed up.

Thx for putting me on the right track.


Marshall Barton said:
moca said:
I doublechecked but there is no problem with the spelling. I still can't
find why it is asking a value for the drug name field.

I am going to explain to you how I did everything and maybe there's
something wrong with how I did some things.

I created a query on which I based my 2012 Sales report , so the only
parameter in that query was in the refilldate field to specify the period of
time (between 1/1/2012 and 31/12/2012). The fields in this Report and query
are Name, UPN (drug code), Amount (a calculated field) and Refill date

The second query is only based on the table drug, with the following field
Names: Name, UPN and Margin (calculated field). And then I created a report
based on this query called 2012 Margin

So my last step was to use the last report (2012 Margin ) as a subreport for
the 2012 Sales Report by linking them with the "Name" field.

I don't know why it isn't working, maybe should I include the "refilldate"
field in the qry 2012 Margin and put the a parameter too like what I did for
the 2012 Sales qry?
Click to expand...


I can't see how adding a criteria would make a difference,
but you can try whatever you like. Sometimes we even learn
something from trying odd things ;-)

The popup prompt has the exact spelling the query/report
does not understand. Check it carefully, maybe you are not
looking in the right place (e,g, Sorting and Grouping) for
the offending name.

I'm getting a definite impression that you actually have a
field in a table that is named Name. Since that is probably
the name of the most common property in Access, I can
envision all kinds of things going wrong.

You really should not use built in names for your own
purposes (e,g, Name, Date, and many, many other common
wrods). You could try enclosing the field name in [ ] and
maybe(??) Access will realize its not one of its own items.
Best to change the name of the field to DrugName or
something similar.

Another thing you should try is opening the subreport
directly from the Database window (without using the main
report) to see if the main report's influence is part of the
issue.
Click to expand...
 
The symptoms of a problem are not always what we expect.

Having an invalid LinkMaster/Child setting could very well
be the cause, but I haven't made that kind of mistake in a
long time so no I'm not sure.

Also, using built in names as names for your own things can
manifest in all kinds of strange ways. You may find this
page useful http://allenbrowne.com/AppIssueBadWord.html
--
Marsh
MVP [MS Access]

I finally found the problem, the name field was not the appropriate link
between the report and the subreport. I chose the drug code as the link and
it worked perfectly. I was just wondering, do you have any ideas why is
that ? My only guess is that the field "Name" is a built-in name so Access
got mixed up.


Marshall Barton said:
moca said:
I doublechecked but there is no problem with the spelling. I still can't
find why it is asking a value for the drug name field.

I am going to explain to you how I did everything and maybe there's
something wrong with how I did some things.

I created a query on which I based my 2012 Sales report , so the only
parameter in that query was in the refilldate field to specify the period of
time (between 1/1/2012 and 31/12/2012). The fields in this Report and query
are Name, UPN (drug code), Amount (a calculated field) and Refill date

The second query is only based on the table drug, with the following field
Names: Name, UPN and Margin (calculated field). And then I created a report
based on this query called 2012 Margin

So my last step was to use the last report (2012 Margin ) as a subreport for
the 2012 Sales Report by linking them with the "Name" field.

I don't know why it isn't working, maybe should I include the "refilldate"
field in the qry 2012 Margin and put the a parameter too like what I did for
the 2012 Sales qry?
Click to expand...


I can't see how adding a criteria would make a difference,
but you can try whatever you like. Sometimes we even learn
something from trying odd things ;-)

The popup prompt has the exact spelling the query/report
does not understand. Check it carefully, maybe you are not
looking in the right place (e,g, Sorting and Grouping) for
the offending name.

I'm getting a definite impression that you actually have a
field in a table that is named Name. Since that is probably
the name of the most common property in Access, I can
envision all kinds of things going wrong.

You really should not use built in names for your own
purposes (e,g, Name, Date, and many, many other common
wrods). You could try enclosing the field name in [ ] and
maybe(??) Access will realize its not one of its own items.
Best to change the name of the field to DrugName or
something similar.

Another thing you should try is opening the subreport
directly from the Database window (without using the main
report) to see if the main report's influence is part of the
issue.
Click to expand...
Click to expand...
 
Back
Top