Sub-forms need to show as Continuous Forms.

[weightinwildcat]

I am working on a form in Access 2010 that is needed to show information on partial shipments of line items from sales orders. I have a main form to show the order number, a customer name, the order date, and the total number of items ordered from all of the line items. There is a sub-form that shows information on individual line items. There is a second sub-form, which at present is a sub-form to the sub-form, that shows information on partial shipments for a sales order from a table created for that purpose. If a partial shipment is made, the information is recorded in that table. The default view for this additional sub-form is set at Continuous Forms. I would like to have the first sub-form display in this way, but Access will not allow this when the form in question has its own sub-form. I tried to work around this by putting sub-forms in footer sections, and it seemed like I was getting somewhere, but the first sub-form displayed near the top of the form, and the second sub-form displayed near the bottom of the form, with a large gap in between. Any ideas you can offer me would be greatly appreciated.

 

[MajP]

The way I do this I have two continuous forms, but do not nest them. Nested subforms are difficult to comprehend IMO. I put the subforms side by side and then you synchronize the two subforms. To synchronize the subforms put a hidden textbox on the main form and call it txtLink. Now link subform line items normally. In the oncurrent event add this code.

me.parent.txtlink = me.lineItemId
'where lineitem id is the PK on subform1

Now on subform 2 set up the following properties

Master Link field: [txtBxLink]
Child link field: [lineItemID]

so when you click on a record in subform 1 it will set the hidden link field to the id of that record. The second subform is linked to the hidden textbox and will show the related records for that line item.

 

[MajP]

See if this is what you mean. I have a main form with Employees. Subform 1 is the list of orders for that employee. If I move to an order record such as 10285 in subform 1, subform 2 shows the order details.

 

[weightinwildcat]

I was able to adapt your suggestion very nicely. Thank you!


One additional question if I may. I would like to have the top sub-form expand to show multiple records. At present it can show more than one record, but it does not resize for multiple line items. (I assume this is because the bottom sub-form does not move.) The bottom sub-form does this just fine. How do you suggest I enable the top sub-form to vary in size, and still be able to see the bottom sub-form?

 

[MajP]

Can you post a screen shot to explain what you would like?

 

[weightinwildcat]

The screen looks like this at present:

WE would like the upper sub-form to show everything like the lower sub-form.

 

[MajP]

If I understand then you would just repeat the same trick and use three subforms and make the main form unbounded. Add another hidden textbox on the main form "txtBxLink2". In the new order subform current event
me.parent.txtbxlink2 = Order# 'or whatever the parent key is
Now link the line item subform to txtbxlink2 like you did with txtbxlink.

Is this what you are looking for? Not pretty but it is 3 linked subforms. I click on an employee, see all of that employees orders, and click on an order and see its details. Some formatting would make this easier to follow, but it does work.

 

[weightinwildcat]

It should be vaguely more like this:

http://www.blueclaw-db.com/contin1.gif

 

[MajP]

I was confused, I thought you were referring to the main form. The only way the subform can show all records would be to size the subform larger but this may not look so good if you have few records. The reason the bottom subform "shows everything" is simply there are few records to show. You can size the subform on the fly so that it is just big enough. After sizing the top subform you would have to move the bottom subform down or up. This can be done by the on current event of the subform and main form. You count the records in the subform and then figure out how big to make it. Then move the second subform down. Here is some code to get you started

Public Sub GrowSub(subformctl As Access.SubForm)
  Dim intRecs As Long
  Dim rs As DAO.Recordset
  Const oneRecordHt = 0.4
  Const HeaderHt = 0.25
  intRecs = subformctl.Form.RecordsetClone.RecordCount
  subformctl.Height = InchesToTwips(oneRecordHt) * intRecs + InchesToTwips(HeaderHt)
End Sub

Public Function InchesToTwips(inches As Double) As Long
  InchesToTwips = CInt(inches * 1440)
End Function

Public Sub MoveSubForms()
  Const spaceBetween = 0.25
  'move subforms down based on the size of the other subforms
  Me.subFormCtl2.top = Me.subFormCtl1.top + Me.subFormCtl1.Height + InchesToTwips(spaceBetween)
  'if there was more
  ' Me.subFormCtl3.top = Me.subFormCtl2.top + Me.subFormCtl2.Height + InchesToTwips(spaceBetween)
End Sub

However. If you edited your subforms so that they are in tabular view (labels at the top of page instead of to the side and repeating), you could save a lot of real estate and could make those subforms narrow. Then you could do something like I did where you put the subforms side by side.

 

[weightinwildcat]

Thank you!