Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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

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
shogun5
Asked:
shogun5
  • 8
  • 5
  • 4
2 Solutions
 
Gustav BrockCIOCommented:
Remove tblClass and use as filter: Where tblStudent.StudentClassId = 14
Then use Delete from tblStudentClassBehaviors.

/gustav
0
 
shogun5Author Commented:
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
 
PatHartmanCommented:
There is no reason to include the other two tables in the delete query so remove them.  Leave only the behaviors table.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Gustav BrockCIOCommented:
You didn't remove tblClass.

/gustav
0
 
shogun5Author Commented:
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
 
Gustav BrockCIOCommented:
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
 
shogun5Author Commented:
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
 
PatHartmanCommented:
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
 
shogun5Author Commented:
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
 
PatHartmanCommented:
ClassID is numeric and so should not be enclosed in quotes.
0
 
shogun5Author Commented:
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
 
shogun5Author Commented:
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
 
shogun5Author Commented:
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
 
PatHartmanCommented:
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
 
shogun5Author Commented:
Thanks! Okay....that did it! Gustav and Pat....thanks so much for your help. Learned a lot!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
 
PatHartmanCommented:
You're welcome:)
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 8
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now