how to create form\subform

[catcoota]

Hi

I have a basic question related to database child-forms

If I have 2 tables, one called tblorder (orderID, productID, date) and another one called tblproducts (productID, ProductName, Category) and they are linked throuh 1:M relationship (one order could have multiple products)
I want someone to teach me how to create a form (orders) with a subform (products) where I create a new order , in other words, I want to enter data from in the form and the subform without problems,,, I hope someone could explain it step by step!

 

[Lynn_AccessUser]

First you need to create a 3rd table to store the one-to-many relationship between Orders and Products. For example if OrderID = 1 has 2 orders then the table will show OrderID =1, ProductID = 1, OrderID =1, ProductID = 2.

Attach is a quick and dirty sample db showing you the basics.

 

[Pat Hartman]

If you haven't already installed the Northwind Traders sample from your Office CD, do it. It is an excellent tutorial with lots of help and is exactly the type of application you want to build.

 

[catcoota]

Lynn_AccessUser


Thanks a lot for the example, i got the idea

Pat Hartman

I've just seen the database, & i liked the idea but i couldn't apply it in my database i don't know why?

I noticed that Order table in north wind is linked to Order details thru 1:M relation ship where both fields are primary keys of 2 of the tables, but I could't do the same in my database! i get a 1:1 relationship although i set all the properties just exactly like the properties of the northwind table

Can anyone help me?

 

[Pat Hartman]

The OrderDetails table uses a compound primary key. It includes both OrderID (foreign key to the Order table) and ProductID (foreign key to the product table). To make a compound key, select the OrderID and then cntl-click on the ProductID to highlight both fields. Then press the key icon from the toolbar.

I prefer an autonumber pk for the OrderDetails table. The two-field key used by Northwind restricts the ProductID from being included in the order more than once. In some applications that is fine. But, in others, such as clothing where a size and/or color is also required, you may need two of the same product on a given order.