Auto-resizing subforms

[KAyers]

Hello-
(I'm trying to resize a subform automatically depending on how many records it is displaying. It needs to resize to fit all of the contents it displays, but no more. This needs to be done every time the user cycles through records in the main form) so that for each record the subform resizes to fit that particular recordset's subform data- instead of just expanding once and staying that large. As an aside- the subforms scrollbars are turned off to allow enough room for the data to show up within the design constraints of the main form which is why the resizing is necessary. I searched for a solution and found something from 2002 but I can't open the zip file that was attached to the reply. I would appreciate it if anyone has any insight.
Thanks!
K
message in () copied from the 2002 thread- it said what I was looking to say, so why remake the wheel

 

[GolferGuy]

If you can't open a 2002 db, what version are you using? If you attached that zip file here, maybe someone could open that 2002 version and convert it to the version you are using.

I have resized a subform control to fit the size of the main form as I have changed the size of the main form by draging the top or bottom of the form to resize it. What I would suggest, if you can't ever get the 2002 database open, would be to calculate, through trial and error, the height of each subfile record, then as you go through the main form records, have a routine that will count the subfile records (Use DCount on the table that supplies the records for the subfile, and use the same criteria in the DCount that is used for the subform records.) then resize the subform control to match, depending on the number of records in the subform.

 

[KAyers]

Thank you for the DCount solution... I will try that out and let you know how it works. As for the db- it's not a matter of my Access version but that I can't download zip files at work which makes life more interesting when dealing with dbs.

 

[KAyers]

I'm not sure the DCount is what I'm looking to do- I would rather keep this as simple as possible and have the subforms height resize (perhaps using AutoResize?) to display the data, as little as 0 records and as much as 90ish and highly subject to change. What I'm having problems with is where to control the resizing- in my main form Wiring!tblLineNumbers1_A.Height or in the subform itself where I created all of the design for the layout displayed in the main form. Maybe I'm just chasing my tail but it strikes me that I should be able to have the subform expand to fit the data which then expands the entire main form. Since I can't play with zip files I will attach some screen shots:
The three boxes on the bottom of subforms.jpg are the subforms for A, B, and C respectively in the main form. Currently they only show 4 records. The user would like the height of both the subform and main form to resize to fit the number of records in the subform for that particular set up. In that way, if for the W2160 configuration there are 15 records for A, 73 for B, and 25 for C, then as the user scrolls down the main form all three #1 records for A, B nd C are lined up, as are #2, #3, and so on...
subformA.jpg is just a shot of the subform layout outside of the main form.

Thanks for any help... I feel like I'm looking at this as if it were too complex when the solution is quite simple and I'm just missing it!

 

[GolferGuy]

The only way I know of to change the size of a subform on a form is to resize the subform control using VBA. If you think there should be another way, keep looking. I have never seen another way, nor heard of any other way. In VB.net or VB 6.0, or if you want to find (I do not know of any) a specialized control that can be "easily" resized, keep looking. But if you just want to do a little coding and get on with your life, I would suggest doing the DCount for each of your A, B, and C, finding the one with the most records, then multiplying that biggest number times the height of one record in your subfile, then using that number to set the height of each of the three subform controls on your main form, AFTER setting the height of you main form to it's current height plus the amount you are going to set the subform controls to minus the current height of the subform controls. Yes, that looks complicated, but if you read it slowly, one step at a time, it is all there and really not all that hard.

Good luck.

 

[KAyers]

While I thank you for the explanation, the condescension was not necessary... I was not dismissing you by asking for something other than the DCount but was under the impression that it would keep the subforms at their constant maximum height and therefore always require scrolling to the bottom of 90 records of height for the subform controls.

 

[GolferGuy]

The condescension was not intended, sorry it sounded that way. I just wanted to explain that the DCount is the only way I know of to solve your request, except for the other ways that I believe would be a waste of time. And yes, it did sound to me that you were dismissing the DCount to find something easier.
I have never seen, but that does not mean it has not been done, any forms that have been resized to accommodate a larger or smaller subform. I have done it for a cash flow tracking system for my personal finances, therefore I shared what I have learned from that experience.