Custom ID Format (1 Viewer)

[nathanmav]

hi everyone does anyone knows how to make a custom ID format that increment every time you add a new record the sample ID look like this "HCCR-SMA-CV-ST-000". hope someone can help me. thanks in advance!

 

[CJ_London]

Can you explain your customid in more detail - from what you have indicated so far it would appear to be

HCCR-SMA-CV-ST-001
HCCR-SMA-CV-ST-002
HCCR-SMA-CV-ST-003
HCCR-SMA-CV-ST-004
etc

In which case, are you limiting the number or records to a maximum of 999? If the 'HCCR-SMA-CV-ST-' part doesn't change this can be stored separately and combined in form view with an autonumber

 

[nathanmav]

Can you explain your customid in more detail - from what you have indicated so far it would appear to be

HCCR-SMA-CV-ST-001
HCCR-SMA-CV-ST-002
HCCR-SMA-CV-ST-003
HCCR-SMA-CV-ST-004
etc

In which case, are you limiting the number or records to a maximum of 999? If the 'HCCR-SMA-CV-ST-' part doesn't change this can be stored separately and combined in form view with an autonumber

yes thats what im trying to do and im limiting it up to 999. no that part is not fixed. CV-ST will change to diffirent category.

if that part is fixed and doesn't change how can i code it.. thanks for the reply.

 

[nanscombe]

The Thread Question Auto generated number might be worth a look.

Towards the end I wrote a function.

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

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

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

    nextIdString = nisPrefix & Format(nextIdString, String(nisNumberOfZeros, "0")) ' create next string Id
    ' Create new ID string by concatenating the formated number to the prefix
End Function

Assuming this is on a form you could use it thus ...

Private Sub Form_BeforeUpdate()
  If Len([Sample ID] & vbNullString) = 0 Then
    Me![Sample ID] = nextIdString("Sample ID", "yourTableName", "HCCR-SMA-CV-ST-", 3) 
  End If
End Sub

It looks for all records starting with the prefix, "HCCR-SMA-CV-ST-" in your case. Finds the largest number. Adds 1. Then sticks the prefix on the front.

eg
Finds "HCCR-SMA-CV-ST-003"
Extracts the "003" on the end as 3
Add 1 to make 4
Creates "HCCR-SMA-CV-ST-004"

 

[CJ_London]

In this forum there are a large number of threads on this subject so quite which is the best one for you, you will need to decide.

One of the issues is the number of users who are creating new records at the same time because the new id will need to be calculated and if two users are inserting a new record at exactly the same time, they could duplicate the ID.

However here is a suggestion.

1. I would still have an autonumber field in your table in case of duplications in your ID
2. Your ID needs to be created at the time the record is inserted into the table and not before - so if your user needs to see the id before inserting, this complicates things a bit.
3. Numbers will increment from the largest number in each CV-ST combination - so old numbers cannot be reused.

To do this put the following code into your form beforeupdate event - note I have made up names so you will need to change these to suit your table/form.

Dim LastNum as string
LastNum=right(nz(dmax("[UniqueID]","myTable","CV='" & me.CV & "' AND ST='" & me.ST & "'"),"000"),3))
If lastnum="999" then
    msgbox "No more items allowed for " & ="HCCR-SMA-" & me.CV & "-" & me.ST
    cancel=true
else
    UniqueID="HCCR-SMA-" & me.CV & "-" & me.ST & "-" & string(3-len(CInt(lastnum)+1),"0") & Cint(lastnum)
end if

I'vbe not tested this, so you will need to try and see