Join/delete problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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?
Click to expand...

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
 
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 - 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.
Click to expand...

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
 
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.
 
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.
Click to expand...

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
 
Back
Top