Unique sequential ID #

[abublitz]

Hello everyone,

I am working on a database for my company for complaints. This is my first time working with access. I've been trying, and failing to find a good way to create a unique, sequential ID# for the complaint records. I've done a bunch of googling but haven't really found an answer I can understand, or one that will work for what I want it to do. If anyone could assist me in figuring this out, I would greatly appreciate it.

I've included a copy of my database, it's not quite done yet but you will get the idea. On my Complaint form, I want the unique, sequential ID# to auto populate in the Complaint# text box. The box has enabled set to No so users are unable to edit it. The number format needs to be Cyy-XXXX, where yy is the current year and xxxx is the auto number. For example, C16-0001. I've tried to do this with access built in auto number and a prefix for the Cyy but that didn't really give me what I needed. The YY wouldn't roll over to 17 in the new year and it wasn't overly friendly in my search box I added either.



I realize that it's pretty for there to be gaps in a sequence in access. My plan to offset that is to make it so records cannot be deleted (is that possible?) and to incorporate a void checkbox. So that unused records are there, but easily filtered out in reports.

Does anyone have any ideas for how I can code this? (Also, I am using access 2003)

Have issues getting the uploader to work... https://www.dropbox.com/s/u4cjjsrtxa...0Copy.mdb?dl=0

[CJ_London]

My plan to offset that is to make it so records cannot be deleted (is that possible?)

it is genrally a good idea not to delete records - but use a void flag as you thought, but it won't stop gaps appearing in autoID. As a test open a table, start to enter a new record - the autonumber is populated. Now hit the esc key and abandon the entry. Next start to enter a new record, the autonumber is populated, but not with the previous number.

You need to store your unique key in 2 or more fields - one for "C", one for year (default value =format(date(),"yy") and one for your incremental number. For this use what is commonly referred to as DMax+1 which is

Dmax("numberfield","mytable","yrField='" & format(date(),"yy"))+1

and should be calculated when the table is updated, not when a new record is being appended.

But be aware that this can be insufficient in a multi user environment if two users append at the same time, they may pick up the same number. Instead consider creating the record as soon as you start to enter a new record, and then update - this at least gives you the chance (in code) to assign another number if required.

Personally, I would keep the code simple, do users really want to by typing in C16-XXXX? you can always format the year basis from the complaint date for example and if C means complaint and that's all you are handling then why bother with storing C?

[ssanfu]

I looked at your dB and noticed there are things you should fix:

Shouldn't use spaces, special characters (exception is the underscore) or punctuation on object names
In table "Model",
"Part#" - has the hash mark. Better would be "PartNum"

In table "Complaint Database", (has space):
"City,State" - has a comma. Better would be "CityState" or "City_State"
"Lot/SerialNum" - has a slash. Better would be "LotSerialNum" or "Lot_SerialNum"
"CAPA#" - has the hash mark. Better would be "CAPANum"

"Description" (in table "Model") and "Index" (in table "Model") are reserved words in Access and shouldn't be used for object names.

"Description" is not very descriptive - I would use "PartDesc"
Instead of "Index", I would use "ComplaintID_PK".


About modules:
The top two lines in EVERY module should be

Option Compare Database
Option Explicit

-------------------------------------------------

I couldn't get the DMax() function to return a value (many problems), so I wrote a UDF.

Here is the code:

Private Function fnCreateComplaintNum() As String
    Dim d As DAO.Database
    Dim r As DAO.Recordset
    Dim sSQL As String
    Dim sComplaintNum As String

    Set d = CurrentDb

    'define query
    sSQL = "SELECT Max(Right([ComplaintNum],3)) AS MaxNum"
    sSQL = sSQL & " FROM [Complaint Database]"
    sSQL = sSQL & " GROUP BY Left([ComplaintNum],2)"
    sSQL = sSQL & " HAVING Left([ComplaintNum],2) = " & Format(Date, "yy") & ";"
    'Debug.Print sSQL

    Set r = d.OpenRecordset(sSQL)
    'check for records
    If Not r.BOF And Not r.EOF Then
        'record found
        sComplaintNum = Format(Date, "yy") & "-" & Format(Val(Nz(r("MaxNum"), "0")) + 1, "000")
    Else
        'record not found
        sComplaintNum = Format(Date, "yy") & "-" & "001"
    End If

    fnCreateComplaintNum = sComplaintNum

    'clean up
    r.Close
    Set r = Nothing
    Set d = Nothing

End Function

To use it you would have:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.ComplaintNumfield = fnCreateComplaintNum
End Sub

I think it would be better to use the "Form_BeforeUpdate" event rather than the "Form_BeforeInsert" event.


And, I modified the complaint form. I added a tab control, moved controls to their own tab and shortened the form........


I am attaching the dB, but I'm not sure that you will be able to open it because I have A2010 on this confuser; I have A2K at home.

[Micron]

Only once did I ever need such a thing. I used the number of seconds elapsed since the date the db was put into production. If, Heaven forbid, two users saved their record at the EXACT same second, the function tried again until it generated a unique number. Lest you think the routine would run out of space for the single data type, my calculations showed that it is more likely that mankind doesn't have that many days left.