Relationship Link

[Gary T.]

I have 3 tables:

tbl_customer
-CustID
-fkeyMarketingID
-CompName
-CompAddr
-ContactName
-ContactAddr
-Ph
-Fax

tbl_Marketing
-MarketingID
-fkeyMarketItemID
-MarketQty
-MarketDateSent

tbl_MarketDetails
-MarketItemName

The relationship looks like this:
tbl_MarketDetails (one) ------ (many) tbl_Marketing , (enforce
referential integrity - ticked ) , (join type - rows in both tables equal)

tbl_Marketing (one) ------ (many) tbl_customer , (enforce referential
integrity - ticked ) , (join type - rows in both tables equal)

Problem: I created a query with all the 3 table fields in this query where a
form (frm_newcustomer) is linked. It does not work in terms of allowing me
to save new entries in the form. I get the following message:
"You cannot add or change a record because a related record is required in
table 'tbl_Marketing' "
I don't understand - every field from the 3 tables is accounted for in the
query.

[Allen Browne]

This error can be triggered by the presence of a Default Value in the fields
of the lookup tables, or a Default Value in the controls that are bound to
the lookup tables.

Although you are not trying to add records to those tables, Access (wrongly)
applies the Default Value, and then complains that it can't add the record.
Removing the Default Value solves the issue.

There are other possible causes, but that one is less than obvious to track
down.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gary T." <g4****@hotmail.com> wrote in message
news:43***********************@news.optusnet.com.a u...

I have 3 tables:

tbl_customer
-CustID
-fkeyMarketingID
-CompName
-CompAddr
-ContactName
-ContactAddr
-Ph
-Fax

tbl_Marketing
-MarketingID
-fkeyMarketItemID
-MarketQty
-MarketDateSent

tbl_MarketDetails
-MarketItemName

The relationship looks like this:
tbl_MarketDetails (one) ------ (many) tbl_Marketing , (enforce
referential integrity - ticked ) , (join type - rows in both tables equal)

tbl_Marketing (one) ------ (many) tbl_customer , (enforce referential
integrity - ticked ) , (join type - rows in both tables equal)

Problem: I created a query with all the 3 table fields in this query where
a
form (frm_newcustomer) is linked. It does not work in terms of allowing me
to save new entries in the form. I get the following message:
"You cannot add or change a record because a related record is required in
table 'tbl_Marketing' "
I don't understand - every field from the 3 tables is accounted for in the
query.

[Wayne Morgan]

To add a record to tbl_Customer you need a record in tbl_Marketing that has
a value in the field joining the two tables that matches the value you are
entering in the joined field (on the tbl_Customer side of the join) for the
new record in tbl_Customer.

--
Wayne Morgan
MS Access MVP
"Gary T." <g4****@hotmail.com> wrote in message
news:43***********************@news.optusnet.com.a u...

I have 3 tables:

tbl_customer
-CustID
-fkeyMarketingID
-CompName
-CompAddr
-ContactName
-ContactAddr
-Ph
-Fax

tbl_Marketing
-MarketingID
-fkeyMarketItemID
-MarketQty
-MarketDateSent

tbl_MarketDetails
-MarketItemName

The relationship looks like this:
tbl_MarketDetails (one) ------ (many) tbl_Marketing , (enforce
referential integrity - ticked ) , (join type - rows in both tables equal)

tbl_Marketing (one) ------ (many) tbl_customer , (enforce referential
integrity - ticked ) , (join type - rows in both tables equal)

Problem: I created a query with all the 3 table fields in this query where
a
form (frm_newcustomer) is linked. It does not work in terms of allowing me
to save new entries in the form. I get the following message:
"You cannot add or change a record because a related record is required in
table 'tbl_Marketing' "
I don't understand - every field from the 3 tables is accounted for in the
query.

[Gt394]

It's still not working.
Basically trying to use the frm_NewCustomer to enter info on new customers.
Customer A could have the following record of sent material (a subform of
tbl_marketing).
MarketItemName MarketQty MarketDateSent
BrochureA 9 9/9/05
SampleC 6 10/6/05
BrochureE 5 10/6/05

I have now simply placed all the marketing in one table:

tbl_Marketing
-MarketingID
-MarketItemName
-MarketQty
-MarketDateSent

Somehow this won't work as I think i still need to separate the
MarketItemname in its own table to
allow me to have a record of different materials sent at different dates per
customer.
I did this before as mentioned in my last post but no success.


"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:R8**************@newssvr12.news.prodigy.com.. .

To add a record to tbl_Customer you need a record in tbl_Marketing that has a value in the field joining the two tables that matches the value you are
entering in the joined field (on the tbl_Customer side of the join) for the new record in tbl_Customer.

--
Wayne Morgan
MS Access MVP
"Gary T." <g4****@hotmail.com> wrote in message
news:43***********************@news.optusnet.com.a u...

I have 3 tables:

tbl_customer
-CustID
-fkeyMarketingID
-CompName
-CompAddr
-ContactName
-ContactAddr
-Ph
-Fax

tbl_Marketing
-MarketingID
-fkeyMarketItemID
-MarketQty
-MarketDateSent

tbl_MarketDetails
-MarketItemName

The relationship looks like this:
tbl_MarketDetails (one) ------ (many) tbl_Marketing , (enforce
referential integrity - ticked ) , (join type - rows in both tables equal)
tbl_Marketing (one) ------ (many) tbl_customer , (enforce referential integrity - ticked ) , (join type - rows in both tables equal)

Problem: I created a query with all the 3 table fields in this query where a
form (frm_newcustomer) is linked. It does not work in terms of allowing me to save new entries in the form. I get the following message:
"You cannot add or change a record because a related record is required in table 'tbl_Marketing' "
I don't understand - every field from the 3 tables is accounted for in the query.

[Wayne Morgan]

What are the names of each table, the fields in those tables, and which
fields the tables are linked on?

--
Wayne Morgan
MS Access MVP
"Gt394" <g4****@hotmail.com> wrote in message
news:43***********************@news.optusnet.com.a u...

It's still not working.
Basically trying to use the frm_NewCustomer to enter info on new
customers.
Customer A could have the following record of sent material (a subform of
tbl_marketing).
MarketItemName MarketQty MarketDateSent
BrochureA 9 9/9/05
SampleC 6 10/6/05
BrochureE 5 10/6/05

I have now simply placed all the marketing in one table:

tbl_Marketing
-MarketingID
-MarketItemName
-MarketQty
-MarketDateSent

Somehow this won't work as I think i still need to separate the
MarketItemname in its own table to
allow me to have a record of different materials sent at different dates
per
customer.
I did this before as mentioned in my last post but no success.


"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:R8**************@newssvr12.news.prodigy.com.. .

To add a record to tbl_Customer you need a record in tbl_Marketing that

has

a value in the field joining the two tables that matches the value you
are
entering in the joined field (on the tbl_Customer side of the join) for

the

new record in tbl_Customer.

--
Wayne Morgan
MS Access MVP
"Gary T." <g4****@hotmail.com> wrote in message
news:43***********************@news.optusnet.com.a u...

>I have 3 tables:
>
> tbl_customer
> -CustID
> -fkeyMarketingID
> -CompName
> -CompAddr
> -ContactName
> -ContactAddr
> -Ph
> -Fax
>
> tbl_Marketing
> -MarketingID
> -fkeyMarketItemID
> -MarketQty
> -MarketDateSent
>
> tbl_MarketDetails
> -MarketItemName
>
> The relationship looks like this:
> tbl_MarketDetails (one) ------ (many) tbl_Marketing , (enforce
> referential integrity - ticked ) , (join type - rows in both tables equal) >
> tbl_Marketing (one) ------ (many) tbl_customer , (enforce referential > integrity - ticked ) , (join type - rows in both tables equal)
>
> Problem: I created a query with all the 3 table fields in this query where > a
> form (frm_newcustomer) is linked. It does not work in terms of allowing me > to save new entries in the form. I get the following message:
> "You cannot add or change a record because a related record is required in > table 'tbl_Marketing' "
> I don't understand - every field from the 3 tables is accounted for in the > query.
>
>

[Gt394]

See my first post in the thread discussions. All the tables/fields are
listed and the join relationships described.
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:yU*****************@newssvr14.news.prodigy.co m...

What are the names of each table, the fields in those tables, and which
fields the tables are linked on?

--
Wayne Morgan
MS Access MVP
"Gt394" <g4****@hotmail.com> wrote in message
news:43***********************@news.optusnet.com.a u...

It's still not working.
Basically trying to use the frm_NewCustomer to enter info on new
customers.
Customer A could have the following record of sent material (a subform of tbl_marketing).
MarketItemName MarketQty MarketDateSent
BrochureA 9 9/9/05
SampleC 6 10/6/05
BrochureE 5 10/6/05

I have now simply placed all the marketing in one table:

tbl_Marketing
-MarketingID
-MarketItemName
-MarketQty
-MarketDateSent

Somehow this won't work as I think i still need to separate the
MarketItemname in its own table to
allow me to have a record of different materials sent at different dates
per
customer.
I did this before as mentioned in my last post but no success.


"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:R8**************@newssvr12.news.prodigy.com.. .

To add a record to tbl_Customer you need a record in tbl_Marketing that

has

a value in the field joining the two tables that matches the value you
are
entering in the joined field (on the tbl_Customer side of the join) for

the

new record in tbl_Customer.

--
Wayne Morgan
MS Access MVP
"Gary T." <g4****@hotmail.com> wrote in message
news:43***********************@news.optusnet.com.a u...
>I have 3 tables:
>
> tbl_customer
> -CustID
> -fkeyMarketingID
> -CompName
> -CompAddr
> -ContactName
> -ContactAddr
> -Ph
> -Fax
>
> tbl_Marketing
> -MarketingID
> -fkeyMarketItemID
> -MarketQty
> -MarketDateSent
>
> tbl_MarketDetails
> -MarketItemName
>
> The relationship looks like this:
> tbl_MarketDetails (one) ------ (many) tbl_Marketing , (enforce
> referential integrity - ticked ) , (join type - rows in both tables

equal)

>
> tbl_Marketing (one) ------ (many) tbl_customer , (enforce

referential

> integrity - ticked ) , (join type - rows in both tables equal)
>
> Problem: I created a query with all the 3 table fields in this query

where

> a
> form (frm_newcustomer) is linked. It does not work in terms of

allowing me

> to save new entries in the form. I get the following message:
> "You cannot add or change a record because a related record is

required in

> table 'tbl_Marketing' "
> I don't understand - every field from the 3 tables is accounted for

in the

> query.
>
>

[Wayne Morgan]

Ok, so this "subform" you are entering the new customer in is based on the
tbl_Customer table? Also, in your original message you list tbl_Marketing
with a fkeyMarketItemID field, but you don't show a table where this is the
primary key field.

When entering a new customer, the value for fkeyMarketingID that is entered
(or not entered, i.e. Null) will have to exist in tbl_Marketing in the
MarketingID field.

Also, you say that this "subform" is "a subform of tbl_marketing". Are you
doing this with a form or a sub datasheet directly in the table? Have you
set the Parent/Child link fields for the form/subform setup?

The advantage of separating the MarketItemName into a separate table would
be to use a combo box on the form to "lookup" the name of the item instead
of having to type it in all of the time. You would then need a form to keep
this table up to date. Another option would be to base the Row Source of the
combo box on this field in the tbl_Marketing table and just add a new item
when needed. You would query the field so that duplicates would be hidden.
The combo box will then show you each item that already exists and you would
add new items when needed just by typing them in if you didn't find them in
the list.

Example Row Source:
SELECT DISTINCT MarketItemName FROM tbl_Marketing ORDER BY MarketItemName;

--
Wayne Morgan
MS Access MVP
"Gt394" <g4****@hotmail.com> wrote in message
news:43**********************@news.optusnet.com.au ...

See my first post in the thread discussions. All the tables/fields are
listed and the join relationships described.