linking tables and relationships

[SharonMee]

Hello all:

I think I posted this in the wrong forum earlier. I have a table called Member List, it has an ID column (auto number), Last Name, First name.

I want to create another table, Address List, such that it would have the same ID # as in the Member List and also have extra columns for apartment, city, province, zip code. Could you walk me through how I would connect these tables together so that when I need to add a new record in the Member List, I can just add the first & last names and then go to the Address table, put in the new id number gotten from the Member list table and then put in the new addresses. This way, I would not need to repeat the names in the Address table.

The second question is: How do I create a form that will have all the fields in these 2 tables, that way I can update the tables (add a new record or correct an existing record) using the form.

Please help, thanks.

 

[PHV]

Provided your relations are properly created (menu Tolls -> Relations ...), just follow the wizard.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

In your table AddressList you need to have a field named MemberID that is a Long.

In the relationship window, add the Member table and the AddressList table. click on the MemberID in table Member and drag it to MemberID in table AddressList.

Now you have the relationship set up. Using the form wizard you can easily set up a form that allows you to enter the address information for each person.

HTH

Leslie

 

[SharonMee]

Thanks Leslie:

I have established the relationships, as you suggested and I think that's fine.

Then, I went to the form wizard to add fields in the tables to the form, that went okay (I think), also it asked if I wanted a subform or linked form? Didn't know which one to choose, so I chose subform.

When I viewed my Form and changed the Member ID # and pressed enter, it did not give me the corresponding information for that ID#. Do you know why? I want to be able to put in an ID # and then update the tables through the form, is this possible?

Thanks.


SharonMee

 

[willir]

SharonMee

If the relastional design setup is correct, you may just have to tweak your form / subform.

With the main form in design view,, and the properties window open ("View" -> "Properties"), select the subform. In the "Data" tab of the properties window, you should see "Link Child fields", "Link master Fields". (If you do not see this option for the data tab, select the main form, and then select the subform again.) Select Link Child fields - a command button with "..." dots appears to the right - click on this commadn button to open up the Subform Field Linker window. Make sure the Master field and Child field are using the fields you are using to link your "Member" table to your "Address" table.

Try again. Magic!
Richard