Solved

MS Access 2013: Control Button to Display Subforms within Subforms

Posted on 2013-12-25
9
2,700 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
9 Comments
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:mdstalla
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for 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 start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now