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!
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
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
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 ...
Code:
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"
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.
Code:
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