AutoNumber (Using Numbers and Letters)

[motoxracer400f]

Hi everyone,

I have a program that I have created in Access 2007 that stores all of our drawing numbers with a letter following the number to cover the revision level. For the longest time I've been wanting to add an auto number button on the form so that I don't have to find the last record and then create a new one each time...

Here is my situation:
1) I have a table called "QF Drawings"
2) Within that table there is a text box called "Q-F DWG #"
3) Normally I could just set the box to autonumber the text box, but since I am using a number followed by a letter, the autonumber doesn't work properly.
4) The number sequence that I am using is like this:
100101A, 100102A, 100103A, 100103B, 100104A, etc.
5) I have a button on my form called "AutoNumber"

How do I make it so when I click the "AutoNumber" button it searches the "Q-F DWG #" text field for the last number before the letter and creates a new auto number with an "A" after it. Ex: "100105A"

If anyone could help me out with this it would be extremely appreciated!
Thank You!!
--Jason V

 

[John Big Booty]

Have a look at the DMAX() Function. Also search this forum for subjects such as auto increment and the like.

 

[ChrisO]

Not too happy with Q-F DWG # as a Field Name but, when in Rome…

Public Function GetNextDrawingNumber() As Variant
    Dim vntOld As Variant
    Dim strNew As String
    
    vntOld = DMax("[Q-F DWG #]", "[QF Drawings]")
    
    [color=green]' Watch out for Nulls and Pass back Empty as default.[/color]
    If Not IsNull(vntOld) Then
        [color=green]' Determine Z to A rollover.[/color]
        If Asc(UCase(Right$(vntOld, 1))) > 89 Then
            [color=green]' Bump the number and go back to 'A' while preserving case.[/color]
            strNew = CStr(Val(vntOld) + 1) & Chr$(Asc(Right$(vntOld, 1)) - 25)
        Else
            [color=green]' Bump the letter while preserving case.[/color]
            strNew = CStr(Val(vntOld)) & Chr$(Asc(Right$(vntOld, 1)) + 1)
        End If
        
        [color=green]' Pass back the new String.[/color]
        GetNextDrawingNumber = strNew
    End If
    
End Function

Watch how you use it but hope that helps.

Regards,
Chris.

 

[motoxracer400f]

Thanks for the quick reply and it worked!!

I greatly appreciate your help!!