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 IncGeneral 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 Id 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
Id 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