none
Continuous Subform-Grow & Shrink (if no data) RRS feed

  • Question

  • I have multiple continuous forms placed onto a Main Form (Single Form).

    1. I'm needing to know how to grow/shrink the continuous subforms based on number of records.

    2. Is there a way to remove the subform if no data?

    Friday, July 21, 2017 9:08 PM

All replies

  • This should be possible but may need a lot of code. For example, if you have two subforms on top of each other and you want to reclaim the space for the top subform, would you like to move the bottom subform up? If so, then it's going to be a lot of work. Otherwise, if you don't mind showing an empty space, then it should be simpler.
    Friday, July 21, 2017 9:34 PM
  • This should be possible but may need a lot of code. For example, if you have two subforms on top of each other and you want to reclaim the space for the top subform, would you like to move the bottom subform up? If so, then it's going to be a lot of work. Otherwise, if you don't mind showing an empty space, then it should be simpler.

    You are spot on. I would like to have the bottom subform move up if there is no data in the top subform.
    Friday, July 21, 2017 9:39 PM
  • Okay, I don't have a sample file to show you but here's the basics.

    1. You can use the RecordCount property of the subform control or use DCount() to find out if there's any records in the subform

    2. Modify the subform control's Height property to accommodate the number of records in the subform

    3. Use the Visible property to hide or show the subform

    4. Adjust the Top and Left properties of the subform control to move the subform around the form (up, down, left, or right)

    Hope it helps...


    Friday, July 21, 2017 9:47 PM
  • 1. I'm needing to know how to grow/shrink the continuous subforms based on number of records.

    2. Is there a way to remove the subform if no data?

    Hi FK_1234,

    .theDBguy gave you already all the ingredients to realize your goal.

    I use this kind of techniques quite intensive to make dynamical forms for all kind of purposes. This has gone so far that I only use one form as "holder", that can be filled with with one subform as many times as is practical.

    In your case, all the controls for the subforms would be invisible in the beginning. In the Open (or Load) event of the mainform all the subform can be dimensioned (height and width) according to what will be in the subform, and positioned (top and left) to arrange them with respect to each other, and made visible.

    For me it is big fun to work in this way, but it needs (a little bit to quite a lot) code.

    Imb.


    • Edited by Imb-hb Saturday, July 22, 2017 7:47 AM edit
    Saturday, July 22, 2017 7:46 AM
  • Okay, I don't have a sample file to show you but here's the basics.

    1. You can use the RecordCount property of the subform control or use DCount() to find out if there's any records in the subform

    2. Modify the subform control's Height property to accommodate the number of records in the subform

    3. Use the Visible property to hide or show the subform

    4. Adjust the Top and Left properties of the subform control to move the subform around the form (up, down, left, or right)

    Hope it helps...


    This is exactly what I'm needing, but I'm needing some help with the code.  I've tried a couple of things I have found by googling, but it's not working properly.  Any samples would be much appreciated.
    Monday, July 24, 2017 4:32 PM
  • Hi,

    See if this previous thread can help you get started.

    Hope it helps...

    Monday, July 24, 2017 4:41 PM
  • 1. I'm needing to know how to grow/shrink the continuous subforms based on number of records.

    2. Is there a way to remove the subform if no data?

    Hi FK_1234,

    .theDBguy gave you already all the ingredients to realize your goal.

    I use this kind of techniques quite intensive to make dynamical forms for all kind of purposes. This has gone so far that I only use one form as "holder", that can be filled with with one subform as many times as is practical.

    In your case, all the controls for the subforms would be invisible in the beginning. In the Open (or Load) event of the mainform all the subform can be dimensioned (height and width) according to what will be in the subform, and positioned (top and left) to arrange them with respect to each other, and made visible.

    For me it is big fun to work in this way, but it needs (a little bit to quite a lot) code.

    Imb.


    Do you have a sample code that shows:

    • Continuous subform resize to fit number of records.
    • Subform invisible if no records.
    • Other subforms top property based on visibility of other subforms.

    Any sample you have is appreciated.

    Monday, July 24, 2017 6:23 PM
  • Do you have a sample code that shows:

    • Continuous subform resize to fit number of records.
    • Subform invisible if no records.
    • Other subforms top property based on visibility of other subforms.

    Any sample you have is appreciated.

    Hi FK_1234,Unfortunately I have no ready-to-eat code for you. The code that I use is quite abstract, because I can use the same code for almost any situation  where subforms are used, in any application that I build. Moreover I think it is better to understand what can happen.

    First, Access works with Twips (1440 per inch or 543 per cm). In design I only work with cm, and use a function toconvert it to twips.

    On your main form you have already a couple of invisible subforms. The important properties of the subform-controls are Height and Width, Top and Left. You can set these values in design mode of the main form, but you can also manipulate them during the Open (or Load) event of the form. This is done with VBA.

    Let us do a simple exercise. You have 4 subform controls side by side, with 4, 0, 10, 5 records. Each record consumes 0.6 cm height. You can tune the different Heights by:
        control1.Height = In_twips (4 * 0.6): control1.Visible = True
        control2.Height = In_twips (0 * 0.6): control2.Visible = True
        control3.Height = In_twips (10 * 0.6): control3.Visible = True
        control4.Height = In_twips (5 * 0.6): control4.Visible = True

    As control2 has no records, and you want to skip that, and shift the other controls to the left.
    The number of twips to shift is: diff = control3.Left - control2.Left. All controls right of control then can be shifted to the left.
        control2.Visible = False
        control3.Left = control3.Left - diff
        control4.Left = control4.Left - diff

    The same principles work if the controls are stacked (Top and Height), or side by side (Left and Width)

    The programming for the above example is probably not the problem. More complicated is to get a good algorithm that works In many situations.

    Imb.

    Monday, July 24, 2017 9:40 PM
  • Do you have a sample code that shows:

    • Continuous subform resize to fit number of records.
    • Subform invisible if no records.
    • Other subforms top property based on visibility of other subforms.

    Any sample you have is appreciated.

    Hi FK_1234,Unfortunately I have no ready-to-eat code for you. The code that I use is quite abstract, because I can use the same code for almost any situation  where subforms are used, in any application that I build. Moreover I think it is better to understand what can happen.

    First, Access works with Twips (1440 per inch or 543 per cm). In design I only work with cm, and use a function toconvert it to twips.

    On your main form you have already a couple of invisible subforms. The important properties of the subform-controls are Height and Width, Top and Left. You can set these values in design mode of the main form, but you can also manipulate them during the Open (or Load) event of the form. This is done with VBA.

    Let us do a simple exercise. You have 4 subform controls side by side, with 4, 0, 10, 5 records. Each record consumes 0.6 cm height. You can tune the different Heights by:
        control1.Height = In_twips (4 * 0.6): control1.Visible = True
        control2.Height = In_twips (0 * 0.6): control2.Visible = True
        control3.Height = In_twips (10 * 0.6): control3.Visible = True
        control4.Height = In_twips (5 * 0.6): control4.Visible = True

    As control2 has no records, and you want to skip that, and shift the other controls to the left.
    The number of twips to shift is: diff = control3.Left - control2.Left. All controls right of control then can be shifted to the left.
        control2.Visible = False
        control3.Left = control3.Left - diff
        control4.Left = control4.Left - diff

    The same principles work if the controls are stacked (Top and Height), or side by side (Left and Width)

    The programming for the above example is probably not the problem. More complicated is to get a good algorithm that works In many situations.

    Imb.

    Maybe you can help me with some of the below code:

    If subform recordcount = 0 then subform height =0

    If subform recordcount >0 then subform height = recordcount * 200

    Tuesday, July 25, 2017 6:21 PM
  • Hi,

    You could try:

    Me.SubformName.Height = Me.SubformName.Form.Recordset.RecordCount * 200

    Hope it helps...



    • Edited by .theDBguy Tuesday, July 25, 2017 6:31 PM
    Tuesday, July 25, 2017 6:27 PM
  • Hi,

    You could try:

    Me.SubformName.Height = Me.SubformName.Form.Recordset.RecordCount * 200

    Hope it helps...



    That did not work.  It would be great if it did.  I've tried variations of this, and cannot get anything to work.  I think my problem is the RecordCount is not pulling correctly.  My subform and subform control are named differently.  I've tried variations of both.  Any suggestions on the RecordCount?  I'm using Access 2013 if that makes a difference.
    Tuesday, July 25, 2017 6:54 PM
  • Hi,

    If your subform and subform control are named differently, make sure you use the name of the control and not the form.

    Hope it helps..

    Tuesday, July 25, 2017 7:18 PM
  • Also remember that ACCESS uses Twisps for measurement. There are 1,440 Twisps for each 1", so if you want to move an object UP 1", then you would set the Me.controlname.Top property to:

    Me.controlname.Top = Me.controlname.Top + 1,400.

    This also applies to height and width of an object. If an object is 3" high, that is 3 x 1,440 or 4,320 Twisps. If you want to reduce the height from 3" to 2", then:

    Me.controlname.Height = Me.controlname.Height - 1,440 (Height reduced 1")

    Tuesday, July 25, 2017 7:22 PM
  • Hi,

    If your subform and subform control are named differently, make sure you use the name of the control and not the form.

    Hope it helps..

    I was able to get the first part of what I need with the following code:

    Me.Subformcontrol.Top = 288

    If Me![Subformcontrol].Form.RecordsetClone.RecordCount > 0 Then
    Me.Subformcontrol.Height = (Me![Subformcontrol].Form.RecordsetClone.RecordCount * 421) + 864
    Else
    Me.Subformcontrol.Height = 0
    End If

    Now, I'm needing to duplicate the code for a different subform on the same "On Current" event.  Is this possible?  I've tested it and it only shows 1 record for each subform...all of the records do not show.  Any thoughts?

    Tuesday, July 25, 2017 9:08 PM
  • Maybe you can help me with some of the below code:

    If subform recordcount = 0 then subform height =0

    If subform recordcount >0 then subform height = recordcount * 200

    Hi FK_1234,

    When there are no records to display, I would make the control invisible instead of set the height = 0. In that case the dimensions are not so interesting as long as it fits in its section.

    200 twips is around 0.4 cm. For me it is easier to "think" in cm's then in twips, so my preference would be:  some_height = In_twips(recordcount * 0.4), or something like that. For easy typing I will now use your formula:  recordcount * 200.

    The RecordCount of a Recordset is only reliable the whole Recordset is loaded.

    The height of the control on the main form can be set on recordcount * 200. The main form must be high enough to hold that. In a single-record form that is mostly not a problem, in a continuous form the footer (or header) must be of sufficient height.

    In that case it would be something like that:

        Me!Subform_ctl.Height = 200 * Me!Subform_ctl.Form.Recordset.RecordCount

    the same as what .theDBguy suggested.

    Is there enough height on the main form? Is the Recordset already loaded when you set the height of Subform_ctl?

    You can step with the debugger through your program to find where it goes wrong.

    Imb.

    Tuesday, July 25, 2017 9:27 PM
  • Maybe you can help me with some of the below code:

    If subform recordcount = 0 then subform height =0

    If subform recordcount >0 then subform height = recordcount * 200

    Hi FK_1234,

    When there are no records to display, I would make the control invisible instead of set the height = 0. In that case the dimensions are not so interesting as long as it fits in its section.

    200 twips is around 0.4 cm. For me it is easier to "think" in cm's then in twips, so my preference would be:  some_height = In_twips(recordcount * 0.4), or something like that. For easy typing I will now use your formula:  recordcount * 200.

    The RecordCount of a Recordset is only reliable the whole Recordset is loaded.

    The height of the control on the main form can be set on recordcount * 200. The main form must be high enough to hold that. In a single-record form that is mostly not a problem, in a continuous form the footer (or header) must be of sufficient height.

    In that case it would be something like that:

        Me!Subform_ctl.Height = 200 * Me!Subform_ctl.Form.Recordset.RecordCount

    the same as what .theDBguy suggested.

    Is there enough height on the main form? Is the Recordset already loaded when you set the height of Subform_ctl?

    You can step with the debugger through your program to find where it goes wrong.

    Imb.

    I was able to get the first part of what I need with the following code:

    Me.Subformcontrol.Top = 288

    If Me![Subformcontrol].Form.RecordsetClone.RecordCount > 0 Then
    Me.Subformcontrol.Height = (Me![Subformcontrol].Form.RecordsetClone.RecordCount * 421) + 864
    Else
    Me.Subformcontrol.Height = 0
    End If

    New Problem:  Now, I'm needing to duplicate the code above for a different subform on the same "On Current" event.  Is this possible?  I've tested it and it only shows 1 record for each subform...all of the records do not show.  Any thoughts?  I have multiple "Master Subforms" and multiple "Detail Subforms" (that rely on the master).  Again, the code above works perfectly for 1 Master Subform, but when I try to duplicate it and add to Current event, it isn't pulling records properly.  Any thoughts?


    Wednesday, July 26, 2017 2:29 PM
  • Yes, you should be able to affect multiple subform controls within the Current event but just make sure you refer to their individual names at the right places.

    Hope it helps...

    Wednesday, July 26, 2017 2:58 PM
  • Okay, I don't have a sample file to show you but here's the basics.

    1. You can use the RecordCount property of the subform control or use DCount() to find out if there's any records in the subform

    2. Modify the subform control's Height property to accommodate the number of records in the subform

    3. Use the Visible property to hide or show the subform

    4. Adjust the Top and Left properties of the subform control to move the subform around the form (up, down, left, or right)

    Hope it helps...


    This is exactly what I'm needing, but I'm needing some help with the code.  I've tried a couple of things I have found by googling, but it's not working properly.  Any samples would be much appreciated.

    Hello,

    I suggest you mark helpful post as answer to close this thread. If you have any new issues when coding, please post new threads. Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 31, 2017 7:25 AM
    Moderator