Solved

MS Access 2013: Control Button to Display Subforms within Subforms

Posted on 2013-12-25
9
2,763 Views
Last Modified: 2013-12-27
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
0
Comment
Question by:mdstalla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 39739894
You are using one table. No need for multiple forms.
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.
0
 

Author Comment

by:mdstalla
ID: 39740207
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.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39740437
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mdstalla
ID: 39740844
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--Horizontal Tabs Two Levels).  All 28 of my Standard Forms are dumped onto 28 of the Tabs in this Navigation Forms.  

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--Horizontal Tabs) to house all 5 of the 2nd Level Navigation Forms.  

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.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39740910
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.
0
 

Author Comment

by:mdstalla
ID: 39740989
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.’
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 39741049
Sorry for that misunderstanding.
But one tends to say, to manage a bad design is a costly business.
I think you have to look seriously at improving your design.

To clear off your doubts:

"Your link took me to a menu."
            Yes it shows a menu, try to click a + sign in front of one menu item to expand it.
"can I dump 140 Sub Forms (Tabs) into a single Navigation Form?"
            Yes, if you are within 700 plus controls.

Another point worth mentioning, why on earth do you need 140 sub forms.

A survey is just one form with one table.
0
 

Author Comment

by:mdstalla
ID: 39741193
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.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39742112
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.
0

Featured Post

SuperAntiSpyware Licenses Discounted by 25% !

Exclusive offer to Experts Exchange Members!
Buy SuperAntiSpyware License(s) from us and save 25% on the regular purchase price.
- Includes Full SuperAntiSpyware Vendor Support Entitlements
- Your Subscription does not begin until you activate your license
- Buy for your friends

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question