Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 14th, 2003, 01:58 PM
Registered User
Join Date: Dec 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Mulitple fields primary key

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

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




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. .

Old December 15th, 2003, 10:13 AM
Authorized User
Join Date: Oct 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts

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
       strKeyValue = intDeptIDParm & "/" & intFileIDParm & "/1"
       RetrieveNewDocKey = strKeyValue
       Exit Function
    End If


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

    strKeyValue = rst("MaxDocKey")

    ' Close the recordset and connection objects

    ' 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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Foreign key not updating with Primary key xavier1945 BOOK: Access 2003 VBA Programmer's Reference 2 July 4th, 2007 09:48 PM
Primary key cf2006 BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 1 July 31st, 2006 07:21 PM
FOREIGN KEY and PRIMARY KEY Constraints junemo Oracle 10 June 15th, 2004 01:00 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.