Solved Split Report and Attachments (1 Viewer)

[JoséBob]

Hello,

I would like to create a report with the attachments of my database. But I would like to have the attachments at the end of the report instead of having them together with the records they are linked to. This is to ease the reading of the report as the attachment will be full page and the records will be listed as a table beforehand.

I have several questions:

• How can I split the subreports? i.e. Can I have one subreport and then the second subreport and keep there formatting (esp. the page heading). How can I “group” the attachment at the end?
• How can I get access to show all attachment and not only the first one in two or more instances.

The attachments are added with the “attachment” field of Access (post 2010 I think). There are picture files only (no pdf). I tried already to use sub-reports, but Access keeps on putting the pictures with the records and not at the end. I tried to remove the link to master and child but that gives me a 1300 pages report (instead of ca. 65). I tried to put the sub-report of the attachment in the footer of the report and that shows only one records and its two attachments (although it shows only one picture). The attachment sub report is based on a query that gets only the records with attachments.

I have tried a few things without success. I can’t find any good answers on the forums, thus my question.

Thanks for your help

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[Pat Hartman]

create an unbound main report. Add the first subreport that shows the bound text data. Add the second subreport that shows the attachments.

Or, if the user doesn't always want to see/print the attachments, then use two separate reports and let the user choose what he wants to see.

 

[JoséBob]

create an unbound main report. Add the first subreport that shows the bound text data. Add the second subreport that shows the attachments.

Or, if the user doesn't always want to see/print the attachments, then use two separate reports and let the user choose what he wants to see.

Thanks. That works indeed.
I still have the issue getting the page report of the table on each page. I have the “show header[…]” property to yes. I have also tried the group-trick but that puts the header on each line instead on on top of the pages. Am I missing a parameter ?

 

[Pat Hartman]

I can't visualize the problem. Can you post a picture with markups?

 

[JoséBob]

Hey. You'll find a screenshots attached/below.
this one shows the structure of the report. it is unbound and there are two subreports as discussed earlier.
The first subreport is a table (only header shown btw - the table is in the "detail" of the report). iIt can be a long table and thus I would like to have the heading on top of each page. The second one have the attachements with the name and pictures.
In this instance, you'll see that the header is in the "=1" group. That puts the header only on the top of the first page and not on the others. If I put the header in the "page header" part, it doesn't help.
I put the properties on purpose.
I hope that helps visualise the issue. If not, let me know.

 

[Pat Hartman]

iIt can be a long table and thus I would like to have the heading on top of each page.

There is nothing in your page header. That is where the heading needs to go if you want it to repeat at the top of each page.

 

[JoséBob]

Hei.
do you mean the page header of the main report or the page header of the subreport?
in case I put the column header in the main report page header, it will appear on the pages with the pictures (which would look strange). If I put the column header in the page header of the subreport, it doesn’t appear on all pages but only on the first one.

 

[mike60smart]

Can you upload a zipped copy of the database?

 

[Pat Hartman]

If I put the column header in the page header of the subreport, it doesn’t appear on all pages but only on the first one.

You might need to create a group and mark the group to repeat on a page break.

 

[mike60smart]

If you put the Column Headers in the Report Header then they would appear on all pages.

 

[Pat Hartman]

The Report Header only appears on page 1 of the report. Are you saying that the subreport's Report Header appears on every page of the main report?

 

[JoséBob]

The Report Header only appears on page 1 of the report. Are you saying that the subreport's Report Header appears on every page of the main report?

Yes.

 

[JoséBob]

If you put the Column Headers in the Report Header then they would appear on all pages.

Yes.

 

[JoséBob]

Hei. Here is a lite version of my db. It is actually my test version. It is only partly translated but I don't think it'll matter a lot.
The report causing me the most trouble is the report "ReportWAt". The others are quite ok for me (for now).
Any comments for improvements are welcome.

 

[Pat Hartman]

I guess you didn't bother to try my earlier suggestion to add a group header to the subreport. Here it is

 

[JoséBob]

I guess you didn't bother to try my earlier suggestion to add a group header to the subreport. Here it is

Hello Pat,
Thanks for the help.
I am confused because I tried the to use the group header as shown on my post from Wednesday. I found this trick on several forum: creating a group with an expression "=1", but it didn't work. Why yours worked?
Additionally, would you happen to know why only one picture is shown in the report although there are two different attachments?

 

[Pat Hartman]

I changed the recordsource of the form to a query. I added a dummy group field to the query since I couldn't find a logical group in the data. It always has the same value because you don't actually have a group. Then I used the sorting/grouping options to group on that field and moved the headers to the group header.

I sorted the query by autonumber but you should probably change the report to add sorting options. Reports ALWAYS need to be sorted by something and since Access rewrites your RecordSource and doesn't include your order by, you always need to use the report options to specify sort sequence. Both subforms need to be sorted on the same fields I would imagine.

When I paged through the report, I saw two images.

Opening the report in report view rather than print preview can alter how the report is rendered.

 

[JoséBob]

I changed the recordsource of the form to a query. I added a dummy group field to the query since I couldn't find a logical group in the data. It always has the same value because you don't actually have a group. Then I used the sorting/grouping options to group on that field and moved the headers to the group header.

I sorted the query by autonumber but you should probably change the report to add sorting options. Reports ALWAYS need to be sorted by something and since Access rewrites your RecordSource and doesn't include your order by, you always need to use the report options to specify sort sequence. Both subforms need to be sorted on the same fields I would imagine.

When I paged through the report, I saw two images.

Opening the report in report view rather than print preview can alter how the report is rendered.

Hei Pat,
Thanks for the explanation. It is indeed clearer.
about the images, I also tried the print preview. If you look in detail, you’ll see that for the same record number (on top of the page) there is the same picture twice although there are two different in the database.

 

[Pat Hartman]

I wasn't trying to match them. I just scrolled through the records. You should add a page header to this subform also. Include the data needed to tie a picture to each item.

To troubleshoot, open the RecordSource query directly. Do you see two images for each item? If so, make sure that there is only ONE table being selected from. It is usually an invalid join that causes "duplication"

I'm playing bridge at the moment and so can't just look myself.