+ Reply to Thread
Results 1 to 21 of 21

Need to find record using userform then update and overwrite in same row in spreadsheet

  1. #1
    Registered User
    Join Date
    10-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    65

    Need to find record using userform then update and overwrite in same row in spreadsheet

    Hello Everyone,

    Basically I have learnt vba from scratch by piecing various things together and somehow I have managed to create what I need.

    I have a spreadsheet which is populated by a userform. I have the next, previous, first and last record buttons so i can scroll through existing records.

    However, I cannot find how to do the following and it is becoming very frustrating:

    1. I need to be able to find a record by putting search terms in a text box
    2. When I find the record, I need to be able to update it, delete some things if need be and then save the overwritten record on the same row using an update/amend command button. At present I can only save any overwritten records to a new line which is not what I want.

    I have scanned the forum but cannot find a solution that does not add an additional row

    Unfortunately because of restrictions on my (work) computer I am unable to attach a file. However I am very good at following examples so if someone has something similar that I can use then I would be very grateful. I can give a sanitized example of some of the code I am using though.

    Your help would be much appreciated

    The following is the code I use to populate the spreadsheet and add another record

    Please Login or Register  to view this content.
    The following code occurs when the form is intialized

    Please Login or Register  to view this content.
    The following is the code I use to move along records

    Please Login or Register  to view this content.
    I hope this all helps

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    You cab benefit from using the FIND methodology to instantly jump to an existing known record number, if it exists.

    Your GoToNextRecord macro:
    Please Login or Register  to view this content.

    To jump directly to the "next rec" when a record is already loaded into the form:

    Please Login or Register  to view this content.
    T0 use the FIND Method to jump directly to a record where you have entered the a search text into the form in the textRef box, I believe that data is in column A of your data sheet, so add a SEARCH button and try:

    Please Login or Register  to view this content.

    Does that help you get going down the find/update path? You can use this same technique to insert values back into the data sheet.


    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    Copy of Copy of Client Leads Log vHOME.xlsHello and thanks for replying

    I have created a search button with the code you suggested but where would I put the search term as I currently just get the message back. I am sure this is down to some fault of mine but if you could inform me I would be very grateful again .

    I did find some code (spreadsheet attached) which does what I want, however when I transfer it to my spreadsheet I get the following error message "user-defined type not defined" around the code
    Please Login or Register  to view this content.
    The reason I like this method is because it searches the whole spreadsheet for the term I am looking for not just one column.

    Any help welcome and appreciated

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    I'm not familiar with anything you're trying in the new code.

    The FIND syntax can search more than one column. I'm not sure I can help much more than I have. You're already playing with ideas beyond anything I've done with forms.

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,198

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    If you're getting that error, is it possible that you don't have the listview installed on your computer? The code you posted worked for me.

    The same thing with a listbox would be:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    Hooray!!!!!

    Thank you soooo much. This works!!!

    Would you mind please changing the following code that I use to display the found record on the userform

    Please Login or Register  to view this content.
    I am guessing that I change the Listview1 to something but not sure what.

    Finally - do you have any ideas on how once I have retrieved the found record and changed it, how to save it back in the same row with an amend/update command button?

  7. #7
    Registered User
    Join Date
    10-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    Thank you so much for your assistance

    I now have knowledge of the FIND function which I did not before

    Thanks

  8. #8
    Registered User
    Join Date
    10-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    Quote Originally Posted by JBeaucaire View Post
    I'm not familiar with anything you're trying in the new code.

    The FIND syntax can search more than one column. I'm not sure I can help much more than I have. You're already playing with ideas beyond anything I've done with forms.
    Thank you so much for your assistance (The message at the bottom of the thread was meant for you)

    I now have knowledge of the FIND function which I did not before

    Thanks

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    NOTE: It's not clear in your RAW DATA sheet since you didn't leave examples of real-formatted fake data, but one would expect one column to be some sort of unique rowID. IF there is no unique piece of data in any column you won't be able to reliably do an UPDATE via macro, at least I wouldn't trust it. Is there a unique value in one of those columns? If not, add it. It can be nothing more than an increasing numeric value, 1,2,3,4, etc...

    You can add a text field to your form to display this value, too, but disable the field so the user could not change it.

    This has the added benefit of making it dead easy to add NEXT and PREVIOUS buttons to your form, too. If this locked field shows the value of 5 (record #5, probably row 6), then clicking NEXT would need only see that field's value, increment it, then load in the data from that row. PREVIOUS is just a easy.

    In the NEXT macro you would only need to test if the current value in that field = the MAX value in that column already, in which case the NEXT record is #1, restarting at the beginning. For the PREVIOUS button, you do the same test, except for the MIN value.I've already shown you how to use the FIND method to go directly to a cell. Once the previous macro has finally loaded the row into the form, when you are ready to copy back to the database, use the same technique

    Use the FIND method to search the one column you know you DON'T change, the key piece of data in one of those cell. Store that FIND result in a variable. Once that cell (range) is stored in a range variable, you use the properties of that cell to update that row.
    ==============

    I've already shown you how to use the FIND method. Use that to UPDATE the data row currently in the form. First do a FIND on that unique piece of data, store that in a range variable. Then you can use the range variable ".row" property to update the value on the found row.

    ==============
    Tips on the FIND command.

    In my opinion, the minimum parameters that should be included with every FIND command include the LookAt and the LookIn parameters. If you don't specify them, Excel uses the values from the last time it was run, and that's not good. There are no defaults.

    So in Kyle's macro, I would edit this to specifically indicate the FIND should look for matches in "part" of longer strings:
    Please Login or Register  to view this content.
    In my suggested use of a "code column" to find exact matches on the recNo so you can UPDATE that specific row, you would want to search for exact matches only, else a search for 12 might match to 122 accidentally:

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 11-15-2012 at 10:54 AM.

  10. #10
    Registered User
    Join Date
    10-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    yes, I do have a unique id in column A.

    The thing is that there are free text fields with words and phrases in other columns that I might want to look up. Using the suggested code by kyle123 I can put my search term in, which comes back with results of relevant rows in a listbox. I can then choose the record and it fills the userform with that record so that I can edit it and then save on the same row.

    The excel sheet that I attached does do what I require except it pastes on an additional row (and uses listview which I do not have but I have converted to listbox).

    I am sure that once the desired record fills the userform there must be a way to overwrite on the same row using an update command button.

    Is there not some kind of code that says "put this row of data in the same place you retrieved it from"?

  11. #11
    Registered User
    Join Date
    10-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    Can an iF statement be used to say something like

    If you retrieved the data from row 21 then paste back into row 21

    or

    If the unique id is ABC then clear everything next to ABC and paste new data next to ABC

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,198

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    Quote Originally Posted by JBeaucaire
    In my opinion, the minimum parameters that should be included with every FIND command include the LookAt and the LookIn parameters. If you don't specify them, Excel uses the values from the last time it was run, and that's not good. There are no defaults.
    Yes, I agree, I didn't write the code, just adapted it from a listview

    @paulary, just give it an ID, the user doesn't need to see it, but it makes everything much easier

  13. #13
    Registered User
    Join Date
    10-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    Can an iF statement be used to say something like

    If you retrieved the data from row 21 then paste back into row 21

    or

    If the unique id is ABC then clear everything next to ABC and paste new data next to ABC

  14. #14
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,198

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    No, you need to tell it where to put the data - it has no concept of where it has come from unless you tell it where it came from, hence the requirement for an ID column

  15. #15
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    Use the FIND command that I've shown already to search for the column A value and store it in your found range variable. Now use the found variable's .ROW parameter to update that row. See my original example.

  16. #16
    Registered User
    Join Date
    10-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    @kyle are you able to adapt the following as you did previously

    Please Login or Register  to view this content.
    @JBeaucaire - I will try and follow your instructions as I find this all confusing being a newbie to VBA . I have patched things together like a patchwork blanket. I might try and make a copy of my database that is sanitised and more like the one I am actually using. i don't know where i am putting the code etc. i will get back to you and I do appreciate both of your help very much.

  17. #17
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    It's a good idea to force yourself to understand the code you are intending to rely on in the future. You can do it.

  18. #18
    Registered User
    Join Date
    10-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    I think i am getting an understanding but I do require to search the whole sheet for words and phrases.

    Is there a way of using code similar to
    Please Login or Register  to view this content.
    for more than just the A column


    My userform brings the unique ID into itself when I fill it or move from record to record using (the unique id is in the txtref.Text)

    Please Login or Register  to view this content.
    Can I not use the listbox
    press select to select the record I want
    It then pulls up the record that I selected into the userform based on the ID number (txt.Ref) of the record that I selected
    And then it saves over itself based on the ID?

    I apologise if I am going round in circles

  19. #19
    Registered User
    Join Date
    10-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    By the way I am using the code that you suggested earlier. trying now and will update you....

  20. #20
    Registered User
    Join Date
    10-25-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    65

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    Quote Originally Posted by JBeaucaire View Post
    It's a good idea to force yourself to understand the code you are intending to rely on in the future. You can do it.
    Hello again,

    I have adapted the code that you suggested behind a search button and an update button.

    Then I realised that I have no where to put the search term. Where do I put the search term that i am looking for?

  21. #21
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need to find record using userform then update and overwrite in same row in spreadshee

    Search one column only:
    Please Login or Register  to view this content.

    Search several specific columns:
    Please Login or Register  to view this content.

    Search all cells:
    Please Login or Register  to view this content.

    In all examples above you are placing the "result" of the search into the range variable recFind. So now you use that stored variable's parameters to update that row. It looks like your searchForRec macro is populating the form by calling the retrieve macro.



    As for UPDATE, it's the same thing. Once the macro has searched for the value in txtRef, you have the correct row again stored in the recFind variable's recFind.Row, so you use that to fill out the cell on that row with fields from your form, then reinitialize the form.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1