Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    1

    Question Unanswered: Creating a customized "auto-number"

    Any suggestions for creating a field which will 1) serve as the primary key and 2) will be generated whenever a new record is created. This field will be a reference number field which needs to follow the format of: last two digits of the current calendar year, followed by a hypen, followed by a three digit sequence number beginning from 001. The sequence number will need to begin at 001 for the first record of each calendar year.

    Ex. 04-001 through 04-??? (end of 2004 records)
    05-001 through 05-???

    Older records will be imported into this database and I need this field to maintain the current reference number for these older records as well.

    Does anyone have any suggestions for the most efficient approach for this?

    rhb

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Have a look at the Dev Ashish site.

    http://www.mvps.org/access/tables/tbl0005.htm

  3. #3
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    since there is no trigger functions in access I doubt you could do that in table level. but in your form, you could insert any number to your field using form's before insert method. so you could write an autonumber function and use it in before insert event.
    ghozy.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    An implementation I use is to have a table of "unique" numbers that I run a routine to grab then next # and update it to number after it ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by M Owen
    An implementation I use is to have a table of "unique" numbers that I run a routine to grab then next # and update it to number after it ...
    And an example of such:

    Code:
    Function GetNextTrackingNumber() As Long
        On Error GoTo Err_GNTN
        
        Dim NextTrackNum As Long
        Dim TrgRecSet As ADODB.Recordset
        
        Set TrgRecSet = New ADODB.Recordset
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        SQLString = "SELECT [Next Tracking #] FROM BillingSettings;"
        TrgRecSet.Open SQLString, MyConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            NextTrackNum = TrgRecSet.Fields(0).Value
            TrgRecSet.Fields(0).Value = NextTrackNum + 1
            TrgRecSet.Update
        End If
        TrgRecSet.Close
        Set TrgRecSet = Nothing
        
        GetNextTrackingNumber = NextTrackNum
        
    Exit_GNTN:
        Exit Function
        
    Err_GNTN:
        
        MsgBox Err.Number & ": " & Err.Description
        GetNextTrackingNumber = 0
        Resume Exit_GNTN
    End Function
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •