Data Entry Form - Update Existing Record

  • Hi All,

    As I am new to VBA, i have amalgamated code that I've found online to complete the required function (I hear the collective groans :P). I have data entry form that allows me to enter data into a worksheet as a record (cmAdd). I've managed to create a "Check" to ensure I'm not duplicating entries, but I am coming short when trying to get something that updates an existing record if it already exists (cmdClose) instead of adding another. Logically, I suppose I want to search for the existing record, and then if found, overwrite. I'm just not sure how to do so. I've tried to find the input value in the worksheet to define the row i'm writing to, but no success. Setting iRow for sub CmdClose is where I'm getting the problem, though I could be completely off here, as I don't have any coding experience, but see below code i've got so far. Any help would be greatly appreciated. And again, apologies for my coding/VBA illiteracy

  • From your code I assume that your worksheet has Row 1 as a header row and the data starts in cell A2, is that correct?

    Also, I assume the data can never be separated into blocks by an empty row.

    It should be possible to combine both your codes into a single code which would either update an existing record or add a new one.

    BTW No groan from here, what you have done by looking for code that you can adapt for your own use is the perfect way to learn VBA. What you have managed to achieve is impressive!

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • When it comes to learning VBA, "Any port in a storm". I actually learned how to doo my first User Form from Tiger Spreadsheet Solutions on the You Tubes. :facepull:

  • Was I correct in first 2 assumptions in my last post?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox,

    Thanks for the response! I think I've actually managed to get the update to work. I'll post the code when I'm back at a computer. I've come up against another problem though. My check is meant to validate that the content in column D and column J both exist (if a record contains both of these, it should be unique) for that specific row and match the input of the data form, but it appears it is just checking for those values in both columns in any row eg. It will give the prompt message to update instead if there's the matching values in eg. D7 and J9, when in fact it should only return a response when both values are in the same row eg. D7 and J7. I've used a Countif function for this check as per code.

    Thanks again for the quick responses on this. I feel like I'm almost there! :)

  • OK, I will look into doing a single procedure that will either update a record (after checking with user that an update is required) or add a new record.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks for your help KjBox.

    I thought I'd resolved the update function with the below code, changing iRow, But this will only be searching for the 1 input from the form (me.cboPart.Value), when I need it to search for 2 criteria in 2 rows. ie search for Entries (rows) that contain both me.cboPart.Value (in range D:D) and me.cbo.Combobox1.Value (in range (J:J))

    Apologies, but in respect to your question about the data starting in A2, this is correct in that there is an excel formula (concatenate) in this column that populates depending on the input of data from B2 onwards, if that makes sense. This is to create a unique case string for each record.

    Below is the code i have for the check function. As stated in earlier post, this appears to be checking both columns and returning the message if it finds either in ANY row of the columns stipulated, when i want to to only return the message when these values are both present in the same row.

  • Hi KjBox,

    I've managed to get it to work. I created a column that concatenated the the two required cells in the data worksheet ("PartsData") in column R, and then created a string variable in VBA that was a concatenation of the two form inputs required (CheckRow = Me.cboPart.Value & Me.ComboBox1.Value). I used an If else with this string variable and the find function to determine the row to write the data to. See the code below (I've also made some minor aesthetic amendments like extra form boxes etc). I'm quite happy with this (and myself) now, considering its my first foray into VBA. I hope you haven't started looking at this, as I don't want to waste your time. If you do see issues with the below code though, any recommendations would be appreciated.

    Add function with Check

    Update Function

    Thanks again,


Participate now!

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