Solved

Ms Access 2010 "Specify the table containing the records you want to delete"

Posted on 2016-08-26
17
212 Views
Last Modified: 2016-08-26
Dear Experts,

I am trying to setup a delete query and run it from an onClick button event but when I test the query I see records I want to delete but when I try to 'run' the query I get the messge "Specify the table containing the records you want to delete"

What am I doing wrong here? Please see attachement for tables and relationships.

Thanks!
screenshots.pdf
0
Comment
Question by:shogun5
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
17 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41771644
Remove tblClass and use as filter: Where tblStudent.StudentClassId = 14
Then use Delete from tblStudentClassBehaviors.

/gustav
0
 

Author Comment

by:shogun5
ID: 41771785
When I do this, I get the same error.

screenshot
Here is the sql:
DELETE tblStudentBehaviors.ID
FROM (tblClass INNER JOIN tblStudent ON tblClass.ClassID = tblStudent.studentClassID) INNER JOIN tblStudentBehaviors ON tblStudent.studentID = tblStudentBehaviors.stdID
WHERE (([tblStudent].[StudentClassId]=14));

Open in new window


What am I doing wrong...

Here is the code that I am using in the 'onclick' event and not sure if this is correct. I seem to get the same error.
        
           DoCmd.SetWarnings False
           CurrentDb.Execute "DELETE * FROM " & "tblStudentBehaviors INNER JOIN tblStudent ON tblClass.ClassID = tblStudent.studentClassID INNER JOIN tblStudentBehaviors ON tblStudent.studentID = tblStudentBehaviors.stdID" & " Where tblClass.ClassID = " & "Forms!frmClasses![ClassID]"
           DoCmd.SetWarnings True

    

Open in new window

0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41771832
There is no reason to include the other two tables in the delete query so remove them.  Leave only the behaviors table.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41771835
You didn't remove tblClass.

/gustav
0
 

Author Comment

by:shogun5
ID: 41771860
Okay...

sorry...so like this?
screenshot
I still get the same error message. And since I need to delete only the records that match the appropriate  studentClassID I think I need to keep the student table in there or else I'll delete  all the behavior records and that's not what I want to do.

thoughts?
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 300 total points
ID: 41771879
Yes, these are nasty. Try this variation:
DELETE * 
FROM tblStudentBehaviors
WHERE stdID IN 
    (Select StudentID FROM tblStudent WHERE tblStudent.StudentClassId=14);

Open in new window

/gustav
0
 

Author Comment

by:shogun5
ID: 41771898
Yes! That worked! with hard coding the class ID of "14". Now I need it to read off a field value but I keep getting the following error message.

screenshot
 DoCmd.SetWarnings False
           CurrentDb.Execute "DELETE * FROM tblStudentBehaviors WHERE stdID IN (Select StudentID FROM tblStudent WHERE tblStudent.StudentClassId = '" & Me.ClassID & "'"
           DoCmd.SetWarnings True

Open in new window


StudentClassID is a 'number' value.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41771899
Sorry, I didn't notice that the criteria was on the middle table.  What are you trying to delete?  In a delete query, you can ONLY delete rows from the lowest level table.  In your query that means you can only delete behaviors.  You cannot delete students or classes.

Are you trying to delete the Class record since that is the criteria you are using?  If so, you need to use a query that includes only tblClass.  You would have to have implemented cascade delete to delete the student records or you would have to delete the student records first and then delete the class record.

There also seems to be a missing table in your schema.  There needs to be a junction table between class and student to specify which students are in which classes.  Your schema allows a student to be in only one class.  Maybe you mean Grade rather than class.  Class sounds like English, Algebra, Gym.  But Grade would be 1st, 2nd, 3rd.  You would still use a junction table even though a student can be in only one grade at a time since students progress over time from one grade to another and you would want a history of which grades he attended.

With the junction table, you would delete the junction record to remove a student from a class.  You wouldn't delete the student record or the class record.
0
 

Author Comment

by:shogun5
ID: 41771912
Pat,

Right...the way I have set up this database Elementary students are only in one class not the other way around. So Class one-to-many Students one-to-many behaviors.

The code that gustav provided works like a charm with a hard coded value. I just need now to get the syntax to work using a value in a field as noted above.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41771921
ClassID is numeric and so should not be enclosed in quotes.
0
 

Author Comment

by:shogun5
ID: 41771929
Okay...any ideas...'cause that didn't work either.

screenshot
 DoCmd.SetWarnings False
           CurrentDb.Execute "DELETE * FROM tblStudentBehaviors WHERE stdID IN (Select StudentID FROM tblStudent WHERE tblStudent.StudentClassId = '" & Me.ClassID & "'"
           DoCmd.SetWarnings True

Open in new window

0
 

Author Comment

by:shogun5
ID: 41771933
ooops...pasted the wrong syntax.

 DoCmd.SetWarnings False
           CurrentDb.Execute "DELETE * FROM tblStudentBehaviors WHERE stdID IN (Select StudentID FROM tblStudent WHERE tblStudent.StudentClassId = & Me.ClassID & "
           DoCmd.SetWarnings True

Open in new window

0
 

Author Comment

by:shogun5
ID: 41771934
Even tried this way...

 DoCmd.SetWarnings False
           CurrentDb.Execute "DELETE * FROM tblStudentBehaviors WHERE stdID IN (Select StudentID FROM tblStudent WHERE tblStudent.StudentClassId =   Me.ClassID  "
           DoCmd.SetWarnings True

Open in new window

0
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
ID: 41771968
When creating an SQL string, you need to understand what is the literal part of the string and what is the variable.

This should work:
 DoCmd.SetWarnings False
           CurrentDb.Execute "DELETE * FROM tblStudentBehaviors WHERE stdID IN (Select StudentID FROM tblStudent WHERE tblStudent.StudentClassId =  " & Me.ClassID & ")"
           DoCmd.SetWarnings True

Open in new window


I always create variables to hold these strings so I can easily print them out to the debug window to see what is wrong with the syntax.  If what I posted didn't work, please make a variable and print out the string after it is constructed and paste it here so we can see the problem.
0
 

Author Closing Comment

by:shogun5
ID: 41771982
Thanks! Okay....that did it! Gustav and Pat....thanks so much for your help. Learned a lot!
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41771986
You are welcome!

/gustav
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41772228
You're welcome:)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question