DataBase - Using Access 2003

Asked By Jennifer

20-Nov-09 02:31 PM

I have one table called customers and a table called orders. The primary Key
is CustomerID and the foriegn key is attached to Orders. I then created and
orders and customer forms. I want it so that when I have a Customer name on
the first form I only want orders from that customer to show up on the orders
form. I have tried creating a 1 to many relationship. That did not work for I
deleted. I did a insert a subdatasheet but it only saved that first
cumstomer's information. Please help? Jennifer

20-Nov-09 06:30 PM

Jennifer,
Use a Form/SubForm arrangement and use queries of your tables rather than
directly to the tables. The LinkChild/MasterFields of the SubFormControl will
be the CustomerID.


--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200911/1

20-Nov-09 06:41 PM

Hi Jennifer,
You may want to search your harddrive for Northwind.mdb; it has exactly the
same scenario you are speaking of. Dissect it, and you can learn quite a bit
on how to setup tables and their relationships, and then build forms on top
of that.

-Tom.
Microsoft Access MVP

20-Nov-09 06:43 PM

You need to create the 1 to many relationship between table called customers
and a table called orders with the primary Key CustomerID and the foriegn key
in Orders.  Select options Referential Integerity and Cascade Update.

If you can not set the relations due to error it will be that you have one
or more records in the Orders table that does not have a matching CustomerID.

Run an 'unmatched' query to find and fix the records.  Complete setting the
relationship.

Use a form/subform for customer/orders with Master/Child links with
CustomerID.

CustomerID does not need to be shown on the subform. As you add a new record
in the subform the CustomerID will automatically inserted in the Orders table.

Scrolling through the Customers in the main form you only see the orders
related to that Customer shown on the main.

--
Build a little, test a little.