Generate new customer numbers

[JimmyQ]

Thanks in advance.
I'm looking for form code that will generate new, unique customer numbers
when a new record is added, and replace the CustNo field with the new number.
The function or procedure would need to find the largest existing customer
number, add 1, and return the result. It may be complicated by the fact that
the CustNo field is currently text, not number (altho that could be changed,
if nec.)

 

[Ofer Cohen]

Check this link on "Automatically Increment a Fields Value in a database"

http://www.databasedev.co.uk/automatically_increment_value.html

And yes, it will be better changing the field type to Number, a text field
will return 9 as a grater value then 30, it will compare first the first chr
which is 9 against 3.

 

[JethroUK©]

maybe you can consider that 'unique' refernces dont have to be
'consecutive' - as per primary key/autonumber field which is built-in

 

[Arvin Meyer [MVP]]

You might try something like some code to generate a true alphanumeric with
a range of AA000 to ZZ999, it will error out after this, but you can write
code to cancel if it does (the highest value being 576,000 records) Alter
this code for A00001 to Z99999, the highest value will be for 2,599,974
records.

Function AlphaNumGenerate () As String
' © Arvin Meyer 03/12/1996
' Permission to use or alter this code is granted
' as long as the copyright notice remains intact

' Create a table, called 'tblCounter with one field "Value", Long
Integer data type.
' Use this field as a counter.
' The only record in the field initializes the counter (I used 0 for
AA000)
' In DefaultValue property use: =AlphaNumGenerate Or use with form as
below

Dim db As Database
Dim rst As Recordset
Dim lngCntr As Long
Dim intRetry As Integer
Dim intNum As Integer, intA As Integer, intB As Integer
Dim strANum As String
On Error GoTo ErrorAlphaNumGenerate
Set db = CurrentDB()
Set rst = db.OpenRecordset("tblCounter", db_Open_Dynaset) 'Open table
with the counter
rst.MoveFirst
rst.Edit
rst!Value = rst!Value + 1
rst.Update
lngCntr = CLng(rst!Value) - 1
intNum = lngCntr Mod 1000
intA = (lngCntr \ 1000) Mod 26
intB = (lngCntr \ 1000) \ 26
'Generate the AlphaNumber
strANum = Chr$(intB + 65) & Chr$(intA + 65) & Format$(intNum, "000")
AlphaNumGenerate = strANum
ExitAlphaNumGenerate:
Exit Function
ErrorAlphaNumGenerate: 'If someone is editing this record trap the error
If Err = 3188 Then
intRetry = intRetry + 1
If intRetry < 100 Then
Resume
Else 'Time out retries
MsgBox Error$, 48, "Another user editing this number"
Resume ExitAlphaNumGenerate
End If
Else 'Handle other errors
MsgBox Str$(Err) & " " & Error$, 48, "Problem Generating Number"
Resume ExitAlphaNumGenerate
End If
End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access