Link Child & Master Fields - error

[AlmaL]

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!

 

[louchey]

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

 

[Fizzio]

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.

 

[AlmaL]

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!

 

[AlmaL]

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.

 

[AlmaL]

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!