Auto incrementing letter part of an alphanumeric key

A

AmyAimee

Registered User.
Local time
Today, 02:14
Joined
Oct 5, 2011
Messages
23
This function is supposed to auto generate an alphanumeric primary key.

My form is named F_Claim and has a textbox named noOfClaims that determines the number of keys to be generated.

In case of noOfClaims = 1 the format of the key would be as follows ("CLM/GC/0001/2010")

While in case of noOfClaims > 1 the format of the key should be ("CLM/GC/0001/2010A"),("CLM/GC/0001/2010B"),("CLM/GC/0001/2010C") and so on by keeping the autonumber fixed and incrementing the letter only.

I have to mention that the table T_M_AutoGenerate has 3 columns, [Type], [LastNumberAssigned] and [Letter].

[Type] stores "GC"
[LastNumberAssigned] stores the 0001 part of the claimNumber
[Letter] stores the letter 'A' to be incremented in case of noOfClaims > 1

The function works perfectly when the noOfClaims = 1, but it fails when the noOfClaims > 1

here's my code

Code: 
Function NewClaimNo(pValue As String, noofClms As Integer, year As Integer) As String

    Dim db             As Database
    Dim LSQL           As String
    Dim LUpdate        As String
    Dim LInsert        As String
    Dim Lrs            As DAO.Recordset
    Dim LNewClaimNo   As String
    
    Dim CLM As String
    Dim Slash As String
    Dim sLetter As String
    
    On Error GoTo Err_Execute
           
    Set db = CurrentDb()
    
    CLM = "CLM"
    Slash = "/"
    sLetter = "A"
    
    
    'Retrieve last number assigned for BoxesReceived
    LSQL = "Select * from T_M_AutoClaimNo"
    LSQL = LSQL & " where GroupTypeAbb = '" & pValue & "'"
    
    Set Lrs = db.OpenRecordset(LSQL)
    
    '****** if no of claim = 1
    
    'If no records were found, create a new pValue in the T_M_AutoClaimNo table
    'and set initial value to 1
    If Lrs.EOF = True Then
    
        LInsert = "Insert into T_M_AutoClaimNo (GroupTypeAbb, LastNumberAssigned)"
        LInsert = LInsert & " values "
        LInsert = LInsert & "('" & pValue & "', 1)"
        
        db.Execute LInsert, dbFailOnError
        
        'New Claim No is formatted as "CLM/GC/0001/", for example
        LNewClaimNo = CLM & Slash & pValue & Slash & Format(1, "0000") & Slash & year
        
    Else
        'Determine new ClaimNo
        'New ClaimNo is formatted as "CLM/GC/0001/", for example
        LNewClaimNo = CLM & Slash & pValue & Slash & Format(Lrs("LastNumberAssigned") + 1, "0000") & Slash & year
        
        'Increment counter in T_M_AutoClaimNo table by 1
        LUpdate = "Update T_M_AutoClaimNo"
        LUpdate = LUpdate & " set LastNumberAssigned = " & Lrs("LastNumberAssigned") + 1
        LUpdate = LUpdate & " where GroupTypeAbb = '" & pValue & "'"
        
        db.Execute LUpdate, dbFailOnError
        
    End If
    
   'if no of claim > 1
   If noofClms > 1 Then
   
   
     'If no records were found, create a new pValue in the T_M_AutoClaimNo table
    'and set initial value to 1
    If Lrs.EOF = True Then
    
        LInsert = "Insert into T_M_AutoClaimNo (GroupTypeAbb, LastNumberAssigned, Letter)"
        LInsert = LInsert & " values "
        LInsert = LInsert & "('" & pValue & "', 1, '" & sLetter & "')"
        
        db.Execute LInsert, dbFailOnError
        
        'New Claim No is formatted as "CLM/GC/0001/A", for example
        LNewClaimNo = CLM & Slash & pValue & Slash & Format(1, "0000") & Slash & year & Lrs("letter")
        
    Else
        'Determine new ClaimNo
        'New ClaimNo is formatted as "CLM/GC/0001/A", for example
        LNewClaimNo = CLM & Slash & pValue & Slash & Format(Lrs("LastNumberAssigned"), "0000") & Slash & year & Lrs("letter")
        
        'Increment counter in T_M_AutoClaimNo table by 1
        LUpdate = "Update T_M_AutoClaimNo"
        LUpdate = LUpdate & " set LastNumberAssigned = " & Lrs("LastNumberAssigned")
      '  LUpdate = LUpdate & " set Letter = " & Lrs(Chr(Asc("Letter") + 1))
        LUpdate = LUpdate & " where GroupTypeAbb = '" & pValue & "'"
             
        db.Execute LUpdate, dbFailOnError
        
    End If
   
   End If
    
    Lrs.Close
    Set Lrs = Nothing
    Set db = Nothing
    
    NewClaimNo = LNewClaimNo
    
    Exit Function
    
Err_Execute:
    'An error occurred, return blank string
    NewClaimNo = ""
    MsgBox "An error occurred while trying to determine the next ClaimNo to assign."
    
End Function
 
Is the Autonumber field not sufficient? Format the field to have leading zeros, concatenate all three fields together and save it into the ClaimNo field.
 
The Auto number field is sufficient in case of noOfClaims = 1
the concatenating works perfectly as well

but the last part of the function where it should increment the letter field is not working

the code below doesn't increment the letter field in my table as it should have the following format
("CLM/GC/0001/2010A")
("CLM/GC/0001/2010B")
("CLM/GC/0001/2010C")


Code: 
 Else
        'Determine new ClaimNo
        'New ClaimNo is formatted as "CLM/GC/0001/A", for example
        LNewClaimNo = CLM & Slash & pValue & Slash & Format(Lrs("LastNumberAssigned"), "0000") & Slash & year & Lrs("letter")
        
        'Increment counter in T_M_AutoClaimNo table by 1
        LUpdate = "Update T_M_AutoClaimNo"
        LUpdate = LUpdate & " set Letter = " & Lrs(Chr(Asc("Letter") + 1))
        LUpdate = LUpdate & " where GroupTypeAbb = '" & pValue & "'"
             
        db.Execute LUpdate, dbFailOnError
        
    End If
 
It won't reach Z, the maximum noOfClaims in normal cases could be 10 (could reach J) as the users claimed

Therefore when the function gets noOfClaims > 1 it should automatically start incrementing the letter field in the table, which the current code doesn't do
 
Alright to get an increment of the alphabets, here's the code:
Code: 
Ucase(Chr(Asc(Nz(DMax("Right([[COLOR=Red]ClaimNo[/COLOR]], 1)", "[COLOR=Red]TableName[/COLOR]"), 96)) + 1))
Where, I'm assuming, ClaimNo is the name of the field containing the full concatenated.
 
Oops... I just noticed that you mentioned that there's [Letter] field. Replace Right([ClaimNo], 1) with [Letter]. You might need a WHERE clause in the DMax too.
 
Is it possible to use the code you provided to update my table (In SQL)?

because from what i've noticed, the problem is actually in the statement below

Code: 
LUpdate = LUpdate & " set Letter = " & Lrs[COLOR="Red"](Chr(Asc("Letter") + 1))[/COLOR]

Where I have to replace the statement in red with a code that would increment the letter field (the field that holds the alphabet letter) not the full concatenated field
 
The full code I gave you above is what you need, not what you've written.

Q: Where would you get the last saved letter from?
A: The table. Hence, you need the DMax() function

Q: How do you get the last saved letter for a particular customer/client?
A: By adding a WHERE clause in the DMax() function

Q: How do you ensure that the letter is UPPERCASE?
A: By using the UCase() function.

So, as already advised, the function above meets your requirements.
 
The code you are creating is not ideal as a Primary Key. Long strings are relatively slow for joins.

Moreover it contains a lot of redundent elements such as CLM, GC and the slashes. The full version of the claim number should be created only for display but not stored.

I would not use an autonumber for something the user sees. You cannot guarantee an unbroken sequuence and it is hard to fix if it goes wrong and skips.

BTW Type and Year are reserved words and best avoided.
 
Galaxiom, You're right, this could not be ideal, that's why I tried to avoid it by developing constraints to control the user's input and it worked somehow.

Thanks for bringing the reserved words issue to my attention. I'll work on the redundant elements issue once I get the increment problem solved.


vbaInet, Where should I put the code you gave? I'm kinda lost, especially that the code works fine as I mentioned earlier, untill it reaches the update statement.
 
So what about the slow aspects of this method that Galaxiom mentioned?

It should go in the Default Property of the textbox. But before you do, I would like to see exactly what you've written.
 
Well, maybe I should have given more details, mentioning that I already have a function named "CreateClaimNo" that will insert the created key by the "NewClaimNo" function into a table named 'T_M_Claims'- Which is in the same time a subform in my main form.

My main form has a one to many relationship with its subform and it contains clientName textbox, NoOfClaims textbox and a button that calls "CreateClaimNo" function. (Works perfectly as well).

The key to be generated, which is in reality a claim number would be in the format (CLM/GC/0001/2010) if a client has only one claim and in the format (CLM/GC/0001/2010A),(CLM/GC/0001/2010B) .. in case the same Client has 2 claims and so on for (multi-Claims).

Here where the idea of incrementing the alphabet came from, different claim numbers for the same Client. Professionally it should be ideal.

Therefore all is left is incrementing the alphabet letter using the NewClaimNo function itself, that's why I was looking for a code within the function not the texbox.
 
I think we understood your problem quite alright. It's not the first time we've been asked about auto incrementing.

Whether the code is placed in the Control Source or Expression Builder or in the VBA editor, it will work as normal. Like I mentioned in my post #7, you will need a WHERE clause in the DMax() function. That WHERE clause will enable you get the last entered letter that relates to the current customer. I suggest you have a quick read about the DMax()
 
I know that DMax() returns the maximum value in a set of records, but in my case I only have one record in my table


AmyAimee said:
I have to mention that the table T_M_AutoGenerate has 3 columns, [Type], [LastNumberAssigned] and [Letter].

[Type] stores "GC"
[LastNumberAssigned] stores the 0001 part of the claimNumber
[Letter] stores the letter 'A' to be incremented in case of noOfClaims > 1
Click to expand...
 
All cases have been dealt with in the function I gave you.

* No record in table
* 1 record in table
* > 1 record in table

Should I really explain what every single function I've used there does or are you going to test out the code given? The max between, A and C is C. The max of A (if there's only one record) is A.
 
Alright, I've added a where clause to the code

Code: 
Ucase(Chr(Asc(Nz(DMax("Letter)", "T_M_AutoClaimNo"), 96)) + 1)) where GroupType = "GC"

Now, in case the code above is correct, where should I place it? Default property of a which textbox? And if it could goes anywhere in the VBA editor where I'm having the function already, where exactly should I place it?
 
That's incorrect.

You should read about the DMax() function and see exactly where the third argument is. The third argument is the WHERE part (not clause) of the function:

http://www.techonthenet.com/access/functions/domain/dmax.php

Once you've read it, amend the code and repaste it here.

You can put it in the Default property of any textbox and precede it with an equal to sign (=), i.e. =UCase(...

In the VBA editor I don't know because I have no idea what your full function looks like.
 
Probably

Code: 
UCase(Chr(Asc(Nz(DMax("Letter", "T_M_AutoClaimNo", "GroupType = 'GC'"), 96)) + 1))

You can find my full function in post #1
here's it anyway

Code: 
Function NewClaimNo(pValue As String, noofClms As Integer, year As Integer) As String

    Dim db             As Database
    Dim LSQL           As String
    Dim LUpdate        As String
    Dim LInsert        As String
    Dim Lrs            As DAO.Recordset
    Dim LNewClaimNo   As String
    
    Dim CLM As String
    Dim Slash As String
    Dim sLetter As String
    
    On Error GoTo Err_Execute
           
    Set db = CurrentDb()
    
    CLM = "CLM"
    Slash = "/"
    sLetter = "A"
    
    
    'Retrieve last number assigned for BoxesReceived
    LSQL = "Select * from T_M_AutoClaimNo"
    LSQL = LSQL & " where GroupTypeAbb = '" & pValue & "'"
    
    Set Lrs = db.OpenRecordset(LSQL)
    
    '****** if no of claim = 1
    
    'If no records were found, create a new pValue in the T_M_AutoClaimNo table
    'and set initial value to 1
    If Lrs.EOF = True Then
    
        LInsert = "Insert into T_M_AutoClaimNo (GroupTypeAbb, LastNumberAssigned)"
        LInsert = LInsert & " values "
        LInsert = LInsert & "('" & pValue & "', 1)"
        
        db.Execute LInsert, dbFailOnError
        
        'New Claim No is formatted as "CLM/GC/0001/", for example
        LNewClaimNo = CLM & Slash & pValue & Slash & Format(1, "0000") & Slash & year
        
    Else
        'Determine new ClaimNo
        'New ClaimNo is formatted as "CLM/GC/0001/", for example
        LNewClaimNo = CLM & Slash & pValue & Slash & Format(Lrs("LastNumberAssigned") + 1, "0000") & Slash & year
        
        'Increment counter in T_M_AutoClaimNo table by 1
        LUpdate = "Update T_M_AutoClaimNo"
        LUpdate = LUpdate & " set LastNumberAssigned = " & Lrs("LastNumberAssigned") + 1
        LUpdate = LUpdate & " where GroupTypeAbb = '" & pValue & "'"
        
        db.Execute LUpdate, dbFailOnError
        
    End If
    
   'if no of claim > 1
   If noofClms > 1 Then
   
   
     'If no records were found, create a new pValue in the T_M_AutoClaimNo table
    'and set initial value to 1
    If Lrs.EOF = True Then
    
        LInsert = "Insert into T_M_AutoClaimNo (GroupTypeAbb, LastNumberAssigned, Letter)"
        LInsert = LInsert & " values "
        LInsert = LInsert & "('" & pValue & "', 1, '" & sLetter & "')"
        
        db.Execute LInsert, dbFailOnError
        
        'New Claim No is formatted as "CLM/GC/0001/A", for example
        LNewClaimNo = CLM & Slash & pValue & Slash & Format(1, "0000") & Slash & year & Lrs("letter")
        
    Else
        'Determine new ClaimNo
        'New ClaimNo is formatted as "CLM/GC/0001/A", for example
        LNewClaimNo = CLM & Slash & pValue & Slash & Format(Lrs("LastNumberAssigned"), "0000") & Slash & year & Lrs("letter")
        
        'Increment counter in T_M_AutoClaimNo table by 1
        LUpdate = "Update T_M_AutoClaimNo"
[COLOR="Red"]        LUpdate = LUpdate & " set Letter = " & Lrs(Chr(Asc("Letter") + 1))[/COLOR]
        LUpdate = LUpdate & " where GroupTypeAbb = '" & pValue & "'"
             
        db.Execute LUpdate, dbFailOnError
        
    End If
   
   End If
    
    Lrs.Close
    Set Lrs = Nothing
    Set db = Nothing
    
    NewClaimNo = LNewClaimNo
    
    Exit Function
    
Err_Execute:
    'An error occurred, return blank string
    NewClaimNo = ""
    MsgBox "An error occurred while trying to determine the next ClaimNo to assign."
    
End Function
 
AmyAimee said:
Probably

Code: 
UCase(Chr(Asc(Nz(DMax("Letter", "T_M_AutoClaimNo", "GroupType = 'GC'"), 96)) + 1))
Click to expand...
That's correct Amy (or Aimee :)), well done!

So on testing the code, does it give you the next letter for GC? All that's needed now is to concatenate the rest of the static values to this letter.

Your code is an overkill for something this trivial plus there's a major flaw in the code too, there's no sorting. We can replace your code in just one line. Just let me know the outcome of the above.
 
You must log in or register to reply here.

Similar threads

S
Function to keep numeric value 4 digits
Replies
7
Views
720
pbaldy
pbaldy
C
Auto-incrementing id number, that resets to 1 each year, with a year prefix
2
Replies
20
Views
4,487
jai kushwaha
J
S
Auto Generate Number Error
2
Replies
24
Views
2,340
RainLover
RainLover
S
Function Update
Replies
2
Views
997
steve21nj
S
B
sequence number based on fiscal year
Replies
1
Views
2,137
Galaxiom
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom