Solved

Alphanumeric Record ID in Access Table

Posted on 2013-10-25
19
919 Views
Last Modified: 2013-12-30
I need to create an alphanumeric record ID for my records in my table. I need to have it set up like this:

If the combo box on my form has a value of "D" then the beginning of it should start with a "D", or if the combo box has a value of "F" then the beginning of it should start with a "F".

Then the next 2 digits should be the last 2 digits of the year, 2013 so it will be "13".

Then I need a "-".

Then a sequential numbering system for the next 4 digits. 0001

So the record id would look something like this as an example: D13-0001 and the last 4 digits would increment but when the year changes to 2014 then the 4 digit numeric will reset and start from 0001 with an example being: D14-0001.

Any ideas how I can set this up?
0
Comment
Question by:Lawrence Salvucci
  • 6
  • 6
  • 5
  • +1
19 Comments
 
LVL 57
ID: 39600099
Well first, I'd be remiss if I didn't say this was a bad idea.   You really don't want to be building meaning into a key.  Key's should be derived from the data itself.  

 For example, what does the 0001, 0002 represent?  Date/Time entered into the system?  Then record the date/time entered.   What happens if you get more then 9,999 of a type in one year?   etc.

 But, if you really wanted to, in the BeforeUpdate event of a form, after your sure your going to save the record, and generate the value.

 How you do that is a bit problematic.    The D/F is easy, but the numerical portion is not.  You can use DMax() on a set of records for the current year, but in a multi-user situation, it's quite possible for to users to get the same value.

 So if you go that route, you need a re-try on your save.

  The other approach is to use a Key generation routine, which saves the last value assigned.   By doing this, you can lock the key table and ensure that no two users get the same value.

 However your wrinkle of resetting at the start of each year is a problem as someone would need to go into the key table on 1/1 and reset the value.

 To avoid that, you'd need to have one key record per year.  At the start of the new year, if a record didn't exist, you would create one.

Jim.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39600180
I'm not going to store this as the primary key. That is going to be just an autonumber. This would be a separate field in my table.

The 0001, 0002 represents just a sequential number. There is no special meaning behind it. There is no way I would have more than 9,999 records so I'm not worried about that growing larger than that.

So this would all have to be created on the form when a new record is created. I wouldn't want this to be saved until the record is actually saved in the table.

I'm not sure I understand what you mean about the key generation routine. Can you elaborate on that please?
0
 
LVL 57
ID: 39600256
<<The 0001, 0002 represents just a sequential number. There is no special meaning behind it.>>

 Then why not just use an autonumber?

<< There is no way I would have more than 9,999 records so I'm not worried about that growing larger than that>>

  All I can say after 29 years of development; famous last words<g>

<<I'm not sure I understand what you mean about the key generation routine. Can you elaborate on that please? >>

 A function procedure that you call which returns a key value for you to use.  See below.  Note that I wrote this many years ago (Access 2 days) and while I originally allowed for different key type (including the type your trying to generate now, which is case #2), I never added any additional logic.

Jim.

'
' Get a unique record key.
'
Function GetRecordKey(strTableName As String) As Variant

        Const Routine = "GetRecordKeys"
        Const Version = "1.0"

        Dim dbCurrent As Database
        Dim dbRemote As Database
        Dim tdfAttached As TableDef
        Dim strPath As String
        Dim rst1 As Recordset
        Dim rst2 As Recordset
        Dim lngKeyValue As Long
        Dim lngWait As Long
        Dim lngX As Long
        Dim intLockCount As Integer

10      On Error GoTo GetRecordKeyError

20      GetRecordKey = Null

30      Set dbCurrent = curDB()

        '
        ' First, get the key record
        '
40      Set rst1 = dbCurrent.OpenRecordset("tblCounterTable", dbOpenDynaset)
50      rst1.FindFirst "[TableName]= '" & strTableName & "'"

60      If rst1.NoMatch Then
          ' Not good.  Need an entry for the table to generate a key.
70        MsgBox "No key table entry."
80        GoTo GetRecordKeyExit
90      End If

        '
        ' Now Generate a key based on type.
        '
GetAKey:
100     Select Case rst1![KeyType]

          Case 1
            ' Key is simple numeric counter (ie xxxxxxx).  rst1.Edit
110         lngKeyValue = rst1![LastKeyValue] + 1
120         rst1![LastKeyValue] = lngKeyValue
130         rst1.Update
140         GetRecordKey = lngKeyValue

            'Case 2
            ' Key is a base + a numeric counter (ie.  ABAxxx)

150       Case Else
            ' Undefined key type
160         MsgBox "No key table entry."
170         GoTo GetRecordKeyExit

180     End Select

        '
        ' Got a key.  Does it need to be tested for unique?
        ' Note assumes a JET linked table.
        '
190     If (rst1![UniqueKey]) Then
200       Set tdfAttached = dbCurrent.TableDefs(strTableName)
210       strPath = tdfAttached.Connect
220       If strPath <> "" Then
230         If dbRemote Is Nothing Then
240           strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))
250           Set dbRemote = Workspaces(0).OpenDatabase(strPath, False, True)
260           Set rst2 = dbRemote.OpenRecordset(strTableName, DB_OPEN_TABLE)
270         End If
280       Else
290         Set rst2 = dbCurrent.OpenRecordset(strTableName, DB_OPEN_TABLE)
300       End If

310       rst2.Index = "PrimaryKey"
320       rst2.Seek "=", lngKeyValue

330       If Not (rst2.NoMatch) Then
340         rst2.Close
350         GoTo GetAKey
360       Else
370         rst2.Close
380       End If

390     End If

GetRecordKeyExit:
400     On Error Resume Next

410     rst1.Close
420     Set rst1 = Nothing

430     rst2.Close
440     Set rst2 = Nothing

450     dbRemote.Close
460     Set dbRemote = Nothing

470     Set dbCurrent = Nothing

480     Exit Function

GetRecordKeyError:
        'Table locked by another user
490     If Err = CNT_ERR_RESERVED Or Err = CNT_ERR_COULDNT_UPDATE Or Err = CNT_ERR_OTHER Then
500       intLockCount = intLockCount + 1
510       If intLockCount > 5 Then
520         GetRecordKey = Null
530         Resume GetRecordKeyExit
540       Else
550         DoEvents
560         DBEngine.Idle DB_FREELOCKS
570         lngWait = intLockCount ^ 2 * Int(Rnd * 20 + 5)
580         For lngX = 1 To lngWait
590           DoEvents
600         Next lngX
610         Resume
620       End If
630     Else
640       MsgBox "Unexpected error"
650       GetRecordKey = Null
660       Resume GetRecordKeyExit
670     End If

End Function
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39600272
I will side with Jim, but give it to you in a slightly different way.

All the values you are referring to can be "derived"
For example, the 2013 can come from the current date.
The "D" can come from the combobox

The 0001, 0002 represents just a sequential number. There is no special meaning behind it.
Then if this is meaningless, then why is it needed?
Besided if it is meaningless then you can get it from the autonumber field.
So you can create a simple query that combines all three fields to give you this "composite" field value
No code or systems are then needed to do this...

I understand the allure of having nice sequential numbers.
But remember, as records are deleted you will still end up with holes...

I even understand the desire to use this field to "Count" the records
For example: D-13-007 means that this is the 7th record "D", in 2013
...but here again, as records are deleted, using this field to "Count" records will give you incorrect results.
You can easily get a count with an expression roughly like this:
    Dcount("*","YourTable", "Category='D'" & " AND " & "Year=" & 2013)
(Or use a Group By query)

Again, all of this can be derived and no bespoke process is needed...

Or you should perhaps explain to us why this value is needed...?

JeffCoachman
0
 
LVL 57
ID: 39600354
or to put it another way, if the number is really meaningless, then why not:

D0001
D0002
F0003
D0004
F0005

Meaning you have your letter and a autonumber field that make up the key.  But, those are seperate and distinct values and each should be stored in their own fields.  The key would then be assembled from those two fields (a compound key).

 If you stored the above key as an actual field in of itself, then you'd have data in two places.  The key would then have meaning because of the letter alone even if the numeric sequence was meaningless.

That's what I was driving at about having meaning in the key.

And with that said, does including the letter actually matter in terms of identifying the record?  No, the autonumber value could stand on it's own.

So if you really don't care what nth "D" or "F" this is, then what you should have is simply this:

RecID - Autonumber - Primary Key
RecType - Text(1) - "D" or "F"
EnteredOn - Date/Time
....rest of fields.

 with the auto number here being meaningless.  That gives you the same thing as you want without any restrictions on number of records, etc.

 If you want to walk it through, just post the details on what it is your modeling and we'll figure it out.

Jim.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39600391
I can understand situations where an number that increments would be fine even if records are deleted.  The GERS retail system for instance uses this a similar setup.  Even though previous lines may get voided, the user can still identify an item as the nth item that was recorded on a ticket.  

You can use something like this:
Me.Combo & Year(Date()) & "-" & DMax("[RecID]","Table","[RecID] Like '" & Me.Combo & Year(Date()) & "*'")+1

Open in new window


Ron
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39600483
ok I'll try my best to explain my need for this. Let's forget about the primary key (auto number) field. That's not what I'm trying to manipulate. That is set up in my table as the PK with autonumber as the datatype.

I have a client that is creating test reports in this database and the test reports need to have a specific ID number to them. This is not the same as the primary key. I think that's where I confused everyone. Right now he is using excel and just typing them in manually in that format I mentioned in my first post. We are converting him to an access database and I need to create this test report # in the same format.

He has 2 divisions, one begins with a "D" and one with an "F". That's where the D & F come in to play. The 2 digits next are the year and then the final digits are just sequential numbers that he has been using and creating manually in excel. So I need to recreate this in the database to be stored in the table and to also print on the reports.

I may have explained it wrong from the beginning and I hope this clears up any questions you may have had. I just need to recreate this report id# numbering system on the form and store it in the database. The trailing digits need to be sequential but if a record is deleted it doesn't need to be replaced meaning the sequence can be broken if needed. That's not a critical part of this. I would just need to grab the last record and create the next sequential record from that. As for changing the sequence back to 0001 at the beginning of a new year, I do need that to happen though.

Does this clear up your questions?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39600584
Correction to my last post.  I forgot the nz function.

Me.Combo & year(DATE) & "-" & Nz(DMax("[RecID]", "Table", "[RecID] Like '" & Me.Combo & year(DATE) & "*'"), 0) + 1

Open in new window

Ron
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39600674
lsalvucci,

Ok, thanks for the detailed explanation...

It seems like you have some experts helping you with this already, so I will leave you with them..
;-)

Jeff
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 57
ID: 39600707
<<Does this clear up your questions? >>

 Yes and I can see why your sure these #'s won't overflow.   But everything Jeff and I said still holds true.  

<<I have a client that is creating test reports in this database and the test reports need to have a specific ID number to them. This is not the same as the primary key. I think that's where I confused everyone. Right now he is using excel and just typing them in manually in that format I mentioned in my first post. We are converting him to an access database and I need to create this test report # in the same format. >>

  The question is then, what purpose does this ID really serve?  If you've already got an autonumber PK, then there is no problem identifying each report uniquely.

  So that leaves presenting something familer to the user to identify the report.   Prehaps the the date/time the report was run would be a better way along with the divison stored as a field.   or maybe a comment or a title?  user that ran it?

 And certainly you would want the division stored as it's own field and not as part of any type of ID.

 Let me give you an example; I run a report, selecting D for the divison.   Run it, and now discover that I should have run it for F.    If you have the division stored as a seperate field, I could update that, but what about the ID, which is now "Dxxxx"?

 Or if you don't store the division as a sperate field and rely on the ID field alone, now you have to update part of a field.   Also, what if some decides their should be a division DA and DB?

 Been down this road many times and you often see this when converting a manual system over to a automated one.   Alpha-numeric ID's stored as a value are never a good idea.

 As far as Ron's suggestion, as I mentioned earlier, in a multi-user situation, DMax() is not workable.   Two users can get the same value.

Jim.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39600743
I already went down this road with him and he would prefer to use the same syntax he is currently using to keep things somewhat the same as they are now.

The ID only serves as a report identifier for his customers but he likes the syntax of it right now. You can quickly look at it and know what division, year, and total number of reports for any given year.

There wouldn't be any way a user would select the wrong division and then have to go back and change it afterwards. When a user selects the specific division in the combo box it will have specific fields for that report and vice versa for the other division. So there are fail safes in place to guard against this problem.

As for changing the division prefix down the road I don't this will be an issue either. He's been running with these names for 20+ years now so that shouldn't change either.

I agree that there are easier ways to identify the report/record but I typically like to give my clients what they want. I know this can be done, I just don't know how to create it. :)

Instead of storing these alpha-numeric ID's as a value couldn't it just be set up as a function on the form/report or even just a formula of sorts?

Larry
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 39600888
<<The ID only serves as a report identifier for his customers but he likes the syntax of it right now. You can quickly look at it and know what division, year, and total number of reports for any given year. >>

 Ah, so there is meaning in the ID.

<<I agree that there are easier ways to identify the report/record but I typically like to give my clients what they want. I know this can be done, I just don't know how to create it. :)>>

 Understand that, but by the same token, they also pay you to tell them what's in their best interest as well.   I know sometimes it's a very fine line between giving them what they want and what they should have.  I know you said you've been over this already with them, but it's not a good idea.  Sooner or later, it will turn around and bite them.   What was that old oil change ad slogan "Pay me now, or pay me later".   Later as in being not the oil like now, but a whole engine later.

<<Instead of storing these alpha-numeric ID's as a value couldn't it just be set up as a function on the form/report or even just a formula of sorts?>>

 Sure and that's been my point which may have been unclear; there is nothing stopping you from taking one or more fields and combining them to form a ID.  That's easily done in a query or as an index.  You could easily take the divison, add it to the autonumber and come up with this:

D0001
D0002
F0003
D0004
F0005

 You could also write an SQL statement to return a sequential number over a group:

D0001
D0002
D0003
D0004
F0001
F0002

 But when you actually store multiple pieces of data in the same field for any reason, it's never a good idea.

Jim.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39600916
If you are not in a multi-user environment then there is no problem with using the formula I gave you earlier.  If you are, then one way to prevent duplicate values is to include the user in the recordID.  For instance, if your program is used at 3 locations, your recordID can look like D131-0001 where the number after the year is the location identifier.

Ron
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39600925
Ron,

I'm going to test your formula but will the "year" portion of your code be just 2 digits or 4?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39600971
Your right, I forgot about that.  Here's a revision, but again, if you are in a multi-user environment, you should look at including the user in the RecordID:

Me.Combo & format(DATE,"yy-") & Nz(DMax("[RecID]", "Table", "[RecID] Like '" & Me.Combo & format(DATE,"yy-") & "*'"), 0) + 1

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39601028
Ron,

If I wanted to include the user in the formula, how would I set that up?
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 250 total points
ID: 39601096
That depends on the scenario and I'm not sure what yours is.  For instance we have many stores and whenever someone is logged on to the system we know their StoreCd and their EmpID.  So if there is a single computer assigned in each store for this system, then using the StoreCd would be fine.  However, if multiple users are allowed within each store, you would need to use EmpID instead.  

Here's a sample that incorporates the StoreCd:
Me.Combo & format(DATE,"yy") & [StoreCd] & "-" & Nz(DMax("[RecID]", "Table", "[RecID] Like '" & Me.Combo & format(DATE,"yy") & [StoreCd] & "*'"), 0) + 1

Open in new window

Ron
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39744187
Hi IrogSinta,

I have a followup question to your answer you provided me. Will this sequential number reset to 0001 when we switch to 2014? I was hoping it would do that but not sure if it will from your code. Can you just verify if it will and if not I can repost another question so I can award you points if you need to modify it.

Example:

13-0001
13-0002

And then when the year changes to 2014 it will reset to this:

14-0001
14-0002, etc
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39747708
Yes it will reset.

Ron
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now