How to Use SQL Delete Statement

TGoldenEye

It enables you to enjoy your life more comfortable with your generic cialis online greyandgrey.com love partner. This can be done by working on each other sea of vitality points, which is located on the canadian pharmacies tadalafil jaws. In men’s body usually the penis consists of two kinds viagra 100 mg of bodies – cavernous and spongy. However, sometime later on http://greyandgrey.com/medical-treatment-guidelines/ sildenafil price in india these unwanted effects stop showing up.


SQL- DELETE Statement

The SQL Delete Statement is the SQL code that used to delete data from a database. It will be used in combination of other VB code. There are few examples of using the SQL –Delete Statement below.

SQL DELETE Syntax

Delete a specific record from a table:

DELETE * FROM table_name WHERE  column_name = some_value;

Delete all records from table/query:

DELETE * FROM table_name/query_name;

 

#1 Delete a Selected Record via ListBox

The Delete command can be put under the click button to delete a record that is selected from a List Box.

VB Code under Delete button:

Private Sub cmdDelete_Click()
Dim delCustomer As String
If IsNull(Me.List0) Then
    MsgBox "Please select a customer first"
Else
    delCustomer = "delete * from tbl_customer where (Customer_ID = " & Me.List0 & ")"
    DoCmd.SetWarnings False
    DoCmd.RunSQL delCustomer
    DoCmd.SetWarnings True
    Me.List0.Requery
End If
End Sub

delete from list

 

#2 Delete All Records from Query

Create a specific query with your requirement. In this example, the query for Customers who live in state of CO is created. The VB code below will be used to delete all customers in this query without using criteria in the SQL Delete statement.

state CO list

VB Code:

Private Sub Command0_Click()
Dim delCustomer As String
If IsNull(DLookup("Customer_Id", "qryCustomer at co")) Then
    'do notthing
Else
    delCustomer = "delete * from [Qrycustomer at CO]"
    DoCmd.SetWarnings False
    DoCmd.RunSQL delCustomer
    DoCmd.SetWarnings True
End If
End Sub

There is no record on query “QryCustomer at CO” after deleting by above code.
delete CO state

#3 Delete Specific Record from Criteria

Select a specific record by using a combo box. Then delete a selected record by clicking on the Delete Customer button. The criteria to delete is “Customer_ID =” & Me.cboCustomer. After deleting a current record, the form will move to a new blank record by VB code “DoCmd.GoToRecord , , acNewRec”

VB Code:

Private Sub Command280_Click()
Dim delCustomer As String
If IsNull(Me.cboCustomer) Then
    MsgBox "Please select a customer"
Else
    delCustomer = "delete * from tbl_customer where (Customer_ID = " & Me.cboCustomer & ")"
    DoCmd.SetWarnings False
    DoCmd.RunSQL delCustomer
    DoCmd.SetWarnings True
    DoCmd.GoToRecord , , acNewRec
End If
End Sub

delete with combo

 

#4 Delete Record that Not Match in another Table

There are two tables that contain a field (class_id field) that is on both tables (tblClass and tblTest below). There are some Class_ids are on both tables, but some not. For example below, it will delete a record in tblClass table that have class_id only in tblClass table, but does not have the same class_id in tblTest table.

VB Code:

Private Sub Command20_Click()
Dim strDel As String
strDel = "DELETE tblClass.class_id, * FROM tblClass WHERE (((tblClass.class_id) Not In (SELECT class_id FROM tblTest)))"
        DoCmd.RunSQL (strDel)
        MsgBox "Record deleted"
End Sub




Related posts