Cant delete records

[JohnB]

Hi.

Perhaps a simple one but this is driving me mad. For some reason, I cant
delete records shown in a form, unless I set up the relationships in the
forms record source SQL in a certain way. But that way stops empty subform
records showing. Can anyone help? Im using Access 2002 on XP.

I have three tables, tblMentors, tblSchools and tblMentorSchools. tblMentors
has MentorID as the key, tblSchools has SchoolID as the key and
tblMentorSchools has MentorSchoolID as the key and both MentorID and SchoolID
as number fields. I have all three tables in the record source SQL for the
form frmMentors, which has a subform showing the associated School records.

I find that, if I set up the table relationships in the record source SQL so
that I include all records in tblMentors and only those in tblMentorSchool
etc, then I cant delete any mentor records. It goes through the process of
deleting, but when I close the form and go back in, the record is still
there. However if I set the relationship to include all records from
tblMentorSchool and only those from tblMentors etc, I can delete mentor
records. The problem with setting the SQL in the second way is that I cant
see any mentor records that dont have an associated School record in the
subform. I can, however, delete mentor records directly in tblMentors.

Can anyone see what Im doing wrong here, or is this normal? Does the fact
that Ive removed the Default Value = 0 setting for the MentorID and SchoolID
fields in tblMentorSchools have anything to do with it.

Thanks for any help. JohnB

 

[JohnB]

Hi again.

Just had a thought - why am I creating such a complicated record source for
the main form? Why not just have tblMentors as the record source? Ill try
that tomorrow, so please hold any replies.

Thanks, JohnB

 

[Roger Carlson]

If you are using a Form/Subform set up, you should not have a Join of all
three tables as the record source for the main form. The main form
(frmMentors) should be based on tblMentors and the subform should be based
on a Join of tblSchools and tblMentorSchools. The Link Parent/Child forms
properties should be on MentorID.

On my website (www.rogersaccesslibrary.com) is a small sample database
called "ImplementingM2MRelationship.mdb" which illustrates this.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[JohnB]

Thank you Roger.

Looks like Im over complicating things - youve explained what I should be
doing very concisely. Thanks also for the link - Ill certainly have a look.
JohnB