"Cannot delete from specified tables"

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

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

wrote in message
oups.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

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

You cannot delete from two tables in one query.

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


wrote in message
oups.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

wrote:
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...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200511/1