Generate unique number

  • Thread starter Thread starter Ivor Williams
  • Start date Start date
I

Ivor Williams

I have a form on which there is a combo box from which the user chooses a
job number (cboJobNo). Another combo box is used to choose an employee
number (cboEmpNo). I want to include a text box which will have as a data
source a concatenation of [cboJobNo]&"-"&[cboEmpNo]&"-"&[SequentialNumber]
where SequentialNumber is an automatically generated number beginning with
"1" and incrementing by one each time the job number is chosen. For example:
5555-03-1, 5555-06-2, 5555-03-3. If the next job number is 5556, I want the
sequential numbering to restart at "1". How do I set up the SequentialNumber
to work this way?

Ivor
 
Here's some functions that I have used in the past to generate something
similar to what you need. Read thru it and use what you need.

Best regards

Maurice

*** CODE FOLLOWS**

Topic: Function: AutoID() Custom


Function: fAutoID(), fChrExtract(), fValStr()

Public Function fAutoId(strName1 As String, i1 As Integer, i3 As Integer) As
String
' Revised: 06/2001 Version: 1.5.0
' Function returns ID#(Incremented) from passed parameters:
' Arguments: strName1(Alpha Name), i1 (# Alpha Chrs), Source number As
Integer
Dim Alpha1 As String
Dim strSuffix As String
' Increment the passed number suffix and convert to string
strSuffix = Str$(i3 + 1)

Alpha1 = Left$(fChrExtract(UCase(strName1)), i1)

fAutoId = Trim$(UCase(Alpha1)) & Trim$(UCase(strSuffix))

End Function

Public Function fChrExtract(pstrName As String) As String
' Revised: 06/2001
' Function to extract alpha character from passed parameter, Max 6 characters
On Error GoTo Err_fChrExtract

Dim n As Integer
Dim n2 As Integer
If Len(pstrName) < 6 Then
n2 = 6 - Len(pstrName)
pstrName = pstrName + String$(n2, "X")
End If
Dim nCntr As Integer
Dim aryChr(6) As String

Dim StrSet As String
Dim lStr1 As Variant
Dim lStr2 As Variant

n = 0
lStr1 = False
lStr2 = False

For nCntr = 0 To 5

Do
n = n + 1
StrSet = Mid$(UCase(pstrName), n, 1)

lStr1 = IIf(Asc(StrSet) >= 49 And Asc(StrSet) <= 57, True, False)
lStr2 = IIf(Asc(StrSet) >= 65 And Asc(StrSet) <= 90, True, False)

Loop While lStr1 = False And lStr2 = False
aryChr(nCntr) = Mid$(UCase(pstrName), n, 1)

Next nCntr

fChrExtract = aryChr(0) + aryChr(1) + aryChr(2) + aryChr(3) + aryChr(4) +
aryChr(5)

Exit_fChrExtract:
Exit Function

Err_fChrExtract:
MsgBox Err.Description
Resume Exit_fChrExtract
End Function

Public Function fValStr(pstrID As String, NoAlpha As Integer) As Integer
' Revised: 06/2001
' Passed parameter "NoAlpha is the number of alpha characters in string
' Returns numeric value of string ie. ("MA1036") RETURNS 1036
Dim intI As Integer
intI = Len(pstrID) - NoAlpha

fValStr = Val(Right$(pstrID, intI))

End Function


Example:

To extract last used number from a table where ID contains 2 leading Alpha
chars:
Dim n As Integer
Dim strName As String
strName = "Gates"
n = DMax("fValStr(NameID,2)","tblNames")
Assume this produces: 1045

To produce an ID with 2 leading Alpha chars plus number:
Dim stID As String
stID = fAutoID("strName", 2, n)
stID = "GA1046"

Example2:
Let's assume that you have two fields: "Category", "Name"
First create a string with the first two characters from "Category" and "Name"
Assume: "Category" = "Legal", "Name" = "Spence"
Dim stSet1 As String
Dim stSet2 As String
stSet1 = Mid$(fChrExtract("Legal"), 1, 2)
Returns: "LE"
stSet2 = Mid$(fChrExtract("Spence"), 1, 2)
Returns: "SP"
Assume DMax() [see example 1] returns 1036
strID = fAutoID(stSet1+stSet2, 4, 1036)
strID = LESP1037
 
Back
Top