Linking Two Subforms within Main Form

[abjacs]

Hi.

I have created a main form that contains two subforms.
The main form is based on a Customer table while the other two subforms are based on tables Orders and Line_Items.
Also, Customers is (one-to-many) with Orders and Orders is (One-To-Many) with Line_Items.

Line_Items contains a FK, OrderID that is the Primary Key of the Orders table.
I want Line_Items form to show all line_items for the current Order being viewed.

I created a textbox in the main form (Customers) that has as its Control Source the OrderID of the Orders form. Here I'll call it hiddenOrderIDBox.

Next, I tried the subform field linker to link OrderID in Line_Items form to the newly created textbox in the main form (hiddenOrderIDBox).

However, within the master fields drop down of the linker no textboxes that I've added to the main form myself will ever show up, namely my hiddenOrderIDBox. So, my line_items forms instead shows all line_items and not the order specific ones I want.

I've checked and my relationships between the tables all seem correct.

Any advice is GREATLY appreciated?
Thanks!

 

[CEH]

I'm not sure I follow the reason for the "hiddenOrderIDBox" It sounds like it might be correct... maybe missing a FK... A customer is going to have many Orders.... therefor ORderID as FK in Customer table... And Orders will have many Items... therefor ItemID as FK in Orders table.... Your "hiddenOrderIDBox" is probably unbound... therefor really does nothing.

 

[abjacs]

The attachments show text from a book talking about what I want to do. But the last step, linking a subform to a newly created textbox won't work for me since that textbox won't show up in the linker thinga-ma-jiggy that Access provides.

The book is:
Fixing Access Annoyances
by Phil Mitchell; Evan Callahan
Publisher: O'Reilly


The main form is about the customer.
When a user is viewing a particular customer all the orders for that customer will show up (in the order subform).
Nex the line_item form *should* show all the single line item orders corresponding to the particular order that is currently being viewed (for the current user being viewed).

The order table is a summary of the order, i.e. date, total, discount, etc. while the line_item table shows each line_item that is part of that particular order.

Since, the order and line_item forms aren't nested they can't "see" each other and therefore aren't linked in the form.

Apparently, I can link any subform field to a field in the parent form but I can't (easily) link these two subforms together. That's why I'm using the hiddenOrderIDBox that is bound to ther OrderID in the order form. Then, I want the line_item form to link to that box in order to only show line_items for the particular order being shown.

This is the heart of my question:
Whenever I create a new textbox in my main form and then try to link the subform to that textbox, the newly created field won't show up.

 

[abjacs]

Here's a link talking about what I want to do but can't for whatever reason.
Has anyone ever tried something like this?

http://www.fmsinc.com/free/NewTips/Access/accesstip43.asp

 

[ptm0412]

I have an idea:
Make the data source of the Line_Item subform to be a query rather than its current resource, which query filtered by the orderID selected in the Order Subform. Whenever you select an order in Order Subform, you should have that query Requery, or the Line_Item Subform Refresh.

 

[abjacs]

Will I still be able to bind my second subform to a table?

I want to be able to view records like I've been talking about but I also want to be able to add new records also. That would mainly be add, after selecting the customer buying, add a new order and corresponding line_items.

Thanks.

 

[CEH]

I am still not understanding the need for a hidden box, or the need to link anything....If you have the correct setup and simply build a form with the wizard... you will see it works as you want it. I tried this with a DB I have... The new form is built on a query of 3 tables... Customer table, Order table, (containing OrderID,Order Date, and CustomerID.. and the order detail table (containing OrderID, ItemID, ItemPrice, and Units ordered.) I would also add my "Items List table" this to be part of my second subform. The setup here is Customer table is related to Order table on CustomerID 1 to many, Order table to Order detail table on a one to many on the OrderID PK in Order table and then the Item table is related to the Order Detail table from the PK in the Item table, ItemID on a one to many to the Order detail table.
The wizards in Access are a great way to learn how to relate subforms to mainforms.... Try that and see if it works for you.

 

[ptm0412]

As I know, it is allowed to add record to a query also. please try and check.

 

[abjacs]

Alright, I'm having way too much trouble trying to explain my predicament.

So, I created my own project to illustrate what I'm trying to do.
In my file, I have few tables and a three forms.

The fmCustomer is the main form that contains two other subforms fmOrder and fmBeverageOrder.

Here's how all of this should work:

A user is being viewd while in fmOrder all the orders for that user are shown.
At the same time in fmBeverageOrder is displaying the specific order details for the whichever order is currently selected(active).

Currently, the fmOrder and fmBeverageOrder "don't know about each other" and therefore fmBeverageOrder is showing the entire BeverageOrder table instead.

Also, I have created all the needed relationships between tables and PK's too.

It'd be great if anyone can possibly make whatever fix is needed and upload the project again. I hope to be able to use the form optionally as an order form and also as a way to grab the OrderID and BeverageID and send to a separate form in order to grant a refund.

Thanks for the fast and continuing support!
(If anyone is afraid to open the project I understand even though I'll tell you that it's clean)

 

[CEH]

Sample

OK..... Heres a little sample of what I was talking about.... Now I used all fields... Did away with the refund... for this example... And let the wizard do the rest... But this will show you the relationship setup.... I didn't look into any of your data either... But you should see the detail of each order in the lower subform as you click on the record in the upper subform.......

 

[wiklendt]

hey CEH,

i'm also trying to get two sibling subforms to be linked.

i downloaded your example and found that new records cannot be added to the beverage order datasheet (easily, that is - and there are a couple of error messages that pop up - see attached).

i think the issue may be in your subform syntax in the master/child links. however, i cannot say for sure b/c i'm still trying to get my subforms working; i am half way there, i can add records, but haven't applied the correct VBA/SQL to get the records to display when moving to a different record in subform1... will keep you posted.

wiklendt

 

[wiklendt]

Ok, i've had more time to look at this. A few comments:

It was not the subform syntax causing issues to adding more beverages to each order. it was the fact that you had autonumber for orderID in the beverages_order table. I have made certain adjustments (See attachment). You can now add as many beverages you want to each order as you go. i adjusted the fields you display in the beverage orders such that it's not only simpler and more intuitive, but generates no errors!

Also, i added another subform to display list price of each drink. This prevents superfluous data in the bev order subform.

Something else i noticed is that your item refund table was linked to your beverage order table with two relationships - i added an autonumber field BevOrderID into this table and this is now what is linked to your item refund - makes it easier to search which order you want to refund.

To make data entry easier, i've added a few drop down boxes. now, i haven't done this comprehensively throughout your database, as this is not my job, i'm just demonstrating a few tips.

Something i have started doing as a matter of course is to name my forms/tables intuitively. for instance, in your database i have named the forms such:

CUSTOMER
CUSTOMER_Order
CUSTOMER_Bev
CUSTOMER_BevInfo

once you beging to acquire more assets (like a new beverage entry form, your item refund for etc... it will be easier to see which form belongs where just at a glance) - actually, i should have also preceded these name with "frm" or the like - will make it easier when you have to start adding VBA...

also, don't forget to compact and repair your database periodically. i use Access 2007, and here they have the option to compact on close, i suggest you do that with your databases, it keeps everything nice n small (byte wise).

good job on keeping the data normalised, though - i didn't do so well when i first started out!

i know this thread isn't very recent, but i thought you may still appreciate it...

Cheers,
wiklendt