Solved

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

Posted on 2016-08-26
17
133 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 50

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 37

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 50

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 50

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 37

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 37

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 37

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 50

Expert Comment

by:Gustav Brock
ID: 41771986
You are welcome!

/gustav
0
 
LVL 37

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

752 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