locked
Best Way To Create A Unique Subform RRS feed

  • Question

  • Hello:

    I posted recently a question about the options available for a subform.  There were great answers for a "Regular" subform.  However, my client wants a really "fancy" subform with navigation buttons and text boxes instead of the default subform that is built in to Access. The "built in" version takes care of the logic of linking, updating and adding subform rows to the primary form.<o:p></o:p>

    To illustrate my point, I have attached two examples.  Example 1 is a standard subform that I already have developed and is in production for my client.<o:p></o:p>

    The second example is a "subform" (if you can call it that) that the client wants.  What is unique about it is that is actually a separate regular form just beneath the top form that would have to be manually linked with code.  The "subform" has its own navigation buttons and uses textboxes instead of the usual subform fields. A record in the top portion of the example could have many "rows" in the bottom portion.  But they are not "rows".  The user would navigate to the next linked "subform" record using buttons that I create. However, as I mentioned, the "subform" is actually a form with its own navigation buttons and text boxes.<o:p></o:p>

    Is this a pipe dream of my client, or is it possible to program it using advanced VBA techniques? I'm an experienced VBA programmer so you don't have to go into excruciating detail.<o:p></o:p>

    Any suggestions or observations would be appreciated.<o:p></o:p>


    Thanks for any ideas.

    Regards,

    Rich Locus

    <o:p></o:p>


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Friday, September 11, 2020 6:42 PM

Answers

  • Yes, that is entirely possible. Set the Default View property of the subform to Single Form.

    You can then display text boxes, command buttons etc. on the subform in any configuration you like.

    If you specify the Link Child Fields and Link Master Fields properties of the subform control on the main form, the subform will automatically be linked to the current record on the main form.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by RichLocus Saturday, September 12, 2020 1:24 AM
    Friday, September 11, 2020 7:00 PM
  • Hi Rich, 

    A subform doesn't have to be a datasheet view like your "typical subform".  How the subform appears is wholly dependent on the Default View property of the subform control's source form.  I don't see any reason why you can't create a form like you show in your second example (i.e. a form with its Default View property set to Single Form) and simply add it as a subform to the first form, without any fancy coding.  You will have to code your navigation and record operation buttons but each of those should be basically one line of code each.

    -Bruce


    • Edited by Bruce Hulsey Friday, September 11, 2020 7:05 PM
    • Marked as answer by RichLocus Saturday, September 12, 2020 1:26 AM
    Friday, September 11, 2020 7:03 PM
  • As already stated, it can certainly be done and without advanced VBA techniques being required.

    This is an example of something far more complex than that. A form with 11 dynamic subforms of different types whose appearance depends on the role of the user

    Compared to that, your request is simple


    • Edited by isladogs52 Friday, September 11, 2020 7:51 PM
    • Marked as answer by RichLocus Saturday, September 12, 2020 1:25 AM
    Friday, September 11, 2020 7:50 PM
  • Hi Rich

    There is a main form container and 8 subforms together with 3 sub-subforms. It takes less than 2 seconds to load. Here is the design view

    There are 3 versions of this form aimed at different target groups

    The original version combined all of those into one form and 22 subforms/sub-subforms. The idea was that only those relevant to each user would be displayed. It worked but was too ambitious taking about 7 seconds to load. That was too long a loading time to be acceptable

    • Marked as answer by RichLocus Sunday, September 13, 2020 4:01 AM
    Saturday, September 12, 2020 10:29 PM

All replies

  • Yes, that is entirely possible. Set the Default View property of the subform to Single Form.

    You can then display text boxes, command buttons etc. on the subform in any configuration you like.

    If you specify the Link Child Fields and Link Master Fields properties of the subform control on the main form, the subform will automatically be linked to the current record on the main form.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by RichLocus Saturday, September 12, 2020 1:24 AM
    Friday, September 11, 2020 7:00 PM
  • Hi Rich, 

    A subform doesn't have to be a datasheet view like your "typical subform".  How the subform appears is wholly dependent on the Default View property of the subform control's source form.  I don't see any reason why you can't create a form like you show in your second example (i.e. a form with its Default View property set to Single Form) and simply add it as a subform to the first form, without any fancy coding.  You will have to code your navigation and record operation buttons but each of those should be basically one line of code each.

    -Bruce


    • Edited by Bruce Hulsey Friday, September 11, 2020 7:05 PM
    • Marked as answer by RichLocus Saturday, September 12, 2020 1:26 AM
    Friday, September 11, 2020 7:03 PM
  • As already stated, it can certainly be done and without advanced VBA techniques being required.

    This is an example of something far more complex than that. A form with 11 dynamic subforms of different types whose appearance depends on the role of the user

    Compared to that, your request is simple


    • Edited by isladogs52 Friday, September 11, 2020 7:51 PM
    • Marked as answer by RichLocus Saturday, September 12, 2020 1:25 AM
    Friday, September 11, 2020 7:50 PM
  • Is this a pipe dream of my client, or is it possible to program it using advanced VBA techniques?

    Hi Rich,

    I would not speak of advanced VBA techniques, but consequent and structured VBA techniques. 

    With VBA code you can almost do more than your imagination on this moment says.

    The difference between normal forms and subforms is for me very marginal. With continuous forms I never use subforms. When I want to see details about a specific record, I open a subsequent "normal" forms with all possible details.
    But I use subforms. I have one "normal" form with a couple of (hidden) subform-controls, and one form that is used for the subforms. In this way I create forms with any functionality you want, totally dynamical. It sounds magical, and very complicated, but it is just a consequence of structured working, comparable with how evolution has gone its way.

    The big difference with standard Access is that I do not use the parent-child relation in the form/subform combination, but the parent_id is sent to the next form in a different way (parameter, global variable, ...)

    Back to your question, VBA does not limit your wishes. It is the concept that you use that gives the limitations.

    Imb.

    Friday, September 11, 2020 8:30 PM
  • isladogs52, this is very nice design.  Thanks for sharing that.

    -Bruce

    Friday, September 11, 2020 8:47 PM
  • Thanks Bruce

    The form and subforms also have to work with automatic form resizing as I use that in all my commercial apps.

    That was the biggest challenge in that particular example.

    Each part of the form can be manipulated for additional functionality


    • Edited by isladogs52 Friday, September 11, 2020 8:59 PM
    Friday, September 11, 2020 8:57 PM
  • Hans:

    Thanks so much!  That should simplify matters immensely!

    Rich


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Saturday, September 12, 2020 1:24 AM
  • Hello isladogs52:

    You mentioned that you have 11 subforms on the same Access tab.  Can I assume these are wrapped in a main form container?

    Thanks,

    Rich Locus


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Saturday, September 12, 2020 6:46 PM
  • Can I assume these are wrapped in a main form container?

    Hi Rich,

    You can change the SourceObject of the subform-control to a different SourceObject dynamically, to display a different subform. Perhaps you have to set the proper parent-child-relation. 

    Instead of displaying "as much as possible" subforms on a form, I normally use a series of command buttons to zoom in in the different "subforms"/"sub-functions" to display that functionality with no screen limitations.

    Imb.

    Saturday, September 12, 2020 7:50 PM
  • Hi Rich

    There is a main form container and 8 subforms together with 3 sub-subforms. It takes less than 2 seconds to load. Here is the design view

    There are 3 versions of this form aimed at different target groups

    The original version combined all of those into one form and 22 subforms/sub-subforms. The idea was that only those relevant to each user would be displayed. It worked but was too ambitious taking about 7 seconds to load. That was too long a loading time to be acceptable

    • Marked as answer by RichLocus Sunday, September 13, 2020 4:01 AM
    Saturday, September 12, 2020 10:29 PM
  • Isladogs52:

    Thanks for the additional information!

    Regards,

    Rich


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Sunday, September 13, 2020 4:02 AM
  • Thanks for the additional information!

    Hi Rich and isladogs52,

    Still intrigued by the subject ...

    @ Rich: what do you mean with a "unique subform", as opposed to a "standard" subform. Isn't your original design unique?

    @ isladogs52: in the "MyTimetable" part you have stacked controls. Are these for the subsequent weeks? I think it is simple to use only one layer, and dynamically fill the relevant data per chosen week. In design mode control(2,2) has already a coloured background. Is this "hardcoded", or the default?

    Imb.

    Monday, September 14, 2020 10:32 AM
  • Hi imb

    The application is used in different schools each with different numbers of lessons (periods) per day, some have a fortnightly timetable, others are a weekly cycle. The subform shown (and other student/staff timetable forms allow for this by having 100 timetable slots giving a maximum of 10 lessons per day over a 10 day cycle. The related code places the correct number of slots for each school according to the settings table, displays the relevant timetable info (class/teacher/subject/room) then checks for any lessons with class or student support then colours them appropriately (yellow/green/orange etc) according to the type of support. None of the slot colours are hard coded. On changing to design view, it retains the previous colours but only temporarily.

    The process takes a fraction of a second and is by far the most efficient method of handling a wide variety of possible scenarios. It also can handle cases where individual schools prefer a horizontal display with lessons for each day going across the subform.

    The form is used as a staff home page giving access to all the features most frequently accessed by each member of staff. Taking together all the different parts of the form/subforms, it provides access to around 50 different application features which will differ depending on job roles etc

    Monday, September 14, 2020 10:56 AM
  • The form is used as a staff home page giving access to all the features most frequently accessed by each member of staff. Taking together all the different parts of the form/subforms, it provides access to around 50 different application features which will differ depending on job roles etc

    Hi isladogs52,

    Thank you for the additional information. A beautiful result! And I suppose you have quite a lot of Form-definitions for all the different subforms.

    I took a little different direction. Each of your Subforms would be a "main form" for me, as a holder for a number of identical subforms. The different subforms make that a "main form" can contain a number of independant entities. 

    In design view the "main form" and subform look like:

    The subform is completely generalized. So instead of designing a subform, I build a "subform" using code blocks, that are available in any application through a linked library. This makes it an extremely flexible system, where all can be changed "in situ". 

    I use this systematics in 100+ applications. Maintenance is very easy because the kernel is in a linked library, and the applcation specific "recipes" are in modules, that can be analyzed over all applications.

    Imb.

    Tuesday, September 15, 2020 8:07 AM
  • I see the picture disappeared.

    New attempt:

    Imb.

    Tuesday, September 15, 2020 8:09 AM