Workaround for a subform within a continuous subform?

[rstrong]

Hey,

I have two tables. The first table, tblOrders, has general order information and the second, tblItemInfo, has information about specific items within the order. The primary key for tblOrders is OrderID. Each item within an order will be in tblItemInfo along with the OrderID it is associated with. So within tblItemInfo there could be multiple rows with the same OrderID, meaning those items are in the same order.
I need a way to list all the orders and below each order have a list of the items within that order. When I created a subform and tried to put it into the detail section of a form I received an error message that I couldn't put a subform within a continuous form. Any suggestions?

 

[MD5150]

This kind of output is best done in a report. I would suggest using a report for this instead of a form.

Mike Dorthick

 

[rstrong]

I considered using a report since it can do exactly what I want but I need the user to be able to click on different orders as well as the order items. The only other solution I can think of is to list all the items with the OrderID next to each. This doesn't really separate the Orders like I wanted but I don't know of any other way.

 

[MD5150]

>but I need the user to be able to click on different orders as well

Typically, when using forms, this is how its done. The user has to click, or select, an order to see its details. With this, you have many options:

- user selects order# from combo box...returns detail.
- users selects order# in a listbox list of orders...
- use two listboxes....orders/details
- user enters order# in text box.....
- two subforms: one orders, the other details
- popup order selection...
- treeview control...

Many ways to do it if you accept having the user select the order to view.

>The only other solution I can think of is to list all the items with the OrderID next to each.

Yes, but again, this is more of a summary type of view, rather than a normal add/edit order entry form. Also, you will probably have difficulty not having the order info repeat for each detail line item. It's better in the long run to stick to using forms for add/editing records, and reports for viewing/history data. Also, as you collect more and more data, making use of filters will keep things running faster...rather than showing all orders.

Hope this helps,
Mike Dorthick

 

[Remou]

Have a look at the Northwind Sample database, the Customer Orders form may suit.

 

[RuralGuy]

My solution is to put two continuous forms side by side on another form and have them linked with the LinkChild/MasterFields similar to what Albert has done in this link:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[MajP]

You can use synchronized subforms instead of nested subforms. You can put a subform in the footer as a possibility to that the details for the selected record always appear below. AceMan describes one way to do this:
faq702-5860

I use another way. On the main header or footer you can put a hidden unbound text box, call it txtBxLink. Now on the on current event of the orders form update the value of txtBxLink to orderID. Simply link the subform (in the master child relation) to the text box. When the value in the textbox changes the subform updates.