Activate Delete Query from Form

[Tanya]

Hi I have created the following delete query, without a parameter

DELETE [tblStudents&Classes].*, [tblStudents&Classes].StudentID
FROM [tblStudents&Classes];

What I would like to be able to do is double click on a field in a form to invoke the deletion. Could someone please tell me how to go about this?

I added the following line to the query but it prompted me for the studentID when invoked:

WHERE ((([tblStudents&Classes].StudentID)=[Forms]![frmRemoveStudentfromclass]![subfrmMakeClass]![StudentID]));

code:

Private Sub StudentID_DblClick(Cancel As Integer)
DoCmd.OpenQuery "DeleteStudentsfromclass"
End Sub

Regards
Tanya

 

[MStef]

Why do you complicate, use a DELETE RECORD icon.

 

[Tanya]

Hi Mstef
I am not sure what you are referring to "Delete Record Icon", perhaps you refer to a menu option which I have disabled. I know how to change the properties so that you can edit and delete. I am also aware that in datasheet view you can select a record and delete it, not sure what you mean.
Further to my post I have realised that I cannot delete the record outright because there are related records in another table.
I now have no idea how to set up a form to allow deletion from two tables at the same time.
Any suggestions greatly appreciated.
regards
Tanya

 

[Tanya]

Silly me...
I just realised that I didn't select cascade delete related records in relationship...
I have now rectified the situation and can now delete all records by selection and delete key.
still interested in what you mean thought about delete record icon?
Thanks
Tanya

 

[MStef]

Look at attachment (word, zip).

 

[Tanya]

Hi Mstef
Thank you for this explanation.
No, I don't have this menu option when I open my form. The reason being I don't want to make the window too busy, also restricting access to many of the other tools.
Kind regards
Tanya

 

[gemma-the-husky]

personally, i would be very wary of allowing cascading deletes

you could inadvertently delete a load of stuff you then wish you hadnt - much better to mark it as not required in some way (yes/no flag normally)

 

[Tanya]

You have a valid point, it has occurred to me that I may at some stage want to view the competencies of the student after they leave the class. Mmmm Might add a boolean column for active.
Thanks gemma.