Unable to add new records in sub-form

[Peter]

I have a contacts database developed in Access 2002 with a one to many
relationship between a main contacts table and a another table containing
service records. These tables are conatined in a back-end data base. A
simplified overview of the tables:

CONTACTS
Contact_ID (PK)
Category Code
name
address

SERVICEDETAILS (can have one or more entries per contact)
Contact_ID (FK)
start-date
end-date

The service details table is only populated with records relating to
contacts from the main table that have a specific category code (say code X).

I can open the main contacts table in data sheet view then clicking on the +
sign for a row I can select a sub-data sheet corresponding to the "many"
service details table and can display the records from this table and even
add new records. I can also filter the contacts table records to only
display those relating to category code X and still add records to the
sub-data sheet.

I am trying to achieve the above via a form which contains the main contact
details with an embedded sub-form to display the "many" service details. The
sub-form has had the correct master-child links established. The main form
has as its record source a query which includes all the main fields from the
contacts table but filtered to only include the records relating to Category
X as indicated above.

The form will display the contact records required and the corresponding
service records in the sub-form for the Contact_Id being displayed but on
attempting to add new records in the sub-form I received the following error
message:

"The linkmasterfields property setting has produced this error: the object
doesn't contain the automation object tblContacts".

Other messages are also displayed- "you cannot add or change a record
because a related record is required in table 'tblContacts' "

I cannot see what I have done wrong to cause this. I have a previously
developed database that uses similar methodology with forms and sub-forms
which works fine. There is no macro or VBA code associated with the above
form at this stage, this will be completed later as I need to include some
event procedures. I have checked the table relationships carefully,
including various combinations of join properties but still receive the error.

Anyone that has had similar problems and has been able to resolve these
please advise what I need to do to fix the problem.

 

[Bob Larson]

Peter:

Your Master/Child links for the subform container control (control which
houses the subform on the main form) should just have this in both:
Contact_ID

don't use [Contact_ID]
or
[Contacts]![Contact_ID] in the master and
[ServiceDetails]![Contact_ID] in the child.

Those are common mistakes. Also, your Contact_ID field needs to be in the
recordsource of both the main form and the subform.

--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

 

[Peter]

Bob

Thank you for providing the response. Your suggestion worked.

In the master link I had "tblContacts.Contact_ID" and the child link had
just "Contact_ID". These were both inserted automtaically, I think, when the
sub-form container control was created on the main form, or I may have
entered these from a drop-down list displayed in the dialog as part of the
process of creating the container control. Just removing the "tblContacts."
prefix from the field did the trick.
These fields were in the recordsource for both the main form and sub-form.

I am a little puzzled by the error message as it implied that when adding a
new record to the sub-form, the Contact_ID in the master link was trying to
be updated when in fact it is placing just a new occurrence of the Contact_ID
value in the foreign key field in the child.
--
Peter WA

Peter:

Your Master/Child links for the subform container control (control which
houses the subform on the main form) should just have this in both:
Contact_ID

don't use [Contact_ID]
or
[Contacts]![Contact_ID] in the master and
[ServiceDetails]![Contact_ID] in the child.

Those are common mistakes. Also, your Contact_ID field needs to be in the
recordsource of both the main form and the subform.

--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

I have a contacts database developed in Access 2002 with a one to many
relationship between a main contacts table and a another table containing
service records. These tables are conatined in a back-end data base. A
simplified overview of the tables:

CONTACTS
Contact_ID (PK)
Category Code
name
address

SERVICEDETAILS (can have one or more entries per contact)
Contact_ID (FK)
start-date
end-date

The service details table is only populated with records relating to
contacts from the main table that have a specific category code (say code
X).

I can open the main contacts table in data sheet view then clicking on the
+
sign for a row I can select a sub-data sheet corresponding to the "many"
service details table and can display the records from this table and even
add new records. I can also filter the contacts table records to only
display those relating to category code X and still add records to the
sub-data sheet.

I am trying to achieve the above via a form which contains the main
contact
details with an embedded sub-form to display the "many" service details.
The
sub-form has had the correct master-child links established. The main form
has as its record source a query which includes all the main fields from
the
contacts table but filtered to only include the records relating to
Category
X as indicated above.

The form will display the contact records required and the corresponding
service records in the sub-form for the Contact_Id being displayed but on
attempting to add new records in the sub-form I received the following
error
message:

"The linkmasterfields property setting has produced this error: the object
doesn't contain the automation object tblContacts".

Other messages are also displayed- "you cannot add or change a record
because a related record is required in table 'tblContacts' "

I cannot see what I have done wrong to cause this. I have a previously
developed database that uses similar methodology with forms and sub-forms
which works fine. There is no macro or VBA code associated with the above
form at this stage, this will be completed later as I need to include some
event procedures. I have checked the table relationships carefully,
including various combinations of join properties but still receive the
error.

Anyone that has had similar problems and has been able to resolve these
please advise what I need to do to fix the problem.