+ Reply to Thread
Results 1 to 6 of 6

How to Modify a row in a data Entry form without adding a new record

  1. #1
    Registered User
    Join Date
    12-03-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    How to Modify a row in a data Entry form without adding a new record

    Hi Everyone,

    I currently have a functionality on a data entry form in Excel where I can modify a existing record. Although I can modify the record and save it, it saves it as a new record. Is there anyway I can modify a record so that it modifies the existing row and doesn't add a new row.

    Thanks

  2. #2
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    Re: How to Modify a row in a data Entry form without adding a new record

    It would be much easier for someone to help you if they had a sample file with sensitive date removed. Click on 'Go Advanced' at bottom of post, then Manage Attachments, and upload a sample file.

  3. #3
    Registered User
    Join Date
    12-03-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: How to Modify a row in a data Entry form without adding a new record

    Hi,

    Apologies I am new to this forum.

    Please find attached spreadsheet.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    Re: How to Modify a row in a data Entry form without adding a new record

    Sorry I haven't been able to look into this yet. I thought someone else would have came along by now but I will try to get to it in a little while.

  5. #5
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    Re: How to Modify a row in a data Entry form without adding a new record

    anisali,

    Welcome to the forum! I finally got a chance to look at it and came up with 2 different ways to do this without changing a lot of things. Just so you know, this would have been a lot easier by using a form instead of cells on a sheet, but it does look nice for sure and it works.
    So the problem is in your save code. iRow is set to the first blank row every time you press Save. So it didn't matter if you were modifying a record or not, it still put it as a new record when you hit save because iRow was set to a new record. So the first file (Employee-Data-Entry-Form-in-Excel2.xlsm) I change your 2 variables to public and took them out of each sub, so that different subs would know what they were last equal too. Then added a public Boolean variable (ChangeRow) so when you hit modify, it will set to true, then when you hit save, iRow will stay the same row as it was when you clicked 'Modify' if ChangeRow is true. Then it is set back to False and ready for entering a new record. The problem with that is ChangeRow needs to be set back to false in every button code in case the user doesn't hit 'Save' (which sets it to false)

    The next file (Employee-Data-Entry-Form-in-Excel3) I also changed your 2 main variables to public and took them out of the subs so they would hold their value within all subs. But this one is more dependable I think but there is an message to click OK on. There is no boolean variable. It simply asks you if it is a modified record when you hit save. If you choose yes, it does not change iRow to a new row. If you choose NO, then it changes iRow to a new row and adds it as a new record.

    I guess you will have to decide which way you like better. Just remember, if you choose the first one, you have to set ChangeRow back to false in every button you may add.
    If either of these answered your question, I hope you will add to my reputation with the 'Add Reputation' star below.
    Good luck and let me know if you have any more questions concerning this problem!

  6. #6
    Registered User
    Join Date
    01-22-2021
    Location
    Manila, Philippines
    MS-Off Ver
    MacOS Big Sur
    Posts
    3

    Re: How to Modify a row in a data Entry form without adding a new record

    Hello,

    I stumbled upon your response. Thank you so much as it worked for me for a small data set.

    However when I tried to recreate your formula in Excel2, I find myself stuck with a Runtime Error 1004.

    I really hope you could help me with this one.

    Just to note, the data I am entering on the form will be saved to two separate excel sheets.

    Thank you so much in advance!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] New Entry From User Form Not Adding
    By LewisJ in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-28-2017, 11:38 AM
  2. Adding new record to existing database table with a form
    By Mongoloid in forum Access Tables & Databases
    Replies: 2
    Last Post: 11-10-2016, 03:58 PM
  3. Trouble with Code - Adding New Record with Form does not function
    By JJFletcher in forum Excel Formulas & Functions
    Replies: 34
    Last Post: 09-13-2015, 11:22 AM
  4. User Form Data Entry Adding a TimeStamp
    By caltman242 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-14-2015, 09:15 AM
  5. [SOLVED] Verify that record dosen't exist on user form text box entry
    By jprlimey in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-16-2014, 05:23 PM
  6. Adding new rows based on data imput to modify form template
    By JediJoker in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 05-23-2013, 01:42 PM
  7. Replies: 2
    Last Post: 12-12-2010, 10:27 PM

Tags for this Thread

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