Delete SQL not working?

Happy YN

Registered User.
Local time
Today, 21:40
Joined
Jan 27, 2002
Messages
425
Why does this sql not work?
It brings up the records which satisfy the criteria if one clicks on the view button. Yet does not delete the records! No warning signs appear at all

DELETE pupils.*, pupils.[Pupil ID]
FROM pupils
WHERE (((pupils.[Pupil ID])=[Forms]![Parents]![cmboPupilsDelete]));
Any ideas?
Thanks
 
You have to run it, not just view it. Have you done that?
 
of course!
I was just saying that in the view it shows the relevant records so I know that the criteria is working correctly
Thanks
 
No, never seen or heard that Access can view a record with a delete query, but that would be nice.

What version are you using, A2007 or beta Version 13?

of course!
I was just saying that in the view it shows the relevant records so I know that the criteria is working correctly
Thanks
 
Perhaps I have not explained myself clearly.
All I meant was when designing a query such as a delete query using the design grid, one can click a button on the top left which is called view and will see the list of records satisfying the criteria of the query. This is not the 'run' button mentioned earlier.
What I am asking is Why do the records appear correctly when viewed yet when I run the delete query they do not get deleted.
thanks.
I am using access 2003
 
How are you running it? Do you get any messages?
 
Ah! i see what you mean now.

It is possible that in your Access setup option, you may have uncheck the confirm message else you may need to compact and repair your database.

In your previous SQL......
DELETE pupils.*, pupils.[Pupil ID]
FROM pupils
WHERE (((pupils.[Pupil ID])=[Forms]![Parents]![cmboPupilsDelete]));

It seems you have a inproper SQl syntax.

Try this...

DELETE pupils.[Pupil ID]
FROM pupils
WHERE (((pupils.[Pupil ID])=[Forms]![Parents]![cmboPupilsDelete]));

Try to use the form reference like "[Forms]![YourFormName]![cmboPupilsDelete]" if "Parents" does not not work. Parent is a reserve name in access, not sure about "Parents".

Perhaps I have not explained myself clearly.
All I meant was when designing a query such as a delete query using the design grid, one can click a button on the top left which is called view and will see the list of records satisfying the criteria of the query. This is not the 'run' button mentioned earlier.
What I am asking is Why do the records appear correctly when viewed yet when I run the delete query they do not get deleted.
thanks.
I am using access 2003
 
Last edited:
I have tried running it both in vb and as a query. I get no messages.
I have already tried both sql structures mentioned. The syntax is fine. The proof is that id does find the relevant records.
I doubt parents is the problem since I have been using this for almost 7 years now! The db has been compacted and repaired!
Thanks for all the interest and help
 
When you said can’t delete, is the table link to another table? Like cascade deletes…. this will prevent you from deleting this record. You’ll have to delete it thru your primary table. (Enforce Referential Integerity)

What was the option setting on the “Action queries”?

I have tried running it both in vb and as a query. I get no messages.
I have already tried both sql structures mentioned. The syntax is fine. The proof is that id does find the relevant records.
I doubt parents is the problem since I have been using this for almost 7 years now! The db has been compacted and repaired!
Thanks for all the interest and help
 
Yes!!!!!!
It is linked to another table which has cascade deletes but I didn't think this would prevent deletes on the child table . I thought deleting a parent would automatically cascade to delete their children but one could still delete a child independantly?
Will play around
Thanks
 
Anytime an SQL statement fails, instead of running it, I use the debug.print command which sends it to the immediate window where you can see exactly what is being interpreted.
Have you ever tried this?
 

Users who are viewing this thread

Back
Top Bottom