Deleting rows from a table using a query

[neosam]

Hi,

I am probably real dumb with these codes. I am working on a small project in which one of the most important things to be done is to delete fields from a table. So what i did was to write a SQL query and connect to a form's command button. But this has to only delete fields where the table's primary key matches with what the user enters in a text field on the form. The SQL code i used is

DELETE field1,field2,field3,.....
FROM <TableName>
WHERE field7=<FormName>.<TextboxName>

But there seems to be a problem with the code. Could someone help me with this

Thanks

[Megalog]

DELETE field1,field2,field3,.....
FROM <TableName>
WHERE field7=<FormName>.<TextboxName>

Try:

DELETE TableName.field, TableName.field2, TableName.field3
FROM TableName
WHERE (((TableName.field7)=[Forms]![FormName]![TextboxName));

Make sure that the value in the table field, is of the same type that's being matched up in the form field. (number vs number, text vs text) If not, then a Convert function will be needed.

[neosam]

Try:

DELETE TableName.field, TableName.field2, TableName.field3
FROM TableName
WHERE (((TableName.field7)=[Forms]![FormName]![TextboxName));

Make sure that the value in the table field, is of the same type that's being matched up in the form field. (number vs number, text vs text) If not, then a Convert function will be needed.

Hi,

I did that but the query does not have a regular query icon but it has a "x!" and that is perhaps why the query is not being connected to the form's command button. If run the query seperately it works though!

Thanks

[Megalog]

Nope that's correct, different query types will show different icons. The 'x!' is the delete query icon.

How are you running the query in your form? You should be using:

Private Sub YourCommandButtonName_Click()
 
DoCmd.OpenQuery [YourDeleteQueryName]
 
End Sub

If that works, you should get prompted a warning that you are deleting rows.
If you want to disable the warnings afterwards, use:

Private Sub YourCommandButtonName_Click()
 
DoCmd.SetWarnings False
DoCmd.OpenQuery [YourDeleteQueryName]
DoCmd.SetWarnings True
 
End Sub

[neosam]

Nope that's correct, different query types will show different icons. The 'x!' is the delete query icon.

How are you running the query in your form? You should be using:

Private Sub YourCommandButtonName_Click()
 
DoCmd.OpenQuery [YourDeleteQueryName]
 
End Sub

If that works, you should get prompted a warning that you are deleting rows.
If you want to disable the warnings afterwards, use:

Private Sub YourCommandButtonName_Click()
 
DoCmd.SetWarnings False
DoCmd.OpenQuery [YourDeleteQueryName]
DoCmd.SetWarnings True
 
End Sub

Hi,

I get a error message which is "Can't find project or library". is that something to do with references???

Thanks

[Megalog]

Please post what code you're using in your command button click event.

[neosam]

Please post what code you're using in your command button click event.

Code in VB

Private Sub Command22_Click()
    DoCmd.OpenQuery (Nom)
End Sub

And the SQL query is .

DELETE [Nominations].[Employee_Id], [Nominations].[Employee_Name], [Nominations].[Team_Leader], [Nominations].[Date_Of_Training], [Nominations].[Training_Name], [Nominations].[Hours_Of_Training], [Nominations].[Venue], [Nominations].[Time], [Nominations].[Trainee_Id]
FROM Nominations
WHERE [Nominations].[Trainee_Id]=[Forms]![Cancel Nominations]!Text5;

[Megalog]

Edit: forgot the quotes around the query name =)

paste this over your form code:

Private Sub Command22_Click()
    DoCmd.OpenQuery "Nom"
End Sub

and paste this over your query code in SQL view

DELETE [Nominations].[Employee_Id], [Nominations].[Employee_Name], [Nominations].[Team_Leader], [Nominations].[Date_Of_Training], [Nominations].[Training_Name], [Nominations].[Hours_Of_Training], [Nominations].[Venue], [Nominations].[Time], [Nominations].[Trainee_Id]
FROM Nominations
WHERE ((([Nominations].[Trainee_Id])=[Forms]![Cancel Nominations]![Text5]));
 

[neosam]

Edit: forgot the quotes around the query name =)

paste this over your form code:

Private Sub Command22_Click()
    DoCmd.OpenQuery "Nom"
End Sub

and paste this over your query code in SQL view

DELETE [Nominations].[Employee_Id], [Nominations].[Employee_Name], [Nominations].[Team_Leader], [Nominations].[Date_Of_Training], [Nominations].[Training_Name], [Nominations].[Hours_Of_Training], [Nominations].[Venue], [Nominations].[Time], [Nominations].[Trainee_Id]
FROM Nominations
WHERE ((([Nominations].[Trainee_Id])=[Forms]![Cancel Nominations]![Text5]));
 

Hey it works... Thanks a lottttt!!!

[Megalog]

Hey it works... Thanks a lottttt!!!

Glad to help =) Have fun!

[NeoPa]

FYI: DELETE queries delete records rather than fields. Deleting a field would involve changing the design of the table whereas deleting records is simply unloading some of the data.

This means that it is entirely unnecessary to list the fields in the SQL. The following should work for you just as well (This is not intended in any way as criticism MegaLog - Quite the contrary, you've given a fair bit of helpful advice in this thread :)) :

DELETE *
FROM Nominations
WHERE [Trainee_Id]=[Forms]![Cancel Nominations]![Text5]

[Megalog]

FYI: DELETE queries delete records rather than fields. Deleting a field would involve changing the design of the table whereas deleting records is simply unloading some of the data.

This means that it is entirely unnecessary to list the fields in the SQL. The following should work for you just as well (This is not intended in any way as criticism MegaLog - Quite the contrary, you've given a fair bit of helpful advice in this thread :)) :

DELETE *
FROM Nominations
WHERE [Trainee_Id]=[Forms]![Cancel Nominations]![Text5]

That was going to be my next suggestion if it didnt work.. I only use "Delete *" in my queries. If I delete only certain field data, I use an update query... which I hope isnt what the OP wanted =) I dont take it as criticism, I like the FYI's, usually learn a little fact here and there.

[NeoPa]

Good to hear it - and keep up the good work - some good stuff recently :)