expanding subforms?

[mango97]

Hi,

I'm working on a scheduling system for my fathers company that automates some redundant tasks. Most importantly the system manages the order of processes on different parts (ie, part "tubes" may be cut, then drilled, then welded, then shipped).

I'm trying to create a form that allows for the editing and viewing of all the parts and the processes for each part for a specific work Order. Ideally the information that applies to all the parts (Work order Number, due date etc) would appear at the top of the form with all of the processes to appear beneath (Separated into groups - all the parts to be welded shown together). My problem with using sub forms for this was that I need all the records to be shown without scrolling. Is there a way to dynamically change the size of each Subform to match the required size, or is there a better feature to use?

thanks,
mango97

 

[CJ_London]

from the main form, determine the number of records in your subform using

me.subformcontrolname.form.recordcount

Then multiply this by the height of each row in twips (1cm =567 twips or 1 inch =1440 twips)

then add the height of header and footer if showing (again in twips)

then adjust the the height of the subform with

me.subformcontrolname.height=calced value

Not if too may rows show so the subform becomes bigger than the main form, you will get an error so you will need to protect against this as well - something like

if calced value+me.subformcontrolname.top<insideheight then
me.subformcontrolname.height=calced value

 

[mango97]

Thanks for the help, Ill be sure to implement this soon. I've got 1 other questions to ask you...

Is there a way to expand a form larger than 21 inches?

 

[CJ_London]

Is there a way to expand a form larger than 21 inches?

Yes and no - the actual maximum form height is 200 inches! BUT the maximium section height is 22.75 inches and you can't have a control which spans sections. I would design your maximum subform height so that it always displays on the screen - any show the vertical scrollbar.

One useful tip, is to put the following in the main form resize event so the subform always shows and it's height automatically adjusts as the user adjusts the size of the form

me.subformcontrol.height=insideheight-me.subformcontrol.top

if you have form header and or footer showing then you also need to deduct the heights of these as well


Here is a link to the maximum specifications for Access - forms are about half way down

http://office.microsoft.com/en-gb/access-help/access-2010-specifications-HA010341462.aspx

 

[mango97]

Yes and no - the actual maximum form height is 200 inches! BUT the maximium section height is 22.75 inches and you can't have a control which spans sections.

I was looking into this earlier and I couldn't find a way to create multiple sections (other than the detail, header/footer and page header/footer). Is there a way to create new sections?

 

[CJ_London]

On forms, no, but you can on reports. This link shows how to create report sections

http://office.microsoft.com/en-gb/access-help/add-or-remove-a-section-HP005187900.aspx

 

[vbaInet]

Just wondering if mango97 would benefit from using a Tab control.

On the other hand you mention that the parts need to be in "Separated into groups" but I wonder if this necessarily needs to be put into separate subforms? I doubt this.

If all the parts are related and are recorded in the same table you can have one subform and a combo box that can be used to drill down the records to a certain part type. Probably something to think about.

 

[mango97]

Using subforms was actually a work around for a different problem. Everything is divided into work orders. Each Work Order contains a number of parts that have different shop processes done to them to create the part.

This form shows which work order each process is within. So what I was trying to do with the subforms was not repeat names of shop processes or work order information for each proccess. I'm not sure if this makes sense or not, but is there a better way to only have the information displayed only once?

I ended up writing the following in another thread, which has ended up at the same topic as this one:

OK, full explanation..

I am working on a database which manages the creation of transformer clamps and cores. Each clamp/core is given a work order number which contains information about the customer, the price, the due date and such.

Each clamp/core has multiple parts which go through a set of processes to be created. This form shows a work order with each part separated into the processes done to it. I don't want to have any repeating data on the form, meaning I don't want the work order information to be displayed more than once and I don't want the process name to appear more than once for a given work order. Ideally, All the work orders would appear beneath one another in a list which could be filtered to show a single work order.

My current solution for this is to put the information for each process into a sub form that filters to show the parts that are for the specific process. Since its a sub form it also only shows the values for the work order. The problem with this approach is the maximum size for a form section is about 21" (if my memory serves correctly) which means when I have 15 sub forms, each form can only show 2-3 entries before scrolling. This proves problematic when some work orders have over 40 entries in a specific sub form. Additionally in some cases not all processes are done for the entire work order which means space is filled that doesn't have to be there.

I understand that with some VBA I can dynamically change the size of the sub forms and hide the unused forms, but ultimately the 21" section maximum results in being unable to show all the entries without scroll bars.

By formatting like a report I meant that when I create the report that corresponds with this form I was able to put the information I did not want to repeat in their own section, resulting in the information only being showed once. Is there something like this that I could use in my form to have this behavior?

I hope this was more clear. Please ask specifically what I need to clarify!

Thanks,
mango97