Use VBA user form to find and update a record

  • Hi,


    I have a userform that can Add, Find Next, Find Previous and all work ok. I have added an update button as each record will have to be updated over time. The problem is, when I click update it just creates a new record. How do I fix that?

  • Re: Use VBA user form to find and update a record


    Hi,


    Here is the file.......


  • Re: Use VBA user form to find and update a record


    The board likes code to be wrapped in 'Code Tags'. These indent and colour the code making it easier to read. Please edit your post, select the code and click the # button.


    Taking the code as it stands, the simplest thing to do would be to add a new button to add a new record. The code under that button would simply get the row number of the first empty row (you didn't seem to include that), clear the controls ready for input. The existing Update button would then work correctly but you might want to change the caaption to Save', for example.

  • Re: Use VBA user form to find and update a record


    Sorry, no time to look at a file. Perhaps someone else might be able to help.

  • Re: Use VBA user form to find and update a record


    You have scoped the variable CurrentRow to the userform so that will keep its last assigned value while the userform is loaded. The absolute simplest way to do this is to exit a Find procedure as soon as the item is found and the controls have been updated.



    Your Update procedure will then use that Row reference for the update.


    Haven't delved into it too deeply, but you need to edit any other Find procedures and verify any place CurrentRow is modified does not invalidate any following Update.

  • Re: Use VBA user form to find and update a record


    Hi niguk
    With your userform code...


    using the find function would be better

  • Re: Use VBA user form to find and update a record


    Genius! Works like a charm. Thank you so much. I had been staring at it for days. :):thumbcoo:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!