access how get subform
RH

Forum home » Delegate support and help forum » Microsoft Access Training and help » Access: How to get subform to expand

Access: How to get subform to expand

resolvedResolved · Low Priority · Version 2010

Joe has attended:
Access Intermediate course

Access: How to get subform to expand

Dear Best STL,

I’ve created a subform which will contain from one to four records. It is placed within a tab control in the parent form.
I would like to make the subform auto expand/shrink (downwards) depending whether there are one or four records required to display.
I’ve set ‘can grow’ & ‘can shrink’ in the subform properties but this has no effect: The number of records in the subform will display in the height available, but if there are more records, a scroll bar appears. I don’t want a scroll bar to appear but the subform to expand and display the extra records.
It is the only object in the tab control and there is lots of space for it to expand! I can’t think what I’m doing wrong!

Many thanks
Joe

RE: Access: How to get subform to expand

Hi Joe

The subform should automatically expand if there are more records. To avoid the scroll bars appearing go into design view on the main form and stretch the Subform to a larger height. Say 250cm.

It may look better if you set the subform Border style to Transparent.
Also in the design view of the Subform set Record Selector and Navigation Buttons to No.

I think the Can Grow property is to control what is printed rather than viewed in the form. Let me know if that stops the scroll bar appearing.

Doug
Best STL

RE: Access: How to get subform to expand

Dear Doug,

Thanks for your answer.

I actually have two subforms with the same problem; one displays in ‘Datasheet View’, the other displays in ‘Form View’.
I can’t increase the height to more than will fit in their tab control or a lot of dead space will be created for all the tabs and create scroll bars on the main form page too.
I already tried your other suggestions without luck.
The subform displaying in Form View is the worst example. If there are no records, an empty field displays (no scroll bars). As soon as there is one more record displaying, scroll bars are created even though there is a huge amount of space for this and probably another record. The ‘can shrink’ doesn’t work either.
Best regards.
Joe

RE: Access: How to get subform to expand

Hi Joe
Would you be able to send an example to info@microsttraining.net ?

Best to zip the file. I could look at it today or Monday and see if I can spot a way of expanding the subform the way you want.

Ask in the email to forward to me, thanks.

Doug Dunn
Best STL

Edited on Sun 6 Jul 2014, 20:30

RE: Access: How to get subform to expand

Dear Doug,

I’ve just sent a cut down version of the database to info@microsttraining.net.
Please go to Forms > FRM_STORES_Orders > ‘The order’ (tab) and look at the subform at the bottom. I’d like it to shrink and grow depending on its content.
Similarly on ‘Items on order’ (tab), there’s one big subform. I’d like this to also shrink and grow depending on its content.
Both subforms (Form & Datasheet view) have been set “yes” to ‘Can shrink’ & ‘Can grow’.

Many thanks for having a look at it
Joe

I've just received an email failure message, so sent the database again. I again received an email failure message. The file is 5.8MB. Is that too large for the o@microsttraining.net mailnox?

RE: Access: How to get subform to expand

Hi Joe

Please could you zip the file. (Right click, Send to Compressed folder) and send again.

Thanks
Doug

RE: Access: How to get subform to expand

Dear Doug,

I have previously sent the database as a ZIP (twice) but nonetheless tried again (as a ZIP).
I received the same failure message!

Best regards.
Joe

RE: Access: How to get subform to expand

Sorry Joe
I've looked into why your file didn't get through. 5.8mb is not too large. You could also try forum@stl-training.co.uk

Cheers
Doug

RE: Access: How to get subform to expand

Hi Joe

Thanks for sending your database. It looks very good!
I can see better what you were asking. To make the subform for comments on Frm_Stores_Orders not display scroll bars and appear to expand to display multiple comment records.

The changes to make:
Open your Subform_Stores_orders_Comments
Switch to Design view
Select the Form properties (double click button on top left of form)
Set Navigation Buttons to No
Scroll Bars to None.
Change the Commentorder Height property from 1.05 to 0.9cm.

Close and save

Now open Frm_Stores_orders
Switch to Design view
Select (click once) on the Comments subform
Stretch the height of the Sub form a little (to 1.1cm).
Change the Border Style property to Transparent.

View Frm_Stores_orders

The subform only displays a vertical scroll bar when there are too many comments to fit into the subform.

Please let me know if that helps with what you were asking.
I've attached your database with these changes.
Regards
Doug

Attached files...

JoeGuelat (2).zip

Edited on Thu 10 Jul 2014, 08:31

RE: Access: How to get subform to expand

Dear Doug,

Thanks for you reply.

Unfortunately the method described is not what I'm looking for: From Subform_Stores_orders_Comments ... by removing the navigation buttons it took away the ability to add a new comment; made it shorter & removed the scroll bars so existing comments could no longer be seen; and it still doesn't shrink/grow depending on the number of comments.
Note: the database you attached doesn’t correspond to the explanation given ... the Subform_Stores_orders_Comments scroll Bars property is set to ‘both’ and (from the main form) it’s height is not 1.1cm (oh, I've just seen - you meant the stretch width, not height)

Subform_Stores_orders_Comments
I would like this form to be set short to start with and grow when there are more than one or two comments. There will only be a maximum of four so there is no danger of it overflowing the tab control.
Note 1: I put a border around it so I can see if it really is shrinking/growing
Note 2: Because the subform should grow, there is no need for scroll bars; they are just there now because it doesn't grow

SUBFRM_Tab3
I would like this form to be set tall to start with and shrink when there aren't enough records to fill it. When there are more records than can fit, it should add scroll bars - which is the case now

It's the shrink/grow properties I'm interested in and don't understand why they don't work.

Many thanks
Joe

RE: Access: How to get subform to expand

Hi Joe

Sorry my answer didn't help with what you want.
Both the Can Grow and Can Shrink property only affect the way data is printed rather than how it is displayed on screen. Pressing F1 with your cursor inside a property gives further information.

Unfortunately there doesn't seem to be a property to shrink the sub form when there aren't many comments. So, as you have done, decide on largest height that is practical then use the scroll bar if need be.

Regards

Doug
Best STL

RE: Access: How to get subform to expand

Dear Doug,

Sorry for the late answer (was away).
Thanks for the answer. Weird the ‘can shrink/grow’ properties don’t work on a subform (as they do for textboxes on a report), but if that’s the way it is…

Best regards.
Joe

Fri 25 Jul 2014: Automatically marked as resolved.


 

Access tip:

Create calculated fields that work out your age

You can uset eh year function to work out the year from NOW function and then subtract it with your date of birth type field

Age=Year(Now( ))-Year([DoB])

View all Access hints and tips


Server loaded in 0.05 secs.