Hello,
I have a system called document management system. it is used to organize all the documents in the company.
the primary key for each document should be composed from 3 fields.
which are department_id , file_id and document_id.

all the files_id are sorted accordding to its related department.

ex. for document reference no

document reference no.: 9/13/95

this means that this document is related to:
department_id: 9
file_id: 13
document:95

the problem is:
when I want to add another document in another department and another file
an auto no. will be generated ex. 96. and the document reference no. will be ex. 7/45/96
another document will 97.


I need a solution to have this auto no. to be like this:
once I add a doucment to a new file in a specific department it should be started from 1 for each file
ex:

9/8/1
9/8/2
9/8/3

--------
9/14/1
9/14/2

--------
7/7/1
7/7/2
7/7/3
7/7/4

--------
7/8/1
7/8/2
7/8/3
and so on

see that file 9/8(department_id:9 and file_id=8) is different from 7/8 (department_id:7 and file_id=8)and each one holds defferent document starting from one.
please, I need to automat the document reference no. .

thanks:D

What you will need to use is a combination of a MAX function in a SQL statement and a simple parser. Assuming that you know the department ID and file id beforehand, you would begin by using a SELECT query with the MAX function.

For example, if your table name is tblDocuments and the primary
key that contains the document ID is called strDocumentKey, here
is a function that will generate the next sequential key for you:

Public Function RetrieveNewDocKey(intDeptIDParm As Integer, intFileIDParm As Integer) As String

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    Dim intIndex As Integer
    Dim intLen As Integer
    Dim intSlash2Location As Integer
    Dim intStartPos As Integer
    Dim lngDocSeqNbr As Long
    Dim strKeyValue As String
    Dim strSQL As String

    ' Open the database connection
    Set cnn = CurrentProject.Connection

    ' Create the SELECT MAX query string
    strSQL = "SELECT MAX(strDocumentKey) As MaxDocKey " & _
             "FROM tblDocuments " & _
             "WHERE strDocumentKey LIKE '" & CStr(intDeptIDParm) & "/" & CStr(intFileIDParm) & "/%';"

    ' Open the recordset using the SQL specified from above
    rst.Open _
       strSQL, cnn, adOpenStatic, adLockReadOnly

    ' If no record was found with the specified Dept and File ID, return a "default" value
    If rst.BOF And rst.EOF Then
       rst.Close
       cnn.Close
       strKeyValue = intDeptIDParm & "/" & intFileIDParm & "/1"
       RetrieveNewDocKey = strKeyValue
       Exit Function
    End If

    rst.MoveFirst

    If IsNull(rst("MaxDocKey")) Then
       rst.Close
       cnn.Close
       strKeyValue = intDeptIDParm & "/" & intFileIDParm & "/1"
       RetrieveNewDocKey = strKeyValue
       Exit Function
    End If

    strKeyValue = rst("MaxDocKey")

    ' Close the recordset and connection objects
    rst.Close
    cnn.Close

    ' Determine the location of the 2nd slash character
    For intIndex = Len(strKeyValue) To 1 Step -1
       If Mid(strKeyValue, intIndex, 1) = "/" Then
          intSlash2Location = intIndex
          Exit For
       End If
    Next intIndex

    ' If the loop above has fully completed without a match, there
    ' is an error in the format of the document key - format should
    ' be nbr/nbr/nbr.
    If intIndex = 1 Then
       MsgBox "RetrieveNewDocKey format error", vbExclamation
       Exit Function
    End If

    ' Extract the current doc sequence number from the key value
    intStartPos = intIndex + 1
    intLen = Len(strKeyValue) - intIndex
    lngDocSeqNbr = Mid(strKeyValue, intStartPos, intLen)

    ' Increment the current doc sequence number by 1
    lngDocSeqNbr = lngDocSeqNbr + 1

    ' Return the new doc key
    strKeyValue = intDeptIDParm & "/" & intFileIDParm & "/" & lngDocSeqNbr
    RetrieveNewDocKey = strKeyValue

End Function



The first part opens the connection and a SELECT query which includes the
MAX function. If there is no match under the BOF/EOF check or the NULL
value check, the function will return the key with a default value of "1"
at the end of the key, since no other entries for the dept id and file
id exist.

Once the key value has been retrieved, you can close the database objects
and then determine the location of the 2nd slash character so you can
retrieve the sequence number only from the end of the key value.

The loop match check is done as a "catch-all" error - in case a slash
was NOT found in the loop, you want to notify the user because there
is some type of formatting error.

The Mid function will retrieve the actual sequence number from the key
value, the sequence number will then be incremented, and then the last
part will build the key with the new sequence value.

If for some reason that you have problems on the match in the SQL statement,
try substituting an asterisk(*) for the percent sign(%) in the LIKE
condition of the query.

Hope that helps!

Warren :D