Join/delete problem

[Guest]

I have a join problem I can't figure out. I have two back-end databases
joined on their control numbers through a front-end query (db2 on the left,
db1 on the right). No other relationships exist between these tables. The
control number for db1 is a primary key; the control number for db2 is not.
Db2 is a very small subset of db1, and the form used to fill in db2 displays
some relevant info from db1. Users have to manually enter db2 control
numbers; there's no error checking in place, but records should include only
numbers that are already in db1. The db2 control number is currently set to
index/duplicates okay, because it was originally anticipated that there would
be duplicate records in db2 for each record in db1. Now I've been asked to
prevent duplicates. I thought I could simply set the db2 control number to
index/no duplicates. But when I do that, deleting a record from db2 also
deletes the record from db1 that has the same control number, which I don't
want it to do. Is it the query relationship that's causing this? I think
I'm missing something really obvious.

 

[John Vinson]

But when I do that, deleting a record from db2 also
deletes the record from db1 that has the same control number, which I don't
want it to do. Is it the query relationship that's causing this?

Check the relationship in the Relationships window: click the line and
view its properties. If the "Cascade Deletes" checkbox is checked,
uncheck it.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John - thanks, but this is why I'm confused. There are no properties in the
relationships windows. These tables are joined *only* through a query, and
referential integrity isn't a choice there.

 

[John Vinson]

John - thanks, but this is why I'm confused. There are no properties in the
relationships windows. These tables are joined *only* through a query, and
referential integrity isn't a choice there.

Are you sure the record is in fact being deleted from both tables? If
it exists only in one of the two tables (either one), it will NOT show
up in a Query joining the two tables by the default Inner Join.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Yes. I've manually checked both tables, and when the delete is done in the
first, the corresponding record is also deleted from the second.

 

[John Vinson]

But when I do that, deleting a record from db2 also
deletes the record from db1 that has the same control number, which I don't
want it to do. Is it the query relationship that's causing this? I think
I'm missing something really obvious.

If the FOrm is based on a query with a one-to-one relationship, it may
be deleting both records. Is that the case? It might be safer to have
a Form with a Subform, or to do the deletion in code.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

It is a query with a one-to-one. This is the answer. Thanks *very* much.