Solved

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

Posted on 2016-08-26
17
32 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 34

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
 
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 34

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 34

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 34

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 34

Expert Comment

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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now