Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

This is an easy one...

Status
Not open for further replies.

michaelreaves

Technical User
Sep 10, 2001
2
US
Hello,

I'm beginning to use MS Access 97 more and more every week, and I need some help as I work through learning how to develop databases.

This time, I need to be able to use one form to modify and create new records in up to 7 tables at a time. I'm not experienced in programming (well, except HTML and beginning JavaScript), so please be easy on me... I'll give examples to make this easier on you:

I have a table with names, a table with addresses, a table with phone numbers, and a table with interests. I want to use one form to enter info into all 4 of these tables. How do I do this? Do I need to make relationships? If so, where? Please reply to this message, and as well send a copy of the reply to:
m_r_productions@yahoo.com

Thanks in advance for any help...
MICHAEL REAVES
 
Hi Michael

Yes, relationships will definitely help.

You can update all the tables from one form by using subforms for the subsidiary tables. The relationships will help display the data in the subforms that is linked to the record in the main form.

Step 1: Set up your relationships
Your main table (Name?) will need to contain a primary key field. Each of the subsidiary tables will need to contain the same data field (referred to as a Foreign Key).
Open the relationships window by clicking on Tools|Relationships on the menu, or by clicking on the Relationships icon on the toolbar. Add your tables to the window by selecting them from the dispalyed list and click the Add button.
From the main table, click on the Primary key field and drag it to the equivalent field in the first subsidiary table. This will open a dialog box with some options to choose from. Select the appropriate options for your relationship ans then click OK. The relationship is now set.
Repeat this process for each subsidiary table.

Step 2: Design your Form
Use the wizard to set up a new form based on your main table, and then open it in design view. From the toolbox, add a subform control to the form, ensuring the toolbox wizard is turned ON. Follw the steps in the wizard to complete the process.
Repeat this for each subsidiary form.

You're now ready to enter data.

The following topics in the On-line Help will provide more detail on these processes.
About relationships in a database
Define relationships between tables
Display the Relationships window
What is referential integrity?
Why should I use cascading updates or cascading deletes?
Create a form with two subforms
Create a form with multiple pages or tabs


HTH
Lightning
 

You might consider not to divide that info in different table unless you are doing it because you need it. If your example is like this:

table 1
-------
Customer_ID
CustomerName

table 2
-------
Address_ID
Customer_ID
Address

table 3
-------
Phone_ID
Customer_ID
PhoneNumbers

table 4
-------
Interest_ID
Customer_ID
Interests

then you are assuming that two different customers might have the same address or the same phone number or the same interest. If that is so, then make sure that all the initial ID are primary keys and make a relation from the cutomer_ID field on table 1 to all the Cutomer_ID of the other tables.

If that is not what you intended to do then a simple solution would be to create two tables in case the interests only might be the same for more than one customer:

table 1
-------
Customer_ID
Name
Address
PhoneNumber

table 2
-------
Interest_ID
Customer_ID
Interest

HTH
Alcar
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top