Deleting a record

                Deleting a  record  on a table is very crucial so in terms on coding we must be safe . there  are two types of deleting a record one is deleting all of the content of a specific table or we want to delete a specific record on a specific table.

                Now lets proceed for the first example we want to delete all of the content on the table so create a button with a caption of delete all, and another button to with a caption of Delete

Your form should look like this

Now first we will concentrate the most easiest part which is deleting all of the records, double click the button delete all.

If MsgBox("Are you sure you want to delete", vbYesNo, "delete") = vbYes Then

                ‘ first before deleting the content the database we want to ask the user if he/she is sure on deleting the record then if he press the button yes then that would be the time to execute this codes.

    Dim rstDelall As New ADODB.Recordset

                ‘declareation a of recordset

rstDelall.Open "Delete * from tbl_Products", con, 3, 3

                ‘ we are saying that we want to delete all the records on the specified table

   

Me.lvproducts.ListItems.Clear

                ‘ we need to clear the listview since we already deleted all the products

End If

Deleting specific Record

Dim product As String

‘declaration of product with a datatype of  string

product = Me.lvproducts.SelectedItem.SubItems(1)

‘product will now hold the value of the name of the selected  product on the listview

If MsgBox("are you sure you want to delete " & product, vbYesNo, "delete") = vbYes Then

    ‘ask the user if you really want to delete the selected product

Dim rstdel As New ADODB.Recordset

‘declaration of a recordser

    rstdel.Open "Select * from tbl_Products where fld_PID= " & Me.lvproducts.SelectedItem.Text & "", con, 3, 3

‘select statement that we want to get a specific record

    rstdel.Delete

‘delete is we are refering that we want to delete a single record

    Me.lvproducts.ListItems.Remove (Me.lvproducts.SelectedItem.Index)

‘remove the selected item on a listview.

End If

Were done..

Happy coding …

Updating/modifying a record on a table

                On  updating a record there is one question that you must consider, what record  would you like to update is it dynamic modification on the table or fix, when we say dynamic you are updating a record base from the input of the user where in you are not sure on what would be the record to update, while when we say fix you already know what is the record to be updated.

                Now lets proceed  on the form that we already created the insertion of products form create a button wit a caption of Update the thing that we want to do is that we want to select a record on the listview and the one who is selected would be the data to be updated.

Example: Dynamic modification of records.

                Our reference if what product we want to update is the selected item on the listview and the PID which is the first column on the listview.

                To get the selected Item on the listview the code is  “lvproducts.SelectedItem.Text”  this code would return the first column on the selected row/item in the listview but is you want to get the value of the preceding column you need to use a keyword called subitems, “lvproducts.SelectedItem.SubItems(1)” this code gets the value of the second column take note that the first column have a index value of 0 then the preceding is 1 to… it is arranged like array.

                Now for the first step on coding we want to select a item on the listview what we want to do is when we select an item on the listview the corresponding value will be putted to the textbox except for the quantity.

Now lets do the coding double click the listview on our form

After double clicking, click the event property on the upper right corner then select for the event itemclick. A new event will be created.

 On this event put this code:

Me.txtProduct.Text = Me.lvproducts.SelectedItem.SubItems(1)

‘this code is use to put the product name to the txtProduct from the second column on the selected row

Me.txtPrice.Text = Me.lvproducts.SelectedItem.SubItems(3)

‘this code is use to put the product name to the txtPrice from the third column on the selected row

If you observe and done the code correctly for every click on the listview the corresponding value for the textbox is inserted.

Now the next code would be the real deal, we will now put some code on the button  update go to design view then double click the button update then put  this code.

Dim rstupdate As New ADODB.Recordset

‘declaration of variable rstupdate

rstupdate.Open "Select * from tbl_Products where fld_PID = " & Val(Me.lvproducts.SelectedItem.Text) & "", con, 3, 3

‘ this become tricky why? What we want to do is to base on what would be the product to update on the selection on the listview we have a none fix value for the listview it is depending on our selection the “where” clause is used to get a specific data on the table, if we observed we added a  3 double quotes  " & Val(Me.lvproducts.SelectedItem.Text) & "", and on the middle is the val(…)  meaning that we are referring to a field on the table with a data type of number.

rstupdate.Fields("fld_ProductName") = Me.txtProduct.Text

‘if  there is a change on the name then it will be updated

rstupdate.Fields("fld_Price") = Me.txtPrice.Text

‘same with this code you are assigning a new price for the product

rstupdate.Fields("fld_Stocks") = Val(rstupdate.Fields("fld_Stocks")) + Val(Me.txtStocks.Text)

‘now this one is where we need to  consider the old stock so if we will update it we need to odd the current stock from the new one, the new one is txtstock and rstupdate.fields(“fld_stocks”)  is the old stocks.

rstupadte.update

‘always remember that any modification that we create on the database table we must call update keyword  to make some changes on the tables.

txtProduct.text=””

txtPrice.text = “”

txtstocks.text= “”

now were done

Happy coding