Subreports behaving badly...

[Mike]

I have a master report that contains invoice header information and a
subreport that contains invoice details. The subreport works fine when run
by itself but when inserted in the master (invoice header), it displays two
instances of the subreport. This is being inserted in the detail section of
the master. Any ideas why this is happening?

Thanks, Mike

 

[Duane Hookom]

I expect you have duplicate InvoiceID field values in your header record
source.

 

[Mike]

I'm not finding that situation in the header record source (good suggestion
though!). The table only has one primary key and the query returns only one
row. Don't know if this will help but here's the query for the header:

SELECT DISTINCT dbo_customer.FULLNAME, dbo_customer.ADDR1,
dbo_customer.ADDR2, dbo_customer.VILL, dbo_customer.STATE, dbo_customer.ZZP,
dbo_HISTORY.SINV, dbo_HISTORY.BKG, dbo_HISTORY.CENT, dbo_HISTORY.CVIFLOC,
tblSaleType.SaleType, dbo_HISTORY.PONUM, dbo_customer.QDLIC,
dbo_customer.FEDLIC, dbo_customer.EIN, dbo_VILLAGE.VNAM, dbo_HISTORY.VOYNUM,
dbo_HISTORY.DINV, dbo_HISTORY.VESSEL
FROM tblSaleType INNER JOIN ((dbo_customer INNER JOIN dbo_HISTORY ON
dbo_customer.CVIFLOC = dbo_HISTORY.CVIFLOC) INNER JOIN dbo_VILLAGE ON
dbo_customer.VCOD = dbo_VILLAGE.VCOD) ON tblSaleType.SaleTypeCode =
dbo_HISTORY.CACH
WHERE (((dbo_HISTORY.SINV)=[Forms]![frmMain]![txtInvoiceNumber]));

 

[Duane Hookom]

Are you suggesting that when you view the record source of the report in
datasheet view, you see only one record but your subreport duplicates?

Have you set the link master/child properties of the subreport control?

Do both instances of the subreport contain the exact same record?

--
Duane Hookom
MS Access MVP

I'm not finding that situation in the header record source (good
suggestion
though!). The table only has one primary key and the query returns only
one
row. Don't know if this will help but here's the query for the header:

SELECT DISTINCT dbo_customer.FULLNAME, dbo_customer.ADDR1,
dbo_customer.ADDR2, dbo_customer.VILL, dbo_customer.STATE,
dbo_customer.ZZP,
dbo_HISTORY.SINV, dbo_HISTORY.BKG, dbo_HISTORY.CENT, dbo_HISTORY.CVIFLOC,
tblSaleType.SaleType, dbo_HISTORY.PONUM, dbo_customer.QDLIC,
dbo_customer.FEDLIC, dbo_customer.EIN, dbo_VILLAGE.VNAM,
dbo_HISTORY.VOYNUM,
dbo_HISTORY.DINV, dbo_HISTORY.VESSEL
FROM tblSaleType INNER JOIN ((dbo_customer INNER JOIN dbo_HISTORY ON
dbo_customer.CVIFLOC = dbo_HISTORY.CVIFLOC) INNER JOIN dbo_VILLAGE ON
dbo_customer.VCOD = dbo_VILLAGE.VCOD) ON tblSaleType.SaleTypeCode =
dbo_HISTORY.CACH
WHERE (((dbo_HISTORY.SINV)=[Forms]![frmMain]![txtInvoiceNumber]));

I expect you have duplicate InvoiceID field values in your header record
source.

 

[Mike]

Hey Duane...my answers are between your questions:

Are you suggesting that when you view the record source of the report in
datasheet view, you see only one record but your subreport duplicates?
Yes. But I think I'm getting two subreports not just duplicate rows.
Have you set the link master/child properties of the subreport control?
I have set them and removed them with the same result.
Do both instances of the subreport contain the exact same record?
Exact same record (or records depending on the invoice I'm inquiring on). The subreport has a frame around it and the frame is appearing twice with the same row(s) in each frame. This is why I think there are two instances of the subreport and not just a doubling of the rows.
--
Duane Hookom
MS Access MVP

I'm not finding that situation in the header record source (good
suggestion
though!). The table only has one primary key and the query returns only
one
row. Don't know if this will help but here's the query for the header:

SELECT DISTINCT dbo_customer.FULLNAME, dbo_customer.ADDR1,
dbo_customer.ADDR2, dbo_customer.VILL, dbo_customer.STATE,
dbo_customer.ZZP,
dbo_HISTORY.SINV, dbo_HISTORY.BKG, dbo_HISTORY.CENT, dbo_HISTORY.CVIFLOC,
tblSaleType.SaleType, dbo_HISTORY.PONUM, dbo_customer.QDLIC,
dbo_customer.FEDLIC, dbo_customer.EIN, dbo_VILLAGE.VNAM,
dbo_HISTORY.VOYNUM,
dbo_HISTORY.DINV, dbo_HISTORY.VESSEL
FROM tblSaleType INNER JOIN ((dbo_customer INNER JOIN dbo_HISTORY ON
dbo_customer.CVIFLOC = dbo_HISTORY.CVIFLOC) INNER JOIN dbo_VILLAGE ON
dbo_customer.VCOD = dbo_VILLAGE.VCOD) ON tblSaleType.SaleTypeCode =
dbo_HISTORY.CACH
WHERE (((dbo_HISTORY.SINV)=[Forms]![frmMain]![txtInvoiceNumber]));

I expect you have duplicate InvoiceID field values in your header record
source.

--
Duane Hookom
MS Access MVP


I have a master report that contains invoice header information and a
subreport that contains invoice details. The subreport works fine when
run
by itself but when inserted in the master (invoice header), it displays
two
instances of the subreport. This is being inserted in the detail
section
of
the master. Any ideas why this is happening?

Thanks, Mike

 

[Duane Hookom]

If you are getting two instances then I think you either have two invoice
records or you have two copies of the same subreport.

--
Duane Hookom
MS Access MVP

Hey Duane...my answers are between your questions:

Are you suggesting that when you view the record source of the report in
datasheet view, you see only one record but your subreport duplicates?
Yes. But I think I'm getting two subreports not just duplicate rows.
Have you set the link master/child properties of the subreport control?
I have set them and removed them with the same result.
Do both instances of the subreport contain the exact same record?
Exact same record (or records depending on the invoice I'm inquiring
on). The subreport has a frame around it and the frame is appearing
twice with the same row(s) in each frame. This is why I think there are
two instances of the subreport and not just a doubling of the rows.
--
Duane Hookom
MS Access MVP

I'm not finding that situation in the header record source (good
suggestion
though!). The table only has one primary key and the query returns
only
one
row. Don't know if this will help but here's the query for the header:

SELECT DISTINCT dbo_customer.FULLNAME, dbo_customer.ADDR1,
dbo_customer.ADDR2, dbo_customer.VILL, dbo_customer.STATE,
dbo_customer.ZZP,
dbo_HISTORY.SINV, dbo_HISTORY.BKG, dbo_HISTORY.CENT,
dbo_HISTORY.CVIFLOC,
tblSaleType.SaleType, dbo_HISTORY.PONUM, dbo_customer.QDLIC,
dbo_customer.FEDLIC, dbo_customer.EIN, dbo_VILLAGE.VNAM,
dbo_HISTORY.VOYNUM,
dbo_HISTORY.DINV, dbo_HISTORY.VESSEL
FROM tblSaleType INNER JOIN ((dbo_customer INNER JOIN dbo_HISTORY ON
dbo_customer.CVIFLOC = dbo_HISTORY.CVIFLOC) INNER JOIN dbo_VILLAGE ON
dbo_customer.VCOD = dbo_VILLAGE.VCOD) ON tblSaleType.SaleTypeCode =
dbo_HISTORY.CACH
WHERE (((dbo_HISTORY.SINV)=[Forms]![frmMain]![txtInvoiceNumber]));


:

I expect you have duplicate InvoiceID field values in your header
record
source.

--
Duane Hookom
MS Access MVP


I have a master report that contains invoice header information and a
subreport that contains invoice details. The subreport works fine
when
run
by itself but when inserted in the master (invoice header), it
displays
two
instances of the subreport. This is being inserted in the detail
section
of
the master. Any ideas why this is happening?

Thanks, Mike