subreport field does not exist

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LGarcia - 06 Dec 2006 00:35 GMT

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

Reply to this Message

Allen Browne - 06 Dec 2006 01:12 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
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
[quoted text clipped - 21 lines]
> TIA,
> LGarcia

Reply to this Message

LGarcia - 06 Dec 2006 15:13 GMT

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.
>
[quoted text clipped - 44 lines]
>> TIA,
>> LGarcia

Reply to this Message