How to have a report with more than 2 pages? (1 Viewer)

[Pat Hartman]

Actually, the 4 pages I've mentioned is for the SINGLE record.

We all knew that.

There is a way to get the visual you want with a normalized schema. I spent way too long putting this sample together. Hopefully, it is fleshed out enough so you can see the path.
I built a new schema that will allow you to add new groups and questions as well as control the order they appear in the lists. tblQuestions was built from your column names. I can't promise that I collected all the individual attributes but those I gathered ended up in tblIncident. Some of the tables in tblQuestions will end up as option groups and others will be subforms depending on whether they are mutually exclusive (option group/list/combo) or multi-select (subform). I don't have a final thought on what to do with the "other" text comments but that can wait until you rebuild your app in my image.

You spent a huge amount of time on your presentation and boxed yourself into a corner. Stop now, back up, fix the schema, rebuild the forms/reports. You will be ever so much happier going forward. I've saved you from having to write thousands of lines of code and create hundreds of queries to analyze the results.

The form presentation should be tabs which eliminates the bulk of the scrolling. People find scrolling a massive, single record form to be distracting and annoying. Tabs allow you to solve that by stacking subforms.

Look at:
1. The schema - tblQuestions is the answer to your problems
2. frmQuestions- This is pretty complete. You can add groups and you can add questions to a group. The sequence number is automatically generated. If you later want to reorder the list, change a sequence number - 80 to 45 to move the item at 80 to between 40 and 50. Then renumber to clean up the sequence number if you want.
3. frmIncidentDesc- has only a few fields. Many of the fields will end up on tabs. You only want to keep info that you always need to see on the top part of the main form. I created a tab with 2 pages, you will obviously need more. I also created only one subform - sfrmUnsafeConditions
4. In the AfterInsert event of frmIncidentDesc, the code runs an append query that copies all the rows from tblQuestions to the junction table tblIncidentQuesAnswer table. This allows each incident to have a place to store the answers to your questions. I didn't do much formatting. Yours is very nice. If you can't get the sfrm to be formatted to your satisfaction, create a continuous version since that view has better formatting options.

 

[waleedimtiaz]

We all knew that.

There is a way to get the visual you want with a normalized schema. I spent way too long putting this sample together. Hopefully, it is fleshed out enough so you can see the path.
I built a new schema that will allow you to add new groups and questions as well as control the order they appear in the lists. tblQuestions was built from your column names. I can't promise that I collected all the individual attributes but those I gathered ended up in tblIncident. Some of the tables in tblQuestions will end up as option groups and others will be subforms depending on whether they are mutually exclusive (option group/list/combo) or multi-select (subform). I don't have a final thought on what to do with the "other" text comments but that can wait until you rebuild your app in my image.

You spent a huge amount of time on your presentation and boxed yourself into a corner. Stop now, back up, fix the schema, rebuild the forms/reports. You will be ever so much happier going forward. I've saved you from having to write thousands of lines of code and create hundreds of queries to analyze the results.

The form presentation should be tabs which eliminates the bulk of the scrolling. People find scrolling a massive, single record form to be distracting and annoying. Tabs allow you to solve that by stacking subforms.

Look at:
1. The schema - tblQuestions is the answer to your problems
2. frmQuestions- This is pretty complete. You can add groups and you can add questions to a group. The sequence number is automatically generated. If you later want to reorder the list, change a sequence number - 80 to 45 to move the item at 80 to between 40 and 50. Then renumber to clean up the sequence number if you want.
3. frmIncidentDesc- has only a few fields. Many of the fields will end up on tabs. You only want to keep info that you always need to see on the top part of the main form. I created a tab with 2 pages, you will obviously need more. I also created only one subform - sfrmUnsafeConditions
4. In the AfterInsert event of frmIncidentDesc, the code runs an append query that copies all the rows from tblQuestions to the junction table tblIncidentQuesAnswer table. This allows each incident to have a place to store the answers to your questions. I didn't do much formatting. Yours is very nice. If you can't get the sfrm to be formatted to your satisfaction, create a continuous version since that view has better formatting options.
View attachment 106448

Thank you so much for this effort. Appreciated big time!

 

[Pat Hartman]

You're welcome. Please take some time to look at it and come back with questions. It is very different from your current approach but it is infinitely more flexible and will make analysis of the data significantly easier. As I said, it is a start. I did what I could with limited knowledge of where you are going with this.

 

[spaLOGICng]

Hey everyone, I am a beginner in MS Access. I want to make a report with around 4-5 pages, but Access limits me to make a report within 2 pages only.
I tried to make two different reports and then tried to combine them as subreports, in the REPORT VIEW I can see all 4 pages, but in the PRINT PREVIEW I can't see all the pages, in fact, I can only see a blank page as there is no space to combine these 2 reports in the print preview.

Print Preview is simply that, Print Preview. You can either toggle through the pages, or you can select one of the Zoom options from the Ribbon, but if Report does not have enough data to preview, then you will not see them.

 

[Pat Hartman]

@spaLOGICng The problem is not viewing multiple records. The problem is that the schema is improper and each record has TOO MANY fields to fit on a report given the space limits of a PAGE which is 22" wide and possibly the same length. That is the equivalent of 4 normal report pages which is still not big enough for all the data.

Think of it this way. You have a questionnaire. You can add 255 columns to a table and that represents 255 questions. That's it. You are locked in. Not only do you have to print all 255 questions on the same page but you can't add any more questions because 255 is the limit of columns for a table or query.

The solution to the questionnaire problem is to go vertical. That gives you ONE question per row and an infinite number of questions. If you group the questions as they are in this example, you can use that grouping to either create subforms/subreports or to create page breaks in a report. The additional benefit of this schema is that you have a table that defines the questions and so includes the text of the question. That means that you don't have to physically create labels on forms and lay them out. You also would not have programming changes if you wanted to change the text of a question slightly. OR if you wanted to resequence the questions, you just change the sequence number - NO PROGRAMMING - required and the question shows up in a different place or even in a different group. It is really a no-brainer unless you've spend days/weeks making pretty reports and you are loath to abandon that effort.