locked
Best Way to Handle Subform With Large Number of Fields RRS feed

  • Question

  • Hello!

    One of the objects in my latest project is a form/subform.  The form contains relatively few objects, but the subform has so many fields that need to be completed it won't fit horizontally.  The user would have to scroll to the right to complete the data in the subform.  Is there a better way?  Perhaps launch a regular pop-up form from the subform that the user would complete, and after completion, the data from the pop-up would populate the subform.  Is that possible or recommended?

    Thanks,



    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Thursday, September 10, 2020 5:00 PM

Answers

  • Hi Rich

    There are many ways to handle this issue in Access.

    1. Your idea of a popup that offers the entire set of fields could work, but assuming you want to also display all of the data post-commit, you still have some of the original dilemma.
    2. Add a tab control to the form and divvy up the columns between subforms on the tabs according to some grouping of the data that makes sense. This is probably the most common manner of addressing the issue at hand.
    3. Add a subform control to the main page and load forms into it via code according to the slice of data the user is entering or wants to view. This is similar to option #2 but doesn't use the tab control. This solution would involve a bit more code.
    4. Access has had a Page Break control since day one; it allows one to split up a long form into sections that can be brought into view as needed. I stopped using this control decades ago but that's just me, you might find it suits your purpose/style.

    Something to bear in mind is that when a table starts getting really wide (lots of columns) it can sometimes indicate that the data might want to be broken out into separate tables. Knowing nothing about your data, can't say, but worth mentioning.

    • Marked as answer by RichLocus Friday, September 11, 2020 4:24 AM
    Thursday, September 10, 2020 5:57 PM
  • I was going to suggest some of the solutions above but rusticloud beat me to it.

    Personally I would go for option #2.

    However whenever a form/subform has so many fields that they don't fit horizontally, I would also question whether all of those should be in the same table


    • Edited by isladogs52 Thursday, September 10, 2020 7:47 PM
    • Marked as answer by RichLocus Friday, September 11, 2020 4:24 AM
    Thursday, September 10, 2020 7:47 PM
  • Perhaps launch a regular pop-up form from the subform that the user would complete, and after completion, the data from the pop-up would populate the subform.  Is that possible or recommended?

    Hi Rich,

    The best way is the way that suits you the best. I only know my best way. 

    I have one continuous form to display any recordset, tuned by a meta-datatable. In principle the columns are not editable, but any combination of columns can be made editable dynamically, depending on the circumstances. In the example the column "Volgnr" is made editable.

    Each record in this form has a "yellow" field, that - when pressed - opens a single-record form with all the fields vertical. This can evenly be divided in multi-columns. In general, all fields are editable, though under circumstances, can be made not-editable. Also, this single-record form can handle any record, tuned by the same meta-datatable.

    This single-record form has two buttons "Volgend" en "Vorig" to step through the underlying recordset.

    Perhaps this inspires you in the struggle for your best choice.

    Imb.


    • Marked as answer by RichLocus Friday, September 11, 2020 4:24 AM
    Thursday, September 10, 2020 9:10 PM
  • Is there a need for the fields themselves to be presented horizontally, i.e. as in a datasheet? If not, make sure your subform's default view property is single form or continuous form and arrange the fields however you'd like, e.g. multiple rows of fields in a single form.  Other possibly obvious suggestions are to use smaller fonts, use smaller controls (e.g comboboxes instead of listboxes or radio buttons), move labels from the left of controls to the top of controls (or to form headers), make sure your controls are no larger than they need to be to display relevant information, and make use of the control alignment tools (size/space and align on the "arrange" ribbon) to get things just right . If you then find that you still can't make adequate use of the real estate on the form, resort to tabs as others have stated.

    -Bruce

    • Marked as answer by RichLocus Friday, September 11, 2020 4:24 AM
    Thursday, September 10, 2020 10:55 PM

All replies

  • Hi Rich

    There are many ways to handle this issue in Access.

    1. Your idea of a popup that offers the entire set of fields could work, but assuming you want to also display all of the data post-commit, you still have some of the original dilemma.
    2. Add a tab control to the form and divvy up the columns between subforms on the tabs according to some grouping of the data that makes sense. This is probably the most common manner of addressing the issue at hand.
    3. Add a subform control to the main page and load forms into it via code according to the slice of data the user is entering or wants to view. This is similar to option #2 but doesn't use the tab control. This solution would involve a bit more code.
    4. Access has had a Page Break control since day one; it allows one to split up a long form into sections that can be brought into view as needed. I stopped using this control decades ago but that's just me, you might find it suits your purpose/style.

    Something to bear in mind is that when a table starts getting really wide (lots of columns) it can sometimes indicate that the data might want to be broken out into separate tables. Knowing nothing about your data, can't say, but worth mentioning.

    • Marked as answer by RichLocus Friday, September 11, 2020 4:24 AM
    Thursday, September 10, 2020 5:57 PM
  • I was going to suggest some of the solutions above but rusticloud beat me to it.

    Personally I would go for option #2.

    However whenever a form/subform has so many fields that they don't fit horizontally, I would also question whether all of those should be in the same table


    • Edited by isladogs52 Thursday, September 10, 2020 7:47 PM
    • Marked as answer by RichLocus Friday, September 11, 2020 4:24 AM
    Thursday, September 10, 2020 7:47 PM
  • Perhaps launch a regular pop-up form from the subform that the user would complete, and after completion, the data from the pop-up would populate the subform.  Is that possible or recommended?

    Hi Rich,

    The best way is the way that suits you the best. I only know my best way. 

    I have one continuous form to display any recordset, tuned by a meta-datatable. In principle the columns are not editable, but any combination of columns can be made editable dynamically, depending on the circumstances. In the example the column "Volgnr" is made editable.

    Each record in this form has a "yellow" field, that - when pressed - opens a single-record form with all the fields vertical. This can evenly be divided in multi-columns. In general, all fields are editable, though under circumstances, can be made not-editable. Also, this single-record form can handle any record, tuned by the same meta-datatable.

    This single-record form has two buttons "Volgend" en "Vorig" to step through the underlying recordset.

    Perhaps this inspires you in the struggle for your best choice.

    Imb.


    • Marked as answer by RichLocus Friday, September 11, 2020 4:24 AM
    Thursday, September 10, 2020 9:10 PM
  • Is there a need for the fields themselves to be presented horizontally, i.e. as in a datasheet? If not, make sure your subform's default view property is single form or continuous form and arrange the fields however you'd like, e.g. multiple rows of fields in a single form.  Other possibly obvious suggestions are to use smaller fonts, use smaller controls (e.g comboboxes instead of listboxes or radio buttons), move labels from the left of controls to the top of controls (or to form headers), make sure your controls are no larger than they need to be to display relevant information, and make use of the control alignment tools (size/space and align on the "arrange" ribbon) to get things just right . If you then find that you still can't make adequate use of the real estate on the form, resort to tabs as others have stated.

    -Bruce

    • Marked as answer by RichLocus Friday, September 11, 2020 4:24 AM
    Thursday, September 10, 2020 10:55 PM
  • Many good answers.  I will experiment and see which one fits best for the application.

    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Friday, September 11, 2020 4:25 AM
  • Many good answers.  I will experiment and see which one fits best for the application.

    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Friday, September 11, 2020 4:25 AM
  • Many good answers.  I will experiment and see which one fits best for the application.  Very nice answer!!

    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Friday, September 11, 2020 4:26 AM
  • Thanks.. I will check out the suggestions.

    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Friday, September 11, 2020 4:26 AM