Need more than one auto number field in access 2003

Posted on 2009-04-15
Last Modified: 2013-11-29
I have an access db that uses the auto number field to link tables together for relational purposes.
I need to generate a random, perhaps incrementing, number in a different field for a customer reservation number.
I could use the auto number already generated to link the tables but it is too long.
Could probly use the last 3 or 4 digits of that number but, so far, that's not what cust wants.
Any ideas?
Question by:ButchDog
    LVL 3

    Accepted Solution

    I assume you can use VBA?

    1) Don't use the last four digits of the autonumber because although unlikely and not very often...its possible that it could be duplicated.
     - However... if you do use it....don't store the field... just use the sub string of the autonumber everytime you need the key.

    2) I would recommend generating a string from the date and time (to seconds if it is possible that you could generate more than one per minute)... That way it will always be unique. So for instance... the string could be 090415211324.....  If that is too long I would recommend using the last method..

    3) Create a random number and insert it into the first booking....everytime you want to create a new that number through SQL...add one, then have your next unique number.

    Let me know if you need more help!
    LVL 65

    Assisted Solution

    I would go for a incrementing number approach. Maybe use a prefix or suffix to identify different things about that reservation.

    Create a table called ReservationNumber with one column called NextNum
    Give it a initial value, there will be just the one row

    Now create a function called GetNextReservationNumber

    The way this func works is it tries to open the table, if it is already opened (by someone else), it waits and retries up to 10 times.
    I put a bit about adding here, if no recs found, it adds a new row, starts from 1.
    otherwise gets the number, adds one, updates database and returns that

    Public Function GetNextReservationNumber() As Long

        Dim rs As DAO.Recordset
        Dim iCnt As Integer
        'Assume the worst, return -1 to signify failure
        GetNextReservationNumber = -1
        iCnt = 0
        On Error GoTo SodThis
        Set rs = CurrentDb.OpenRecordset("ReservationNumber", dbOpenTable, dbDenyWrite)
        If rs.EOF = True Then
            'No records found so lets create one
        End If
        rs!NextNum = Nz(rs!NextNum, 0) + 1
        GetNextReservationNumber = rs!NextNum

        Set rs = Nothing
        Exit Function
        'If table exclusivly opened, then wait a little while and try again
        If err.Number = 3008 Then
            iCnt = iCnt + 1
            'try 10 times
            If iCnt = 10 Then
                GoTo CloseDown
                Resume 0
            End If
            MsgBox err.Description
            GoTo CloseDown
        End If
    End Function


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now