Formatting a Number (?)

[Judy]

I'm not sure if this is an appropriate title for this request, but I have a table with a text field ([ID]) as the primary key in which I'm storing a number code. The code is 4 spaces and begins with a letter and three digits following the letter (D004, for example). On a form, I would like to create a button to add a new record and assign the record the next sequential number (D005). Are there any suggestions on how I can get the button to assign the next sequential number code to the record? Thanks in advance for any help!

 

[Jack Cowley]

There are a number of ways to do this. One would be to have two fields. One to contain your prefix letter and an autonumber (primary key) with the format set to 000. For your form you can concatenate the two fields and show your ID as D003. Using Autonumber as the primary key will always give you a new number when you go to a new record and it will always be unique.

 

[Abby N]

Based on the information in your post, Jack's suggestion is the better one IMHO. But, in case there's something about your DB we're not aware of, here is a function that will do what you ask. It was made specifically for the conditions you described. So, if your ID format changes the function will no longer work. For example, changing to a 5 digit ID will give you an erroneous return. Furthermore, it has a set limit of Z999.

To use the function copy and paste the code below into a module. You can then use it pretty much like you would a DLookup except without criteria. i.e. SetID("[ID]","TableName")
You can use this as a field's default value or in a command button. Again, I recommend you go with Jack's suggestion if you can. This is just here in case you need it. Good luck!

Function SetID(fld, tbl) As String
Dim strID As String, intID As Integer
On Error GoTo Err_ID
If Left(fld, 1) <> "[" Then fld = "[" & fld
If Right(fld, 1) <> "]" Then fld = fld & "]"
strID = DMax(fld, tbl)
intID = CInt(Right(strID, 3))
again:
Select Case Asc(Left(strID, 1))
    Case 65 To 89
        If intID = 999 Then
            strID = Chr(Asc(Left(strID, 1)) + 1) & "001"
        Else
            strID = Left(strID, 1) & Format(intID + 1, "000")
        End If
    Case 90
        If intID = 999 Then
            GoTo Err_ID
        Else
            strID = Left(strID, 1) & Format(intID + 1, "000")
        End If
    Case 97 To 122
        strID = Chr(Asc(Left(strID, 1)) - 32) & Right(strID, 3)
        GoTo again
    Case Else
        GoTo Err_ID
End Select

SetID = strID
Exit Function
Err_ID:
SetID = "#Error"
End Function

~Abby

[This message has been edited by Abby N (edited 08-17-2001).]

 

[Jack Cowley]

Abby -

I always find your answers concise, accurate and thoughtful so I am very flattered by your kind words. Thank you!

Jack

 

[Judy]

Hello again. Thanks to both of you for the tips. Unfortunately Jack, I picked up the database with this primary key already in place and it would require too many modifications to queries, forms, and reports to change the primary key now. I have been requested to automate the generation of the code number.

Anyway, after realizing the extra work that would be involved in creating a new primary key, I decided to try Abby's method and it works great.

Thank you both for your prompt response to my issue. You folks (and this site) are a tremendous help to beginners like me and are very appreciated!

Have a good weekend,

Judy

 

[Jack Cowley]

Abby is very cool and as you can see, very bright. Good luck with your database!

Jack