Need to generate Unique Serial Numbers for each set of combination

[satifali]

ProjNo - 3 8 DIGIT CODE
DocType - 3 letters code
Typist - 3 letters code

There is a table for each of the above fields containing info enabling select appropriate ProjNo or DocType or Typist.

Master table has several other fields including document reference number filed which will be combination of [ProjNo]-[DocType]-[Tyist]-[Serial No]

We need to generate sequencial serial no for each Typist typing different type of document for a project & then store it to final result as shown above to a DocRefNo field in Master Table

[jforbes]

There are multiple ways of doing this depending on how often the Serial (Sequence) Numbers are being pulled, when you want them pulled (Form Open, New Record, Record/Form Save), whether or not you want them pulled from multiple places, and if you are using SQL Server or Access as your backend database.

As long as you are using Access as your backend, they are all pretty similar. Here is a function that with a little tweaking should do what you want:

Public Function getKey(ByRef sProjNo As String, ByRef sDocType As String, ByRef sTypist As String) As String
     Dim sPrefix As String
     Dim sLastKey As String
     Dim iSequenceLength As String
     iSequenceLength = 5
     sPrefix = sProjNo & "-" & sDocType & "-" & sTypist
     sLastKey = DMax("ReferenceFieldName", "MasterTableName", "MasterTableName LIKE '" & sPrefix & "*'")
     getKey = sPrefix & Right("00000" & Val((Right(sLastKey, Len(sLastKey) - Len(sPrefix))) + 1), iSequenceLen)
 End Function

If you as using SQL Server as your backend, you can put the code in a trigger. It would be similar to the above, but written in TSQL.

I used these as references for the above code:
http://bytes.com/topic/access/answer...mber-data-type
http://bytes.com/topic/access/answer...-access-2003-a