"Cannot delete from specified tables"

[celineusa]

Hello!


Here is part of my database:


tblStudents: StudentID
tblStudentsAndMajors: StudentID, MajorID
tblMajors: MajorID
tblMajorsAndClasses: MajorID, ClassID
tblClasses: ClassID
tblStudentsAndClasses: StudentID, ClassID, QuarterTaken

In my form, when I remove a major from a student, I want it to delete
two things:
- in tblStudentsAndMajors: remove the record where StudentID (11 as a
test) and MajorID (2 as a test) match StudentID and MajorID from the
form
- in tblStudensAndClasses: remove the records where StudentID and
MajorID match StudentID and MajorID from the form, and where
QuarterTaken is false


DELETE [Students And Majors].*, [Students And Classes].*
FROM (Majors
INNER JOIN ((Classes
INNER JOIN [Classes And Majors]
ON Classes.ClassID = [Classes And Majors].ClassID)
INNER JOIN [Students And Classes]
ON Classes.ClassID = [Students And Classes].ClassID)
ON Majors.MajorID = [Classes And Majors].MajorID)
INNER JOIN [Students And Majors]
ON Majors.MajorID = [Students And Majors].MajorID
WHERE ((([Students And Classes].StudentID)=11)
AND (([Students And Classes].Taken)=False)
AND (([Students And Majors].StudentID)=11)
AND (([Students And Majors].MajorID)=2));

When I do that query, the datasheet view gives me the results I want
(all the line to delete), but if I run the query, I get "Cannot delete
from specified tables".

Does anyone has an idea?

Thanks,
Celine

 

[Amy Blankenship]

What you probably actually want to do is just make the major inactive (add
an IsActive field) and then add a new record for the new major.

Rather than adding fields for classes the student hasn't taken yet, you
should probably just display the classes they *should* take as a view in a
query. Only add the class when the class is actually taken or at least
scheduled.

So then when they switch majors, the query will show the classes they
*should* take for the new major, and the old major's records, since they
never actually existed, don't show up. If you want to show both classes the
student *has* taken and classes they *should* take, you'd want to use a
union query of the should take query and tblStudentsAndClasses.

HTH;

Amy

 

[celineusa]

Thank you, I am going to try that, it sound like a really good idea!

 

[John Spencer]

You cannot delete from two tables in one query.

You will have to split the delete into two separate queries.

 

[Adam Turner via AccessMonster.com]

Hello!

Here is part of my database:

tblStudents: StudentID
tblStudentsAndMajors: StudentID, MajorID
tblMajors: MajorID
tblMajorsAndClasses: MajorID, ClassID
tblClasses: ClassID
tblStudentsAndClasses: StudentID, ClassID, QuarterTaken

In my form, when I remove a major from a student, I want it to delete
two things:
- in tblStudentsAndMajors: remove the record where StudentID (11 as a
test) and MajorID (2 as a test) match StudentID and MajorID from the
form
- in tblStudensAndClasses: remove the records where StudentID and
MajorID match StudentID and MajorID from the form, and where
QuarterTaken is false

DELETE [Students And Majors].*, [Students And Classes].*
FROM (Majors
INNER JOIN ((Classes
INNER JOIN [Classes And Majors]
ON Classes.ClassID = [Classes And Majors].ClassID)
INNER JOIN [Students And Classes]
ON Classes.ClassID = [Students And Classes].ClassID)
ON Majors.MajorID = [Classes And Majors].MajorID)
INNER JOIN [Students And Majors]
ON Majors.MajorID = [Students And Majors].MajorID
WHERE ((([Students And Classes].StudentID)=11)
AND (([Students And Classes].Taken)=False)
AND (([Students And Majors].StudentID)=11)
AND (([Students And Majors].MajorID)=2));

When I do that query, the datasheet view gives me the results I want
(all the line to delete), but if I run the query, I get "Cannot delete
from specified tables".

Does anyone has an idea?

Thanks,
Celine

You can't delete from a joined table...