Alpha auto ID

[mcinnes01]

Hi,

I am trying to write a function to automatically assign an ID like the way autonumber works, but with alphas. I want a 2 digit alpha assigned and I want to reuse and deleted like as if you compact the database everytime a record is removed.

The Table in this example is called division, it is made up of 2 columns, Division ID (which I want to populate with the alpha ID code) and Division.

I was thinking of opening the recordset in a function and ordering by the Division ID then running a loop that will check if the CHR value of righthand letter of the Division ID has a gap greater than 1 from the previous righthand letter of the Division ID's CHR value.

If it is, this will be the Division ID, if not it will generate the next Alpha ID.

Any ideas, I'm new with access and am currently stuck on ordering the record set and running a loop to check the gap between the CHR of the previous and current loops righthand division ID letter.

 

[mcinnes01]

I have got a little bit further, this is my testing so far.

Option Compare Database
Sub alphaID()
Dim x
Dim rs1 As Recordset
Dim Val1 As String, Val2 As String

Set rs1 = CurrentDb.OpenRecordset("SELECT [Division ID] FROM [DIVISION] ORDER BY [Division ID]")
Do While Not rs1.EOF
    Debug.Print rs1(0)
    Val1 = Asc(Right(rs1(0), 1))
    Debug.Print Val1
    rs1.MoveNext
    Debug.Print rs1(0)
    Val2 = Asc(Right(rs1(0), 1))
    Debug.Print Val2
        If Val2 - Val1 > 1 Then
            MsgBox ("Missing value = " & Left(rs1(0), 1) & Chr(Val1 + 1))
        End If
        
        
Loop
    

End Sub

 

[mcinnes01]

Ok so I have got a little further and can now handle when the previous last letter is a "Z".

This also now creates the next new record if there are no gaps, but my problem now is how do I turn it in to a function that will add a record to the table whether its filling a gap or adding a totally new record?

Option Compare Database
Sub alphaID()
Dim x
Dim rs1 As Recordset
Dim ValA1 As String, ValA2 As String, ValB1 As String, ValB2 As String
 
Set rs1 = CurrentDb.OpenRecordset("SELECT [Division ID] FROM [DIVISION] ORDER BY [Division ID]")
Do While Not rs1.EOF
    ValA1 = Asc(Left(rs1(0), 1))
    ValA2 = Asc(Right(rs1(0), 1))
    'Debug.Print ValA1 & " & " & ValA2
    rs1.MoveNext
    On Error Resume Next
    'Debug.Print rs1(0)
    If Err.Number = 3021 Then
        If ValA2 = 90 Then
            MsgBox ("New Value = " & Chr(ValA1 + 1) & Chr(65))
            GoTo exits
        Else
            MsgBox ("New Value = " & Chr(ValA1) & Chr(ValA2 + 1))
            GoTo exits
        End If
    End If
    ValB1 = Asc(Left(rs1(0), 1))
    ValB2 = Asc(Right(rs1(0), 1))
    'Debug.Print ValB1 & " & " & ValB2
    If ValA2 < 90 And ValB1 - ValA1 > 0 Then
        MsgBox ("Missing value = " & Chr(ValA1) & Chr(ValA2 + 1))
        GoTo exits
    Else
        If ValA2 = 90 And ValB2 > 65 Then
            MsgBox ("Missing value = " & Chr(ValA1 + 1) & Chr(65))
            GoTo exits
        Else
            If ValB2 - ValA2 > 1 Then
                If ValA2 + 1 > 90 Then
                    MsgBox ("Missing value = " & Chr(ValA1 + 1) & Chr(65))
                    GoTo exits
                Else
                    MsgBox ("Missing value = " & Chr(ValA1) & Chr(ValA2 + 1))
                    GoTo exits
                End If
            End If
        End If
    End If
Loop
 
exits:
rs1.Close
Exit Sub
End Sub

 

[gemma-the-husky]

what you OUGHT to do is this.

STILL have an autonumber PK in this table to use as FK in other tables

NOW add another indexed datafield in the main table to manage this bit if data, that you are talking about, so it is independent of the PK/FK. You will find it MUCH easier to manage.

So can you give examples of what this field looks like, and how you want it incrementing/filling.


---
in this case one way MIGHT be to have a master list of all possible values - then when you need a value, check down the list until you find the first blank. You could probably get this with a combination of an unmatched, and dmin query - but even if you have to examine every item, it will only take microseconds.

 

[Galaxiom]

A simpler and considerably faster way is to maintain the value from the sequence in the table as an integer and convert to alpha only for display. This vastly simplifies the loop through the recordset that does the detection of the first gap.

The conversion to alpha is a relatively simple expression:

AlphaID: Chr((seqnum\26) + 65) & Chr((seqnum MOD 26) + 65)

The backslash is the Integer Divide operator and returns the integer part of the division. MOD is the Modulo operator that returns the remainder of the division.

65 is the ASCII code for capital A and the capitals through to Z follow in sequence. Consequently A will be derived in the first position for values up to 26, B for 27 to 52 and so on. Similarly for the second position.

I made a similar suggestion for a mixed alpha-number system in post #4 of this thread.

 

[gemma-the-husky]

clever idea, G

I don't think I will need it, but you never know.

 

[Galaxiom]

I don't think I will need it, but you never know.

Me either but we do seem to get a lot of requests for mixed alpha-number codes and this technique does simplify the generation of the next code.

It also allows a DMax or allocated primary key to be used and the alpha code treated as a derived value.

Not sure the concept is being well appreciated though as many seem committed to the idea of their aplha code being used as the Primary Key.

 

[mcinnes01]

Hi,

Thanks for all the suggestions I will see if I can work it in to my code to shorten it a little.

This is what I have so far and it seems to work well, I don't know if you can see any problems in it? One question regarding the use of an auto number as a reference point...

I understand the idea behind it e.g. 000001 = AA, 000032 = BF etc, however wouldn't that mean I need a way of compacting the database before every record add?

I would gladly have some feedback on my idea so far and I will have a go at working mod it to the code.

Thanks again,

Andy

Option Compare Database
Sub alphaID()
On Error GoTo HandleMyError
Dim x
Dim rs1 As Recordset
Dim ValA1 As String, ValA2 As String, ValB1 As String, ValB2 As String

Set rs1 = CurrentDb.OpenRecordset("SELECT [Division ID] FROM [DIVISION] ORDER BY [Division ID]")
Do While Not rs1.EOF
    ValA1 = Asc(Left(rs1(0), 1))
    ValA2 = Asc(Right(rs1(0), 1))
    Debug.Print rs1(0)
    rs1.MoveNext
    ValB1 = Asc(Left(rs1(0), 1))
    ValB2 = Asc(Right(rs1(0), 1))
    If ValA2 < 90 And ValB1 - ValA1 > 0 Then
        y = " & Chr(ValA1) & Chr(ValA2 + 1)"
        GoTo AddRec
    Else
        If ValA2 = 90 And ValB2 > 65 Then
            y = Chr(ValA1 + 1) & Chr(65)
            GoTo AddRec
        Else
            If ValB2 - ValA2 > 1 Then
                If ValA2 + 1 > 90 Then
                    y = Chr(ValA1 + 1) & Chr(65)
                    GoTo AddRec
                Else
                    y = Chr(ValA1) & Chr(ValA2 + 1)
                    GoTo AddRec
                End If
            End If
        End If
    End If
Loop
    
exits:
rs1.Close
Exit Sub
HandleMyError:
   If Err.Number = 3021 Then
        If ValA2 = 90 Then
            y = Chr(ValA1 + 1) & Chr(65)
            GoTo AddRec
        Else
            y = Chr(ValA1) & Chr(ValA2 + 1)
            GoTo AddRec
        End If
            GoTo exits
    End If
AddRec:
rs1.AddNew
Debug.Print "answer = " & y
        rs1.Fields("Division ID").Value = y
        rs1.Update
            GoTo exits
End Sub

 

[gemma-the-husky]

I think the biggest problem is infilling gaps.

If you have records

AA01
AA02
AA04
AA05
AA06

then it's one thing finding the next number, AA07, and quite another finding a gap AA03, and using that. Trying to fill gaps makes little sense to be honest.

And no, none of these methods will need the database to be compacted. It's just a matter of decidng exactly what you are trying to do, the best way of storing the values, and the best way of getting the next item.

Galaxiom is suggesting you have 2 little functions so that, instead of storing AA32, you have one that turns it into a number for storage, and one that turns it bak again for presentation. The reason being that numbers are much more efficient for Access than text.

 

[mcinnes01]

Hi Dave,

Thanks for the explination there, one question would this still apply if I am only using alphas.

E.g. My ID codes will be unique in the format as follows, the idea is that I have a 6 digit field in our HR system for job codes. The jobs are very unique and hold alot of data. E.g. not simply job title but division, manager, expense authorisor, timesheet authorisor, contract, pension scheme, GL code area, GL code Job etc etc etc. Theres about 20 components that define a job and also anyone who is an authorisor or manager must be on their own job.

So what I have done is broken the job code into 3 component groups. Division, Authorisation and Job title and other variable GL contract etc.

The job code max field size is 6 charactors and we have 6000 staff so there will be about 1500ish jobs.

The first 2 charactors are the division which is what I am working on now. We have around 400 division so this mean numberics was out of the question hence the use of alphas. So the idea is that I assign an alpha ID to each component group of the job code, and each of these groups has a table on my database. (Division,Authorisation,Job) I then have another table (Job Codes), this will then have the ID's of each of the components in it and then I will have a filed called job code which will be a concatenation of these 3 ID's therefore building my job code.

The idea of my database is to allow the HR staff to quickly, easily and accurately enquire on what job code to use by filling out a simple form. Also any new job codes can be easily built and I plan on getting it so the output will copy the data into our import spreadsheet so I can quickly and easily import any new job code builds.

So the Job code component IDs should be as follows:

Division   Authorisation    Job
AA         AA                 AA
AB         AB                 AB
AC         AC                 AC

etc

So for example a HR advisor would have a job of

FSDREF

 

[gemma-the-husky]

the nice thing about numbers is that you can add 1 to get the next item.

so if the last reference was 12345, its easy to say the bnext reference becomes 12346.

With text, it's far harder. If you know the last reference was FSDREF - how do you calculate the next reference. You can't add 1 to a text string.

-----
OK - you may THINK your users want to enter codes. What will happen when you come to deisgn the interface is that you will find better ways of doing this, using some drill down features

first give the users a list of divisions, and let them pick one. Based on that, and maybe further selections, you eventually get them to a list of jobs. They don't need any codes at all. It's all done by inspection.

You shouldn't think you must have a two digit alpha-code, because you have 400 divisions. You can have a much simpler numeric code. Then just present the divisions to the users in alphabetically order, with a combo box. They find the division they need by inspection, (and the secretly within access), it uses the division number to find other information it needs.

Try setting up a balnk form. Put a combo box on based on the divisions table, and you will see how it works.