mdstalla
asked on
MS Access 2013: Control Button to Display Subforms within Subforms
I have a Main Form (‘Master Navigation Form’) that has a Sub Form (‘Navigation Form1’) that also has Sub Forms (Form1 to Form 28).
I have Control Buttons on each of the lowest Sub Forms (a ‘Back Button’ and ‘Next Button’) that, if clicked, should determine which of the lowest Sub Forms to display next.
‘Back Buttons’ always prompt Access go to a pre-selected Form, however, the Form to be displayed when a ‘Next Buttons’ is pressed depends on the selection made on an Option Group displayed on that same form (‘Yes’ or ‘No.’)
For example: If Master ‘Navigation Form—Navigation Form1—Form3’ is currently displayed and the ‘Back Button’ is clicked, I would like Access to display ‘Master Navigation Form—Navigation Form1—Form1.’
Alternatively, if ‘Control Option—Yes’ is selected, and the ‘Next Button’ is clicked, I would like Access to display ‘Master Navigation Form—Navigation Form1—Form4.’
And alternatively, if ‘Control Option—No’ is selected, and the ‘Next Button’ is clicked, I would like Access to display ‘Master Navigation Form—Navigation Form1—Form6.’
Attached is my Database.
Can anyone give me the codes to make this happen? Thank you SO MUCH!
WUCAINSMaster.accdb
I have Control Buttons on each of the lowest Sub Forms (a ‘Back Button’ and ‘Next Button’) that, if clicked, should determine which of the lowest Sub Forms to display next.
‘Back Buttons’ always prompt Access go to a pre-selected Form, however, the Form to be displayed when a ‘Next Buttons’ is pressed depends on the selection made on an Option Group displayed on that same form (‘Yes’ or ‘No.’)
For example: If Master ‘Navigation Form—Navigation Form1—Form3’ is currently displayed and the ‘Back Button’ is clicked, I would like Access to display ‘Master Navigation Form—Navigation Form1—Form1.’
Alternatively, if ‘Control Option—Yes’ is selected, and the ‘Next Button’ is clicked, I would like Access to display ‘Master Navigation Form—Navigation Form1—Form4.’
And alternatively, if ‘Control Option—No’ is selected, and the ‘Next Button’ is clicked, I would like Access to display ‘Master Navigation Form—Navigation Form1—Form6.’
Attached is my Database.
Can anyone give me the codes to make this happen? Thank you SO MUCH!
WUCAINSMaster.accdb
ASKER
While the example I gave you has 1 table, my end project will have a minimum of 3 table (all completely full with all 255 rows filled out).
In the end, I expect to have about 100 to 150 forms. The reason I have a Master Form containing Sub Navigation Forms Containing Sub forms is because I fear that Access will limit the number of Sub forms I can shove into a single parent form? So you do bring up 2 critical questions for me:
1. Is there a limit on how many Forms I can create within a single Access Database (I've already build around 55)?
2. Can I shove as many single-level Sub Forms (Tabs) into a single Parent form as I want?
I appreciate your guidance. Whenever you get a chance, please let me know. Thanks.
In the end, I expect to have about 100 to 150 forms. The reason I have a Master Form containing Sub Navigation Forms Containing Sub forms is because I fear that Access will limit the number of Sub forms I can shove into a single parent form? So you do bring up 2 critical questions for me:
1. Is there a limit on how many Forms I can create within a single Access Database (I've already build around 55)?
2. Can I shove as many single-level Sub Forms (Tabs) into a single Parent form as I want?
I appreciate your guidance. Whenever you get a chance, please let me know. Thanks.
Are these actual Navigation forms (using the Access 2010 or 2013 Navigation control), or regular Access forms with subforms? Unless you are creating a Web app, generally a form with subforms, possibly placed on a Tab control, offers a lot more functionality. With subforms placed on pages of a Tab control, you can easily control which subform is visible from the Tab control's Click event or a command button in the main form header or footer.
ASKER
Helen: I'll try to answer your question the best way I can.
My project is very 'Form Intensive.' When I am done, I expect to have 140 forms bounded to 3-4 tables (I have multiple tables, simply because I can only create 255 fields in a single table-- I need around 850).
The lowest level of Forms I have are standard forms (Create--Blank Form). Once I've created 28 of these Standard Forms, I create a Navigation Form (Create--Navigation--Horiz
Doing the math... you can see that I will need to create 5 of these 2nd Level Navigation Forms. So I do, and I also create a 3rd Level Navigation Form (Create--Navigation--Horiz
The reason I am organizing my Forms this way is because I have no idea how many Standard Forms can be dumped into a single Navigation Form. If-- I can dump all 140 Standard Forms into a single Navigation Form, then great! You are right, I can easily use Tab Control's Click Event sequence to determine which of my 140 tabs to display next.
I'm just afraid Access might crash if too many forms are dumped into a single Navigation Form (I don't know if it will or will not). So let me ask you:
1. Is there a limit on how many Forms I can create within a single Access Database (I've already build around 55)?
2. Can I dump as many single-level Sub Forms (Tabs) into a single Parent form as I want?
I appreciate your guidance. Whenever you get a chance, please let me know. Thanks.
My project is very 'Form Intensive.' When I am done, I expect to have 140 forms bounded to 3-4 tables (I have multiple tables, simply because I can only create 255 fields in a single table-- I need around 850).
The lowest level of Forms I have are standard forms (Create--Blank Form). Once I've created 28 of these Standard Forms, I create a Navigation Form (Create--Navigation--Horiz
Doing the math... you can see that I will need to create 5 of these 2nd Level Navigation Forms. So I do, and I also create a 3rd Level Navigation Form (Create--Navigation--Horiz
The reason I am organizing my Forms this way is because I have no idea how many Standard Forms can be dumped into a single Navigation Form. If-- I can dump all 140 Standard Forms into a single Navigation Form, then great! You are right, I can easily use Tab Control's Click Event sequence to determine which of my 140 tabs to display next.
I'm just afraid Access might crash if too many forms are dumped into a single Navigation Form (I don't know if it will or will not). So let me ask you:
1. Is there a limit on how many Forms I can create within a single Access Database (I've already build around 55)?
2. Can I dump as many single-level Sub Forms (Tabs) into a single Parent form as I want?
I appreciate your guidance. Whenever you get a chance, please let me know. Thanks.
See this office info about Access Specifications.
http://office.microsoft.com/en-us/access-help/access-specifications-HP005186808.aspx
You may have much more controls per form than what you have mentioned.
http://office.microsoft.com/en-us/access-help/access-specifications-HP005186808.aspx
You may have much more controls per form than what you have mentioned.
ASKER
I'm not following you. Your link took me to a menu. What do you mean by "You may have much more controls per form than what you have mentioned?"
Just to stay on topic... in your estimation, can I dump 140 Sub Forms (Tabs) into a single Navigation Form?
And if not... Would you happen to have a code to perform my original request:
If Master ‘Navigation Form—Navigation Form1—Form3’ is currently displayed and the ‘Back Button’ is clicked, I would like Access to display ‘Master Navigation Form—Navigation Form1—Form1.’
Alternatively, if ‘Control Option—Yes’ is selected, and the ‘Next Button’ is clicked, I would like Access to display ‘Master Navigation Form—Navigation Form1—Form4.’
And alternatively, if ‘Control Option—No’ is selected, and the ‘Next Button’ is clicked, I would like Access to display ‘Master Navigation Form—Navigation Form1—Form6.’
Just to stay on topic... in your estimation, can I dump 140 Sub Forms (Tabs) into a single Navigation Form?
And if not... Would you happen to have a code to perform my original request:
If Master ‘Navigation Form—Navigation Form1—Form3’ is currently displayed and the ‘Back Button’ is clicked, I would like Access to display ‘Master Navigation Form—Navigation Form1—Form1.’
Alternatively, if ‘Control Option—Yes’ is selected, and the ‘Next Button’ is clicked, I would like Access to display ‘Master Navigation Form—Navigation Form1—Form4.’
And alternatively, if ‘Control Option—No’ is selected, and the ‘Next Button’ is clicked, I would like Access to display ‘Master Navigation Form—Navigation Form1—Form6.’
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ha! Thank you for the information.
Controls? Does that mean, for example, 'Control Buttons?' I have 2 'Control Buttons' per Sub Form, so, if I understand you correctly, I'll only have 280 Controls (well within 700). That's good news. If I have your definition of 'Controls' wrong, please let me know.
What I am doing is creating an Access, Form-based--- interactive, interview questionnaire (that's a mouthful). I'm using Access because I like the Form concept were I can design how my questions are displayed (aesthetically) and I can control which questions are to be asked and when (often, the 'next question to be asked' depends on the last answer provided).
I've now finished Form95 if 140. Each of these forms are bound to a consecutive series of Tables. The aim, ultimately, is to then export each of these Tables to an Excel Spreadsheet, from which the data is slightly manipulated through formulas, and then used as Insert Mail Merge Fields into a pre-drafted MS Word Document.
In other words... my Interview Questions (Access Forms) will be plugged directly into my Interview Report (Word Document). The 'Dream?' Once my staff Interviews a Customer and enters their answers into my Forms... they will no longer have to sit down and write a 20 page report on the results. My program writes the report for them!
The Excel and Word portion of this project is jaw-droopingly amazing. I just need to survive the Access section.
Controls? Does that mean, for example, 'Control Buttons?' I have 2 'Control Buttons' per Sub Form, so, if I understand you correctly, I'll only have 280 Controls (well within 700). That's good news. If I have your definition of 'Controls' wrong, please let me know.
What I am doing is creating an Access, Form-based--- interactive, interview questionnaire (that's a mouthful). I'm using Access because I like the Form concept were I can design how my questions are displayed (aesthetically) and I can control which questions are to be asked and when (often, the 'next question to be asked' depends on the last answer provided).
I've now finished Form95 if 140. Each of these forms are bound to a consecutive series of Tables. The aim, ultimately, is to then export each of these Tables to an Excel Spreadsheet, from which the data is slightly manipulated through formulas, and then used as Insert Mail Merge Fields into a pre-drafted MS Word Document.
In other words... my Interview Questions (Access Forms) will be plugged directly into my Interview Report (Word Document). The 'Dream?' Once my staff Interviews a Customer and enters their answers into my Forms... they will no longer have to sit down and write a 20 page report on the results. My program writes the report for them!
The Excel and Word portion of this project is jaw-droopingly amazing. I just need to survive the Access section.
I'm sure you don't want to hear this at this point but your approach is significantly more labor-intensive than it has to be because your schema is not normalized.
There are much easier ways to define questionnaires and if you go with one of them, you can use it over and over again and modify it without rewriting everything.
The normalized methods you will find (if you search for questionnaire or survey) don't necessarily group questions but they could and they also don't offer branching logic but they could. Your requests are not trivial but the most difficult part would be setting up the flow since the samples would not include anything like the "flow" you want. You would end up with about a half dozen tables that could support an infinite number of questions and about the same number of forms.
You could do a simplistic version of flow by adding a grouping field to each question that indicated which "form" it would appear on. By using a form/subform, the logic behind the form reads each "group" (mainform) at a time and displays only the questions (subform) from a particular group. So, although your form size would dictate the maximum number of questions, the minimum would be 1.
There are much easier ways to define questionnaires and if you go with one of them, you can use it over and over again and modify it without rewriting everything.
The normalized methods you will find (if you search for questionnaire or survey) don't necessarily group questions but they could and they also don't offer branching logic but they could. Your requests are not trivial but the most difficult part would be setting up the flow since the samples would not include anything like the "flow" you want. You would end up with about a half dozen tables that could support an infinite number of questions and about the same number of forms.
You could do a simplistic version of flow by adding a grouping field to each question that indicated which "form" it would appear on. By using a form/subform, the logic behind the form reads each "group" (mainform) at a time and displays only the questions (subform) from a particular group. So, although your form size would dictate the maximum number of questions, the minimum would be 1.
I suggest you redesign the project to have one form with a Tab control. Each page shows part of the info. Navigation will be simple from one tab to another.