We have 3 plants that use the same access D/B. The plants are 22, 33, 44. We have a generic form that launches (using a simple macro) where operators from each plant can input their samples daily. Each plant has continuous numbers but together they don't........... meaning 22-101, 22-103 but 21-100. so if you were in plant 21 the next number should be 101 and if in plant 22 then its 104.
Currently the operator determines the next sequential #.
I would like to automate this so, When a record is first added to this particular database Im working on, I also would like to be able to automatically assign each record with a unique alphanumeric identifier (e.g. 22-104) when a record is first created.
I would appreciate any suggestions as to how to go about accomplishing this. I would also settle for some alternative in automatically assigning some kind of unique alphanumeric identifier.
With the table being named DailySamples and the column containing the plant number being named PlantNumber, you can use the following function:
Function GetNextNumber(ByVal Plant As Long) As String
Dim strPlantNumber As String
strPlantNumber = Nz(DMax("PlantNumber", "DailySamples", "PlantNumber Like '" & Plant & "-*'"), "")
If Len(strPlantNumber) > 0 Then
GetNextNumber = Plant & "-" & Val(Mid(strPlantNumber, InStr(strPlantNumber, "-") + 1)) + 1
' Not found: Handle error.