Update existing data using a UserForm

  • Aloha,


    I am very new to VBA and have created a UserForm for adding data to a worksheet by basically cutting, pasting and editing codes that I have found by searching the internet. Therefore, I know that my command button codes are not very consistent. I have gotten to the point that I can add new records to the sheet, find an existing record and populating the data back in the UserForm, however I am now stuck at updating an existing record that I just found.


    Here's what I got. The cmdUpdate_Click() area is where I'm in trouble. Instead of updating the info that I just found with cmdFind_Click(), it adds a duplicate entry with the new information. (Note I deleted some code that is working fine for dropdown lists as I didn't think you needed all that info, but if it's necessary I can paste my full code. Also my actual report has over 30 columns, I reduced it down to 4 here)


  • Re: Update existing data using a UserForm


    Hi Malani, - welcome to the forum


    So in summary - my understanding is
    cmdFind will populate the user form with values from the database, based on the search criteria "txtReportNumber.Text"
    cmdUpdate will take the values from the user form and stick them back into the database, based on the "currentrow" variable


    Your coding approach is not wrong, but it is not efficient. You use loops to loop through each row in the "FIND" procedure, which is OK, but the worst case scenario is you have check/test every single row to find the data you want to update. Using ".FIND" method on a range would be quicker as it would go directly to the correct record. However, lets try and fix what you have first before complicating matters a little.



    You have defined current row to be a global variable so its value is visible to both cmdFIND and cmdUpdate. This is fine. However, your problem I think lies in the fact that your loop goes from the first row to the last row, which increments currentrow by 1 each time. When the correct record is found (Cells(currentrow, 1).Text = myfind) and your user form is populated with data from the sheet, the loop continues... so Currentrow is now counting rows right down to the end of the list to the very last row (currentrow = 3 To lastrow). So while you have found the data and populated the user form, you have lost the "currentrow" position. You need to break or exit the loop when the data is found.



    If you want to do this without using loops, you would set a range variable (again a global variable) to find your string... something like this:


    the range variable R now points to the cell where txtReportNumber is found (or is "nothing" if not found), and this can then be used in the update procedure. I'll leave that code up to you ;)


    THE ABOVE CODE IS HAND TYPED AND ALL UNTESTED :) (I'll be shocked if it worked without errors :-D )


    HTH
    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Update existing data using a UserForm


    Ger,


    thank you so much. I like your find code much better. It's much faster with a lot of info. I'm going to start working on the update code, so I may be back if I can't figure it out.

  • Re: Update existing data using a UserForm


    Quote from malani2;773875

    Ger,


    thank you so much. I like your find code much better. It's much faster with a lot of info. I'm going to start working on the update code, so I may be back if I can't figure it out.


    Not a problem.... Start a new thread if you have any questions and I or someone else will help out!


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Update existing data using a UserForm


    Ger,


    I believe I'm already beyond my VBA skills. I have no idea what to do to get the info to update now that I've used your find option.


  • Re: Update existing data using a UserForm


    No worries,


    Firstly keep R as a global variable, visible to all sub routines, do this by moving it to the very top, first line of your code, outside of all the sub routines.

    Code
    1. Dim r As Range
    2. Private Sub cmdFind_Click()



    Now after the the find operation, R is pointing to that first cell so you can use it during the update...

    Code
    1. Private Sub cmdUpdate_Click()
    2. R.Value = txtReportNumber.Value
    3. R.offset(,1).Value = cmbCounty.Value
    4. R.offset(,2).Value = cmbClass.Value
    5. R.offset(,3).Value = txDateOff.Value
    6. 'Etc.


    Hth


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Update existing data using a UserForm


    :-D

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________