- Local time
- Today, 11:50
- Joined
- Feb 19, 2002
- Messages
- 43,408
We all knew that.Actually, the 4 pages I've mentioned is for the SINGLE record.
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.