4. Delete data on the database

Steps:

1.       Create a CustomerRecordUI.frm as shown below:

a.       Select CustomerRecordUI form.

b.      On the Menu, click Tools->Menu Editor, and type in as shown below:

2.       On dbs.bas, type the following:

    Public db As New Connection

    Public Sub OpenConnection()

        'validation

        If db.State = 1 Then db.Close()

        db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _

                                     & "Data Source=" & App.Path & "\Database\SALES.mdb"

        db.Open(db.ConnectionString)

    End Sub

    Public Sub Execute(ByVal sql As String)

        'validation

        db.Execute(sql)

    End Sub

    Public Sub CloseConnection()

        'validation

        db.Close()

    End Sub

3.       On the CustomerRecordUI.frm code window, type the following:

   Private Sub Form_Load()

        Call DisplayCustomers()

    End Sub

    Public Sub DisplayCustomers()

        dbs.OpenConnection()

        Dim rs As New Recordset

        rs.Open("Customers", dbs.db)

        If rs.BOF = True And rs.EOF = True Then Exit Sub

        Me.lvwCustomer.ListItems.Clear()

        Dim counter As Integer

        counter = 0

        Do Until rs.EOF

            Customer = lvwCustomer.ListItems.add(, , rs.Fields("id"))

            With Customer

                .SubItems(1) = rs.Fields("name")

                .SubItems(2) = rs.Fields("address")

                .SubItems(3) = rs.Fields("contactno")

                .SubItems(4) = Format(rs.Fields("creditlimit"), "#,##0.00")

            End With

            counter = counter + 1

            rs.MoveNext()

        Loop

        rs.Close()

        dbs.CloseConnection()

        Me.stbCustomer.Panels(1).Text = "Total Customers: " & counter

    End Sub

    Private Sub Form_Resize()

        On Error Resume Next

        Me.lvwCustomer.Width = Me.Width

        Me.lvwCustomer.Height = Me.Height - 500

    End Sub

    Private Sub lvwCustomer_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)

        On Error Resume Next

        If Button = 2 Then

            lvwCustomer.SelectedItem = lvwCustomer.HitTest(x, y)

            PopupMenu(mnuPopUp)

        End If

    End Sub

    Private Sub mnuDelete_Click()

        Dim result As VbMsgBoxResult

        result = MsgBox(lvwCustomer.SelectedItem.SubItems(1) & " will permanently be deleted. Continue?", vbYesNo, "Confirmation")

        If result = vbYes Then

            Dim Customer As New Customers

            Customer.Delete(CInt(lvwCustomer.SelectedItem.Text))

            Call DisplayCustomers()

        End If

End Sub

4.       On the Customers.vb, type the following:

    Option Explicit

    Public Sub Delete(ByVal ID As Integer)

        Dim sql As String

        sql = "DELETE FROM Customers WHERE id=" & ID & ""

        dbs.Execute(sql)

    End Sub

    Private Sub Class_Initialize()

        dbs.OpenConnection()

                     End Sub

Debugging your code

-To familiarize the behaviour of the language or to know how your codes are executed in sequence, debugging should be done. In visual basic, just put a break point by clicking at the left margin of the procedure or function and then press F8. To break the debugging mode, just click the end button at the right of the start button in the toolbar and to remove the break point, just click the break point to toggle.

Congrats, you made it-mariuz the farmer:)