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