If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
One-to-Many Relationship problem
I have a database with 2 tables. One is a table with a persons bio data
(name, children, primary address, etc), and the other table is used to collect additional addresses and phone numbers for that person. I created a one-to-many relationship between the two tables (bio table is one and alt addresses table is many). I am using the AutoNumber field on the first table and linking it to a field called "Link ID" on the second table (this is not an auto number field). In the table view of the first table, I am able to click the + for a row of any record and see the fields for the second table. I have two forms, one that asks for the bio data, and one that asks for the alt address/phone data. I have a button on the bio form that opens the "subform" for that record and asks for alt address/phone. However, something isn't working. When I add the data, it doesn't link it to any record. The LinkID field in the table is always 0, instead of the autonumber from the first table. If I change the LinkID to the correct AutoNumber (so that it corresponds to a record in the first table), it displays properly. However, I want access to automatically put in the correct number - I thought that is what a relationship is for. What am I doing wrong?? |
#2
|
|||
|
|||
One-to-Many Relationship problem
Roy
?"... opens the 'subform'...?" It sounds like the "subform" is not actually embedded within the main form, but is just opened up in addition to the main form. Take a look at main form/subform construction. This gives you a way to still capture the data via a subform, but Access manages connecting the subform data to its "parent" in the main table. Regards Jeff Boyce Microsoft Office/Access MVP "Roy Carlson" wrote in message ... I have a database with 2 tables. One is a table with a persons bio data (name, children, primary address, etc), and the other table is used to collect additional addresses and phone numbers for that person. I created a one-to-many relationship between the two tables (bio table is one and alt addresses table is many). I am using the AutoNumber field on the first table and linking it to a field called "Link ID" on the second table (this is not an auto number field). In the table view of the first table, I am able to click the + for a row of any record and see the fields for the second table. I have two forms, one that asks for the bio data, and one that asks for the alt address/phone data. I have a button on the bio form that opens the "subform" for that record and asks for alt address/phone. However, something isn't working. When I add the data, it doesn't link it to any record. The LinkID field in the table is always 0, instead of the autonumber from the first table. If I change the LinkID to the correct AutoNumber (so that it corresponds to a record in the first table), it displays properly. However, I want access to automatically put in the correct number - I thought that is what a relationship is for. What am I doing wrong?? |
#3
|
|||
|
|||
One-to-Many Relationship problem
True subforms are embedded on a main form. Setting the master/child link is
what tells Access how to populate the subform's foreign key. This is not automatic. If the master/child link is unset or set improperly, the link between the parent and child will not be made and you will end up creating orphan records. When you open a stand-alone form, Access has no way of knowing which record you need as the parent. In order to properly implement RI on this relationship: 1. Remove the default for the foreign key. Access automatically assigns 0 as the default for numeric fields. Access has no way of knowing at the time you are defining a table that you intend to use a particular field as a foreign key. You don't want foreign keys to have any default (except in unusual cases) so it is up to you to remove it. 2, Then define the FK field as required. That will prevent Access from adding the record if the foreign key is not populated with a valid value. To solve the foreign key problem when using popup forms, 1. pass the value you wish to use as the OpenArgs value in the OpenForm method. 2. In the popup form's BeforeInsert event, place the OpenArg value into the foreign key field. I choose this event because it doesn't fire until someone actually types something into the form. This keeps you from dirtying the form programatically which can cause confusion for the user. An alternate event is the BeforeUpdate event of the form. It is the last event fired before the record is actually saved. Me.YourFKField = Me.OpenArgs "Roy Carlson" wrote in message ... I have a database with 2 tables. One is a table with a persons bio data (name, children, primary address, etc), and the other table is used to collect additional addresses and phone numbers for that person. I created a one-to-many relationship between the two tables (bio table is one and alt addresses table is many). I am using the AutoNumber field on the first table and linking it to a field called "Link ID" on the second table (this is not an auto number field). In the table view of the first table, I am able to click the + for a row of any record and see the fields for the second table. I have two forms, one that asks for the bio data, and one that asks for the alt address/phone data. I have a button on the bio form that opens the "subform" for that record and asks for alt address/phone. However, something isn't working. When I add the data, it doesn't link it to any record. The LinkID field in the table is always 0, instead of the autonumber from the first table. If I change the LinkID to the correct AutoNumber (so that it corresponds to a record in the first table), it displays properly. However, I want access to automatically put in the correct number - I thought that is what a relationship is for. What am I doing wrong?? |
#4
|
|||
|
|||
One-to-Many Relationship problem
I forgot to mention that you have not properly normalized your tables. A
person may have more than one child so children (or dependents) must be held in a separate table. You also may have the issue of addresses associated with the children as well as surnames. You didn't say what your application is so I don't know how important these things are to you. You also have to keep in mind that contact numbers are not necessarily associated with addresses. Cell phones and pagers come to mind. So contact numbers are generally kept in a separate table. If you want to associate phone numbers with addresses, the relationship becomes many-to-many between addresses and phone numbers (they are also many-to-many between people and phones although that is also getting rarer with the proliferation of cell phones) since an address may have many phones and a single number may ring at multiple addresses (rare). You may not wish to allow for the second case of a phone ringing at multiple addresses so in that case it would be acceptable to have the AddressID as a FK in the phone table, restricting the phone to a single address. "Roy Carlson" wrote in message ... I have a database with 2 tables. One is a table with a persons bio data (name, children, primary address, etc), and the other table is used to collect additional addresses and phone numbers for that person. I created a one-to-many relationship between the two tables (bio table is one and alt addresses table is many). I am using the AutoNumber field on the first table and linking it to a field called "Link ID" on the second table (this is not an auto number field). In the table view of the first table, I am able to click the + for a row of any record and see the fields for the second table. I have two forms, one that asks for the bio data, and one that asks for the alt address/phone data. I have a button on the bio form that opens the "subform" for that record and asks for alt address/phone. However, something isn't working. When I add the data, it doesn't link it to any record. The LinkID field in the table is always 0, instead of the autonumber from the first table. If I change the LinkID to the correct AutoNumber (so that it corresponds to a record in the first table), it displays properly. However, I want access to automatically put in the correct number - I thought that is what a relationship is for. What am I doing wrong?? |
Thread Tools | |
Display Modes | |
|
|