automatic numbering system

[masoud_sedighy]

I like to have a Auto number field in my table (tbltask) that use below codification

B10000001

Where B = Base code and 000001 = Identifier of the task.


The identifier of the task is a unique 7 digits number
​

 

[Cronk]

Autonumber fields store long numbers so you can't have exactly what you want.

Why not just append B1 in front of an autonumber value when you display the record on a form or a report?

 

[ButtonMoon]

Why not just append B1 in front of an autonumber value when you display the record on a form or a report?

Because that way you would get the worst of both worlds: no truly domain-independent surrogate key and your business key is tied to a autonumber-type column with all the inbuilt limitations that implies. Use in-table autonumber columns for surrogate (hidden) keys only - that's what they are designed for.

Masoud,
For your numbering scheme you could first insert to a "sequence" table (a table with just one auto incrementing column and used only for the purpose of key generation). Return the generated value from that table and then use it to populate the key value of your target table. Using a separate sequence table helps guarantee uniqueness without creating a blocking transaction.

Another alternative is to do a "MAX()+1" query against your table but that implies that you must serialise all your inserts. Don't do it unless you really need a sequence without gaps.

 

[nanscombe]

Is you base code numeric (1 2 3 etc), or a string (A B C etc)?

 

[nanscombe]

As suggested above you could create a "key" table. You could have two columns; keyName (String) and keyValue (Long)

(Off the top of my head)

Public Function nextIdString(optional ByVal nisKeyName as string = "", optional ByVal nisNumberOfZeros as Integer = 7) AS Variant
Dim rstKey as Recordset, strTemp as String

  If Len(nisKeyName) = 0 Then
' [COLOR="Red"]If no keyName is entered show a message box and leave[/COLOR]
    MsgBox "You must enter a key name", vbOkOnly
    Exit Function
  End if

' [COLOR="Red"]This line is optional if you want upper or mixed case key prefixes[/COLOR]
  nisKeyName = LCase(nisKeyName)

' [COLOR="red"]Open key table[/COLOR]
  Set rstKey = CurrentDb.OpenRecordset("yourKeyTable", dbOpenDynaset)

  With rstKey

' [COLOR="red"]Look for a record with keyName equal to nisKeyName[/COLOR]
    strTemp = "keyName = '" & nisKeyName & "'"
    .FindFirst strTemp

    If .NoMatch Then
' [COLOR="red"]If no record is found then add one[/COLOR]
      .AddNew
        !keyName = nisKeyName
        !keyValue = 0
      .Update
' [COLOR="red"]Find the record that was just added[/COLOR]
      .FindFirst strTemp
    End If

' [COLOR="red"]Get the current value in the table[/COLOR]
    nextIdString = !keyValue

    .Edit
' [COLOR="red"]Increment it and save the new value[/COLOR]
      !keyValue = nextIdString +1
    .Update

' [COLOR="red"]Create next Id by taking nisKeyName and concatenation the stored number with[/COLOR]
' [COLOR="red"]the required number of leading zeroes.[/COLOR]
    nextIdString = nisKeyName & Format(nextIdString, String(nisNumberOfZeros, "0"))

  End With

' [COLOR="red"]Close the table and set the variable to Nothing[/COLOR]
  rstKey.Close
  Set rstKey = Nothing
End Function

You could call this in the Form_BeforeUpdate event thus.

Private Sub Form_BeforeUpdate(Cancel as Integer)
  If Len(Me!myIdField & vbNullString) = 0 Then
    Me!myIdField = nextIdString("yourKeyname") ' For 7 digits
'    Me!myIdField = nextIdString("yourKeyname", 6) ' For 6 digits
  Endif
End Sub

 

[nanscombe]

Using the DMax() method, here is some code I produced in an earlier thread.

Public Function nextIdString(ByVal nisFieldName As String, ByVal nisTableName As String, ByVal nisPrefix As String, Optional ByVal nisNumberOfZeros As Integer = 4) As Variant

    ' [COLOR="Red"]nisPrefix & "0" gives you a default value if one is not found in the table[/COLOR]
    nextIdString = Nz(DMax("[" & nisFieldName & "]", nisTableName, "[" & nisFieldName & "] Like '" & nisPrefix & "*'"), nisPrefix & "0")

    ' [COLOR="red"]Get next numerical value by looking at the highest value number after the prefix and adding 1[/COLOR]
    nextIdString = Val(Mid(nextIdString, Len(nisPrefix) + 1)) + 1

    ' [COLOR="red"]Create new ID string by concatenating the formated number to the prefix[/COLOR]
    nextIdString = nisPrefix & Format(nextIdString, String(nisNumberOfZeros, "0"))
End Function

This could again be used in the Form_BeforeUpdate event.

Private Sub Form_BeforeUpdate()
  If Len(Me!myIdField & vbNullString) = 0 Then
    Me!myIdField  = nextIdString("yourIDfield", "yourTableName", "yourPrefix", 7) 
  End If
End Sub