none
Excel 2010 - Userform - VBA How to stop ‘Job No’ from duplicating itself on next empty row RRS feed

  • Question

  • Hi there
    Thank you in advance for taking the time to check this out.

    Objective:
    To prevent duplication of incident numbers in the datasheet, and format the job number with a prefix of Inc- at the beginning. I currently have the cell customization set to “Inc”General but that only inserts the prefix in the cells on the datasheet, but is not showing in the disabled textbox in the userform.

    The Problem
    I have a ‘Job Number’ that is generated each time the form is opened and when the ‘Save’ button is clicked the data from the form is transferred over

    The job number is generated from the previous entry +1 (auto incrementing the old fashioned way).
    The problem arises when the ‘Save’ button is pressed repeatedly, the same job number and data is duplicated on the datasheet.

    Is there some way to ensure that the number generated is unique, and if the ‘Save’ button is repeatedly pressed that it will just over-ride the existing information?

    The number format currently used is 20150003 (incremented by 1). But what I’d like to be displayed in the form is Inc- 20150003

    The following code is in the form_initialize procedure.

     Me.txtSEC_INC_No.Enabled = True
                               Dim irow As Long
                               Dim ws As ws_Incident_Details
                               Set ws = ws_Incident_Details
                               
                               'find last data row from database'
                               irow = ws.Cells(Rows.Count, 1) _
                               .End(xlUp).Row
                            
                               If ws.[a2].Value = "" Then
                                   Me.txtSEC_INC_No.Text = 0 ' If no value in Col A, it will return a 0
                                                            
                               Else
                                   Me.txtSEC_INC_No.Text = ws.Cells(irow, 1).Value + 1
                               End If
     

    I’d be really grateful if someone could help me out, or perhaps direct me to where I might find some coding that will achieve the result I am seeking.

    The my sample form  is linked to my Dropbox so you can see how it currently works (or doesn't work) .

    With much gratitude,
    TheShyButterfly





    Hope you have a terrific day, theShyButterfly

    Tuesday, March 31, 2015 7:04 AM

Answers

  • Hi,

    When you repeat click the "Save"button, your txtSEC_INC_No.Value didn't refreshed. You may need to refresh this control's value at last or save the latest value  :

              txtSEC_INC_No.Value = txtSEC_INC_No.Value + 1

    or

             Cells(lastrow, 1).Value = 'Cells(lastrow-1, 1).Value +1    'Column A

    Hope this could help you

    Best Regards


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Wednesday, April 8, 2015 1:54 AM
    Monday, April 6, 2015 9:26 AM
    Moderator

All replies

  • I'm sorry ... the sample file I linked to is not up to date :( missing some core elements.

    I have just uploaded the latest version Up to date sample form

    Apologies.

    Cheers,

    TheShyButterfly


    Hope you have a terrific day, theShyButterfly


    • Edited by theShyButterfly Tuesday, March 31, 2015 8:05 AM Can I get my question deleted please - I have posted it in the proper forums ... sorry
    Tuesday, March 31, 2015 7:23 AM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Wednesday, April 1, 2015 6:07 AM
  • Hi,

    When you repeat click the "Save"button, your txtSEC_INC_No.Value didn't refreshed. You may need to refresh this control's value at last or save the latest value  :

              txtSEC_INC_No.Value = txtSEC_INC_No.Value + 1

    or

             Cells(lastrow, 1).Value = 'Cells(lastrow-1, 1).Value +1    'Column A

    Hope this could help you

    Best Regards


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Wednesday, April 8, 2015 1:54 AM
    Monday, April 6, 2015 9:26 AM
    Moderator
  • Thank you Lanlan :)

    I've made the change as per your sample above :)

    Thank you again

    Cheers,

    TheShyButterfly


    Hope you have a terrific day, theShyButterfly

    Friday, April 17, 2015 11:05 AM