Fully Automated Data Entry User Form in Excel - Part 2 (Edit and Delete Records)

hello everyone welcome back you're watching the data labs my name is Dilip in this video we are going to learn how to add edit and delete features in data entry form so basically this video is the second part of automatic data entry form step by step in our previous video we only focused on transferring the data from form to database and there were no like edit and delete features so in this particular video we are going to focus only on editing and deleting features and we will utilize the same work workbook basically the same excel file which we developed in the previous video basically I will provide the previous video link in the description box so that you can go through that video as well and after that after learning how to add the features of transferring the data from form to database then you will start learning how to add the feature of editing and deleting in that form okay so let's without wasting our time let's move to excel window and start developing adding this feature from a scratch so here you can see that this is the same form which we developed in last video let me show you and this is the form so what we need to do we are just going to add two additional features that is called edit and delete so let's close this form and head towards at the developer tab and then Visual Basic and just maximize this window now this is you are in Visual Basic application window right just double-click on the frm form and and now we need to make some modification in this form basically we need to add two but our buttons over here and one is text box so let's increase the height of this form maybe this that is it's enough and just increase this size the frame size again the listbox drag the listbox little bit lower yeah yes and and justice eyes okay good now we need to add to command veterans over here so let's add the command button and this is the command button in ok just click over here and again slip the power button and just draw the command button over here right ok just like this move this toolbox over here and go to the properties window and [Music] select the top maybe just keep it 7 let's make it eight and the height should be a 20 okay again the top I just talked maybe a 10 let's dislike this now you can see that if we have 2 different common buttons command button 1 and come on what will - let's change the name so the first should be CMD add it ok and the caption should be add it let's accelerator key should be e and maybe the back color let's assign any other color maybe this one let's select the second and change the name from command button to to see MD delete okay so basically we are going to utilize this particular button to delete the existing the selected records basically okay and the caption should be delayed and the accelerator key should be maybe 2d okay let's select the back color so back color should be palette and this one so now you can see that of we have two different common version buttons let's quickly add it text box over here so this is the text box just press this text box over here and the name should be txt row number okay and we need to change couple of properties here just like we need to make it I keep it locked so that user will not be able to make entry so let's lock is true and we need to make it like the visibility should be false okay so this is very simple let's start doing coding so first of all we need to add a coding module so let's move to module 1 the first code is to identify the selection which row has been selected by user so let's double click on module and just go to the end and let's start adding so let's add the function basically function to identify the selected row number so we'll collected row number means selected records in list box so as its selected underscore list okay and this should be act long okay let's get some moon over here let's declare a variable team I as long okay and let's initialize this selected list with zero selected underscore list is equal to zero as you mean that a user has not selected any well and now we will loop through each and every record whether user has selected or not right so let's start using a for loop for I is equal to zero to the power M form dot LST database dot list count list count minus one okay and that's next i okay now we need to write the code or to identify whether which particular who has been selected by user so let's start utilizing the if statement if frm form dot LST database dot selected and then I is equal to true and then selected in the score list is equal to I plus one okay and if suppose if the selection has been found while looping through each and every record then we need to exit from the loop so exit for okay and then that's it if that's the simple code to identify which true item has been a selected by user so suppose user has not selected any of the any of the row in list box and clicking on edit button then it will it will give the value of zero it means no nothing has been selected and in that case we will like an it will give a popup message to user that you have not selected any records from the list box so this code has been done let's move to frm form and start assigning the code on edit like the click event on edit and also click event on delete so let's start with edit and double click on that just create some room over here now we need to start with like if selected list is equal to zero then suppose a user has not selected any of the items in list box then we need to give a popup message to user that you were not selected in yet items right so message box is sorry his note row has always selected selected okay and the message should be really okay plus visual basic information icon and then no recorder maybe edit okay and then exit server so in that case if there were there would not be any selection in list box then it will prompt a message and it will come out from this particular procedure right assuming that user has selected and the selected list counting not zero it's a greater than zero then we need to proceed with the code to edit the details right so let's start with so let's put the comment over here to code to update the values to respective controls okay let's start let's declare a variable name s sender as string basically we will if you utilize this variable to store the gender and then we will convert it from string value to boolean that is or like female or male okay because we are utilizing option button for gender okay so let us start the coding so first of all we need to assign the value on the newly created let me go to from here so first of all we need to assign the role number to this text box and this text box is hidden so let us apply this just double click on edit and me dot txt rule number dot value is equal to select are not selected basically I selected selected under school list plus one okay so it will provide the rule number and it will assign the row number to the text row number let's assign a employee ID so me dot txt ID dot value is equal to me and out LST list box dot list and then we need to pass here the row number and then column so the rumor is made or I'll list database dot list index okay comma one so this is for user ID let's copy this code and paste it over here this is for username so that's txt me dot txt name okay and just keep change the column to two let's copy this code again for gender and here now we need to copy this s gender and replace this with because we are first of all we will assign this selected value or male or female values to as send it and then we will reassign the value like the option butter so let's mean and this is root three and then start with you block if s gender is equal to female then me dot opt female dot value is equal to true else me dot opt male dot value is equal to true and if so this is done for gender let's create some room over here and let's start writing code to assign the value drop-down so let's so me dot CMB department dot value is equal to let's copy this code okay just paste it and change the value from three to four okay so let's see the sequence in our excel sheet go to exile and this is for gender than Department and then we need to over city and country okay so let's copy this paste it two times seeing the reference from four to five and here six and here txt city txt country okay that's it so now it has been done so what it will do it will assign all those value to the respective control box maybe text box combo box component combo box basically or option button so let's put a message over here please make the required changes and click on Save button to update okay maybe okay plus let's drag this little bit here weekly information and then get it so now the code is done for editing are like not editing basically it's it will assign reassign the values to the respective controls let's move to the module and let's make the changes in reset as well as good to submit the data so first of all we will add one line code here to reset the textbox and once the additional text box is txt a rule number dot value is equal to the blank okay now let's move to the submit code here you can see that we have added a code that is to identify the next blank rule so so basically we are going to utilize the edit feature here so we have already assigned on the row number existing role number in a our text box so we will utilize that whole okay so let's put the code if if frm form dot txt row number dot value is equal to blank then so suppose the value is not available in txt roll number it means user is entering a new data and in that case we will I will need will have to move the data to or next blank route if txt blank row is available like some some row number is available in TX to Rotom but then we will utilize this txt row number as a I row so let's else pyro is equal to Fr M form dot txt row number caught value and so that's done this particular pudding is done so let's move to a forum forum here and let's start coding for delete so let's double click on delete and now you can see that the one sub procedure has been added on click event so let's make create some room over here let's start the coding so again we need to identify whether user has selected me of the recalls or not so let's if selected list is equal to zero to ten message box no row is selected okay to be okay plus we make information comma and here the title should be delayed okay exit sub because user has not selected then we need our proceed ahead right and if that's done suppose user has selected the records and the selected list is greater than zero then we need to write the code or to delete the records okay let's add a variable declared a variable first like time I as VB message box result and we need to get the confirmation from user whether you want to delete or not so that's I is equal to message box do you want to delete the selected record okay and we need to give the to option either yes or no so gravy yes no plus as we are asking a question - so the icon should be V V question right and here a confirmation now if I is equal to VV No then we simply exit from this subsidy tax is it sir so as you mean user has selected yes then we need to proceed with or the code to delete the selected record okay so let's write the code this working dot sheets and the database because the database is the actual shape where or we are transferring the data dot rows okay and then selected list plus one okay dot delete so this is very simple code to delete the selected list I will provide the row number in list box and as you know we are utilizing a one roof or header in database and that's why we are adding one okay very simple now it will delete the d'accord now we need to reset so let's call reset and then give a confirmation message to user message box selected record has been deleted okay we okay only plus may be information deleted so this is all about the code to delete the selected record so now our coding has been done everything is done let's run this let's debug and compile now you can see that there is no error let's run this form and you can see that these one record here and you can also see the same record available in database so let's transfer a couple of records quickly then we will try to edit and delete so okay so we have three different records let's try to make the change the name of the little mark Dilip Kumar Tiwari - GLIP mark so let's click this and click on edit now you can see that it's showing the message that please make the required changes and click on Save button to update so let's delete the Tiwari here and click on save so you can you can notice here that on the second record showing Dilip Kumar Tiwari once you click on save it will get the confirmation message do you want to save the data yes now you can see that Dilip Kumar - Arya has become Dilip Kumar and here it's also reflecting let's delete one record that is mr. Kumar so let's select this click on delete and click on yes so selected record has been deleted successfully now you can see that the third record is not available here and same goes with the database worksheet okay so friends this is all about how to add edit and delete features in the existing data entry form so in this way you can create advanced or data entry form and make your form very very dynamic so that user will be able to edit and delete the existing code as well okay so hope you enjoy this video thank you for watching please like share subscribe and comment have a great day bye bye

Channel: TheDataLabs

Views: 281,864

Keywords: Automated forms in excel on UserForm with EDIT and Delete features, How to Edit the records with VBA code, How to delete the row through VBA, Add VBA code to edit or delete the existing records in Excel Sheet, Part 2 of Fully Automated Form

Id: K1d4JpuLkqE

Length: 21min 47sec (1307 seconds)

Published: Wed Jan 15 2020