subreport field does not exist

[LGarcia]

Hi all,
Background info: I have one front end db. I have 3 separate back ends that I
can link to. All 3 back ends are similar in structure and content. The
difference is BE#1 contains data for 10 products, BE#2 contains data for 21
products, BE#3 contains data for 32 products. I can't combine the data -
internal issues..not design.
I want to design one report with a subreport that will work with all 3
backends. I'll set the source for the subreport as tblProduct. tblProduct
exists in all 3 BEs - this table has 10 fields in BE#1, 21 fields in BE#2
and 32 fields in BE#3.
To start with I created a report with 7 subreports. Each subreport contains
5 fields. Sub1 contains product1, product2,...etc....up to product5. Sub2
contains product6, product7,...etc....up to product10. Same pattern for each
Sub except for Sub7 which contains only product31 & product32. Works fine
with BE#3.
However when I try to open the report when linked to BE#2, I get prompted to
enter values for product22..thru product25. I expected this. I have code
that hides Subs 6 & 7 when there is no data.
In design view the field for product22 has a control source of [product22].
However in BE#2 [product22] in tblProduct does not exist.
Is there a way to ignore these fields without removing them from the
subreport?
Hope someone can help!
TIA,
LGarcia

 

[Allen Browne]

There's a couple of issues here.

The first is handling the case where a subreport has no data. See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html
for details on how to test the HasData property of the report in the
subreport control, using an IIf() expresion to handle the case.

However, that issue fixes #Error on the report, where you are getting
parameter requests. This suggests you might have fields that don't exist in
the source query. If so, you will need to change the source query before
opening the report. If you do have fields that just "disappeared" in some
cases, you might be able to change the query to that they remain, e.g.:
SELECT SomeField, AnotherField, Null AS TheMissingFieldNameHere ...

If necessary, you can write the SQL property of the QueryDef before you
OpenReport:
Dim strSql As String
strSql = "SELECT ...
CurrentDb.QueryDefs("Query1").SQL = strSql

 

[LGarcia]

Thanks for the reply! It works! I was using the table as the source for the
sub reports but using a query works much better!

There's a couple of issues here.

The first is handling the case where a subreport has no data. See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html
for details on how to test the HasData property of the report in the
subreport control, using an IIf() expresion to handle the case.

However, that issue fixes #Error on the report, where you are getting
parameter requests. This suggests you might have fields that don't exist
in the source query. If so, you will need to change the source query
before opening the report. If you do have fields that just "disappeared"
in some cases, you might be able to change the query to that they remain,
e.g.:
SELECT SomeField, AnotherField, Null AS TheMissingFieldNameHere ...

If necessary, you can write the SQL property of the QueryDef before you
OpenReport:
Dim strSql As String
strSql = "SELECT ...
CurrentDb.QueryDefs("Query1").SQL = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,
Background info: I have one front end db. I have 3 separate back ends
that I can link to. All 3 back ends are similar in structure and content.
The difference is BE#1 contains data for 10 products, BE#2 contains data
for 21 products, BE#3 contains data for 32 products. I can't combine the
data - internal issues..not design.
I want to design one report with a subreport that will work with all 3
backends. I'll set the source for the subreport as tblProduct.
tblProduct exists in all 3 BEs - this table has 10 fields in BE#1, 21
fields in BE#2 and 32 fields in BE#3.
To start with I created a report with 7 subreports. Each subreport
contains 5 fields. Sub1 contains product1, product2,...etc....up to
product5. Sub2 contains product6, product7,...etc....up to product10.
Same pattern for each Sub except for Sub7 which contains only product31 &
product32. Works fine with BE#3.
However when I try to open the report when linked to BE#2, I get prompted
to enter values for product22..thru product25. I expected this. I have
code that hides Subs 6 & 7 when there is no data.
In design view the field for product22 has a control source of
[product22]. However in BE#2 [product22] in tblProduct does not exist.
Is there a way to ignore these fields without removing them from the
subreport?
Hope someone can help!
TIA,
LGarcia