Link Child and 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!

 

[BillPower]

Hi almal,

It sounds like to me that you have the same query/recordsource for both the main form and sub form, try splitting them.

Main form - query TblEmployee only

Sub form - query TblContact only

Bill

 

[almal]

Hello Bill,

That is the query/recordsource I have started off with.

I've since tried:

Main form - query TblEmployee and TblContact
Sub form - TblContact table only

This resulted in the Link Child Fields and Link Master Fields being the same (yea!!) - EeID - but a new error came up:

"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."

Appreciate your help!

Alma

 

[BillPower]

Sounds like EeID in the TblContact is a Primary Key or Unique. Change the field so that it's not a Primary Key or Unique.

Bill

 

[almal]

EeID in TblContact is not a Primary Key, and it's not required and not indexed.

I've added a ContactID that is a Primary Key in TblContact, but that doesn't change the error messages.

Alma

 

[BillPower]

Hi send me a copy of your DB to me at billpower@cwcom.net remove any sensitive records first.

Bill

 

[almal]

Thanks Bill! I'll forward it to your email address.

 

[BillPower]

Hi almal

I've changed the Recordsource in you form sfrmPerfApp

From:

SELECT tlkPerfApp.* FROM tlkPerfApp;

To:

SELECT [lngPerfAppID], * FROM tlkPerfApp;

Seems to work ok now.

Bill