Link Child & Master Fields - error (1 Viewer)

I've only been using Access for a week or two and have come across a frustrating problem... The description below is a simplified version of the tables and form/subforms, but essentially demonstrate the problem. Sorry for the long post!

I have 2 tables linked by a 1:many relationship and am trying to create one form with a subform. The error message is:

"The LinkMasterFields property setting has producted this error: the object doesn't contain the Automation object 'TblEmployee'.


Here is info on the 2 tables:

TblEmployee
- Primary Key is a field called EeID
- Other fields include - EeFirstName and EeLastName

TblContact
- No Primary Key
- Fields are - EeID, HPhone, and Pager

These 2 tables are linked by the EeID field:
TblEmployee / EeID - 1 to
TblContact / EeID - many
The join type is 2 -- include all records from TblEmployee, and only those records from TblContact where the join fields are equal.

In TblEmployee, the EeID field is an AutoNumber, long integer. In TblContact, the EeID field Data Type is "Number", long integer. The Lookup tab for EeID in TblContact is set to:
- Combo Box
- Query/Table
- SELECT [TblEmployee].[EeID] FROM TblEmployee;

Here is info on the form / subform:

The form has 2 fields (EeFirstName and EeLastName) and they work fine.

I have tried creating a subform using various methods (wizard, dragging from database view and linking using wizard or by typing in link child & master fields). When I insert data in the subform in Form View, I get the error message quoted above.

However, I can insert data by opening the table TblEmployee and expanding the other table, TblContact. This new data shows up on the form and subform!

I created a dummy database and went through all the steps to see if the problem is in the first database (which contains quite a few other tables) or form (which contains tabs, etc) and sure enough the dummy database works!

The difference I notice between the dummy database and the real one is that on the dummy database, the Link Child Fields and Link Master Fields are exactly the same:
Link Child Fields - EeID
Link Master Fields - EeID
whereas in the real database, these 2 fields are different:
Link Child Fields - EeID
Link Master Fields - TblContact.EeID

If I manually type in the Link Master Fields - "EeID", I get another error when opening the form: "Enter Parameter Value, EeID".

What do I need to do to get the subform working?

Thank you for reading this post and I really appreciate any help!

Could you zip up your db and post it so we could have a look and help???

What is the recordsource of the form and subform? have you used the tables for the recordsource or built a query to feed the forms?

Unusually, Access is setting the link field on the mainform to a field in the tblContacts (the many table) I suspect it is something to do with the recordsource setup or your relationship definition.

1. The contact table should have a unique ID. Add an autonumber and make it the primary key.
2. In the relationships window, connect the two tables on EeID and check the enforce referential integrity box. Then check the cascade delete box.
3. The forms should each be based on a query. The main form should be based on a query of the employee table. The subform should be based on a query of the contact table. The subform query should NOT include a join to the employee table.
4. The contact table should not have separate fields for home phone and pager. It should have separate rows. The table should be:
ContactID (autonumber primary key)
ContactTypeID (foreign key to contactType table
ContactData

The contactType table should contain entries such as HPhone, Pager, email, Cell Phone, etc. This will allow you to store any type of contact information. Even a type not allready in use such as an embedded phone chip.

The recordsource for the subform is the table TblContact.

The recordsource for the form is a query that only had the fields -

TblEmployee.*
from TblEmployee

I have since added to it the fields -

TblContact.*
from TblContact

Now both Link Child Field and Link Master Field say the same thing - EeID

But when I go to enter data onto the subform through the main form, in Form View, the error message that pops up says -

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to to permit duplicate entries and try again."

The dummy data I've been entering is varied and this same message appears. Also, in the tables, none of the fields are required (except the primary key in TblEmployee), and none of the indexes do not allow duplicates.

In the relationship between EeID in TblEmployee and EeID in TblContact, I've checked both the enforce referential integrity and cascade delete boxes.

Thanks for your help!

I have since added to it the fields -
TblContact.*
from TblContact

Click to expand...

- the contact table should not be included in the main form's query.

Now both Link Child Field and Link Master Field say the same thing - EeID

Click to expand...

- they should have said that before you made the above change since EeID appears in BOTH tables and is the linking field.

"The changes you requested ....

Click to expand...

- this error is caused when key data is not supplied. In your original post you said that you had no key for the subform's table. I suggested an autonumber PK. Autonumber PK's populate automatically and so would not cause this error.

Thanks for your comments Pat. I've made changes as suggested ---

The Contact table is no longer part of the main form's query.

One visible aspect of the problem is that the Link Child Fields and Link Master Fields don't match, even if I go through the wizard.

I've created an autonumber PK in the subform, but it doesn't get rid of the error messages.

I've zipped and attached the db here. Pls note that I had simplified my questions in this forum:

- The main form is actually called frmEe, and the subform is sfrmPerfApp.
- I want to have the subform on the tab entitled 'Perf and Comp' in the main form.
- The table in question is tlkPerfApp.

If you have created a relationship between the two tables, Access will assume that the master/child links should be made on those fields.

If it doesn't make an assumption, you should be able to pick the correct fields from each side manually.

FYI, I couldn't get the sample to work because it is missing a table that the main form needs. I did notice that you have way too many indexes on some of the tables. There is no percentage in making indexes on columns that have very few values such as Gender. The query optimizer cannot use such an index and will do a full table scan anyway. So that index especially is just deadwood. Having an index on DOB doesn't seem useful unless you actually use it to look people up.

It's strange, I created the relationship between the Autonumber PK in the main table and the field in the subform that has the same name as the AutonumberPK.

And yet the master/child link was automatically made to an Autonumber PK in another table (whose info is also on the main form).

When I pick the correct fields manually, the error message about not containing the Automation object comes up when I try to insert data.

Thanks for the tip on the indexing - I'll only put indexing on the fields we're likely to search by.

As for the other problem, I'm starting an Access course tomorrow night, and hopefully the instructor will be able to look at the db and know exactly what I've done wrong.

Thank you for your help!

There are a lot of tables in the query for the main form. I couldn't look at it because some of the tables were missing. Rather then using the asterisk to select all columns, be selective. It is much more efficient especially if your tables are linked to ODBC data sources. You must be getting duplicate column names in the query. If you run the query, you should be able to see them.

I would use a separate subform on each tab rather than having all those tables in a single query. Then the main form should be bound to the "parent" table, i.e. the one with the key that ties all the subforms together.