related record?

T

Tom O

how do i deal with the message:-


"you can add or change a record cause a related record is required in tbale
company"

i'm a novice by the way;)
 
J

John Vinson

On Sat, 9 Apr 2005 06:23:03 -0700, "Tom O" <Tom
how do i deal with the message:-


"you can add or change a record cause a related record is required in tbale
company"

i'm a novice by the way;)
Click to expand...

By ensuring that there is a company in the Company table which matches
the data that you're trying to enter. It seems you're entering data
into a table, and trying to store some value into the foreign key
field linked to the Companies table; the value you're storing doesn't
correspond to any existing Company, so it won't let you add the
"orphan" record.

One way to do this (guessing wildly here since I don't even know what
you're trying to enter) is to use a Form based on the Company table,
with a Subform based on this table you're trying to load; use the
Company ID as the master/child link field.

John W. Vinson[MVP]
 
T

Tom O

is there a way of findin out how find the field thats the error message is on
about so i can easly refind it .

be the way thr is only 4 fields in the company table and primary key in
company has a forgien key in the contact table thats what i tihnk the related
record is the company id forgien key!
 
J

John Vinson

is there a way of findin out how find the field thats the error message is on
about so i can easly refind it .
Click to expand...

I'm sure there is a way, but I cannot suggest what it might be: I have
no idea how your tables are structured, how they are related, what's
in the contact table, or how you are attempting to add this record.
Typing in a datasheet? Using a Form with a Subform? Running an Append
query? Some other way?
be the way thr is only 4 fields in the company table and primary key in
company has a forgien key in the contact table thats what i tihnk the related
record is the company id forgien key!
Click to expand...

The error message says exactly that: you're trying to add a record
(somehow, I don't know how) to the Contacts table; this record
contains a Company ID field in the foreign key, and that value does
not exist in the company table.

Again... note that this particular error will not happen if you use a
Form (based on Companies) and a Subform (based on Contacts) with the
company ID as the master/child link field.

John W. Vinson[MVP]
 
T

Tom O

i'm tryin to enter a record into form that i created and then linked the
record source with my query and then linked up all the fields on the form to
the right ones

company tables has these fields:-

company ID (PK)
company name
company email
company website

contact tables has lots fields including

contact ID (PK)
company ID (FK)


the relationship in the query shows thats


the contact tbl and company tbl have one to many relationship

1 coming from company ID in company table

and many to comapny ID in contact tbl

if yu want me to send yu database i can and if yu what any printscreens i
can !
 
J

John Vinson

i'm tryin to enter a record into form that i created and then linked the
record source with my query and then linked up all the fields on the form to
the right ones
Click to expand...

What's "my query"? Please post the SQL. I would suggest that the main
form should be based on a query containing only the Company table,
probably sorted by Company Name; the Subform should be based on the
Contact table, using the CompanyID as the master and child link field.

I'm guessing that you have a form based on a query joining the two
tables. This can be made to work, with some difficulty, but the
form/subform is much simpler to implement.
if yu want me to send yu database i can and if yu what any printscreens i
can !
Click to expand...

Sorry... I'm willing to work with databases for my paying customers,
but that goes beyond what I'm willing to do as an unpaid volunteer.

John W. Vinson[MVP]
 
T

Tom O

SELECT Contact.[Contact ID], Contact.Role, Contact.Title, Contact.Forename,
Contact.Surname, Contact.[Job Title], Contact.Notes, Contact.[Last Updated],
Contact.[Date Acquired], Contact.delete, Company.[Company ID],
Company.[Company Name], Company.[Company Website], Company.[Company Email],
Communications.extension, Communications.Fax, Communications.Email,
Telephone.[Number Type], Telephone.[Telephone No], Address.[Address Type],
Address.[Address 1], Address.[Address 2], Address.[Town/City],
Address.County, Address.Country, Address.Postcode
FROM (Company INNER JOIN Contact ON Company.[Company ID] = Contact.[Company
ID]) INNER JOIN (Communications INNER JOIN ((Address INNER JOIN
([address/contact/comm] INNER JOIN Telephone ON
[address/contact/comm].[Contact ID] = Telephone.[Telephone No]) ON
Address.[Address 1] = [address/contact/comm].[Address 1]) INNER JOIN
[contact/address] ON Address.[Address 1] = [contact/address].[Address 1]) ON
Communications.[Telephone No] = [address/contact/comm].[Telephone No]) ON
Contact.[Contact ID] = [contact/address].[Contact ID];

hes the SQL i think the problem is the relationship with the tables in the
query becuase in the query yu know when yu go to the data sheet view i dont
even get the blank first record on the data sheet view for the qry so i know
something is wrong with it

is ther a way i can send yu my relationships using this SQL stuff cause i
dont use SQL its too complecated;)
 
J

John Vinson

SELECT Contact.[Contact ID], Contact.Role, Contact.Title, Contact.Forename,
Contact.Surname, Contact.[Job Title], Contact.Notes, Contact.[Last Updated],
Contact.[Date Acquired], Contact.delete, Company.[Company ID],
Company.[Company Name], Company.[Company Website], Company.[Company Email],
Communications.extension, Communications.Fax, Communications.Email,
Telephone.[Number Type], Telephone.[Telephone No], Address.[Address Type],
Address.[Address 1], Address.[Address 2], Address.[Town/City],
Address.County, Address.Country, Address.Postcode
FROM (Company INNER JOIN Contact ON Company.[Company ID] = Contact.[Company
ID]) INNER JOIN (Communications INNER JOIN ((Address INNER JOIN
([address/contact/comm] INNER JOIN Telephone ON
[address/contact/comm].[Contact ID] = Telephone.[Telephone No]) ON
Address.[Address 1] = [address/contact/comm].[Address 1]) INNER JOIN
[contact/address] ON Address.[Address 1] = [contact/address].[Address 1]) ON
Communications.[Telephone No] = [address/contact/comm].[Telephone No]) ON
Contact.[Contact ID] = [contact/address].[Contact ID];

hes the SQL i think the problem is the relationship with the tables in the
query becuase in the query yu know when yu go to the data sheet view i dont
even get the blank first record on the data sheet view for the qry so i know
something is wrong with it
Click to expand...

The problem is that YOU ARE NOT LISTENING.

This Query will be *empty*. It will contain no records, because there
*is no data in one of the tables*. It will not be updateable because
you have three tables in the join.

This query WILL NOT WORK.

What *will* work is a Form with Subforms, as I've said a couple of
times now. Are you willing to try to use something which *will* work
rather than beating on something which won't?
is ther a way i can send yu my relationships using this SQL stuff cause i
dont use SQL its too complecated;)
Click to expand...

Certainly, if you don't mind my sending you a consulting contract.
I've got an 8 hour minimum and my rates aren't particularly cheap.


John W. Vinson[MVP]
 
Top