Solved

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

Posted on 2016-08-26
17
64 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
  • 8
  • 5
  • 4
17 Comments
 
LVL 49

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 35

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 49

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 49

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 35

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 35

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 35

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 49

Expert Comment

by:Gustav Brock
ID: 41771986
You are welcome!

/gustav
0
 
LVL 35

Expert Comment

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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

786 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