DAO & table relationships 
Author Message
 DAO & table relationships

I'm having a strange problem....

I have a table that contains client info and another table that contains
"coverage" information.  I setup a relationship (one to many) between the two.
 The "coverage" table contains the foreign key.  

I'm using DAO, for several different reasons.

After creating a client record, I can modify the record and save the changes
using "RsBas.Update".  However, if a "coverage" record exists for that client
and I try to change client information I get an error "Runtime Error 3200 - The
Record cannot be deleted or changed because table "tblCoverage" contains
related records."

Any ideas anyone??

Thanks in advance,
Andrew



Sun, 18 Nov 2001 03:00:00 GMT  
 DAO & table relationships
Hi, Andrew-
I was able to overcome similar difficulty by setting the relationship "Cascade
Update" and/or "Cascade Delete" properties to the tables.
hth
Good Luck!
Chuck
Quote:

> I'm having a strange problem....

> I have a table that contains client info and another table that contains
> "coverage" information.  I setup a relationship (one to many) between the two.
>  The "coverage" table contains the foreign key.

> I'm using DAO, for several different reasons.

> After creating a client record, I can modify the record and save the changes
> using "RsBas.Update".  However, if a "coverage" record exists for that client
> and I try to change client information I get an error "Runtime Error 3200 - The
> Record cannot be deleted or changed because table "tblCoverage" contains
> related records."

> Any ideas anyone??

> Thanks in advance,
> Andrew



Sun, 18 Nov 2001 03:00:00 GMT  
 DAO & table relationships

Quote:

>I have a table that contains client info and another table that contains
>"coverage" information.  I setup a relationship (one to many) between the two.
> The "coverage" table contains the foreign key.  

What are you using for a key between the two tables?  An autonumber
field or the client name?

Quote:
>After creating a client record, I can modify the record and save the changes
>using "RsBas.Update".  However, if a "coverage" record exists for that client
>and I try to change client information I get an error "Runtime Error 3200 - The
>Record cannot be deleted or changed because table "tblCoverage" contains
>related records."

Would this happy only when you go to correct or otherwise change a
client name?

If so the problem is that your using client name as a key into both
tables.  You should be using an autonumber field in both tables.  With
a foreign key in the tblCoverage pointing to the Client ID number.

However if you are indeed using ID numbers then I don't know.

Double check your relationships.  Maybe you have something wierd
there.  Also click on the Show All Relationships button as maybe some
relationships are "hidden"

Tony
----
Message posted to newsgroup and, if appropriate, emailed.
Tony Toews, Independent Computer Consultant
Microsoft Access Links, Hints, Tips & Accounting Systems at
   http://www.granite.ab.ca/accsmstr.htm
VolStar http://www.volstar.com Manage hundreds or
   thousands of volunteers for special events.



Mon, 19 Nov 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. DAO 3.5 Adding Relationships to tables

2. Tables & Relationships

3. Combining Access Tables in VB6 & DAO

4. Using VB6 and DAO to delete all relationships in an Access database

5. creating relationships in SQL DDL, DAO 3.6

6. Delete Relationship - DAO - A2k

7. A friendly challenge -- creating relationships programatically (dao)

8. Access 97 VBA/DAO for relationships

9. Repost: Using VB6 and DAO to delete all relationships in an Access database

10. DAO 3.6 with Access Database 2000 with relationships defined

11. DAO Relationships - Error 3011

12. DAO 3.5 & DAO 3.51 work differently

 

 
Powered by phpBB® Forum Software