Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Need more than one auto number field in access 2003

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?
2 Solutions
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 booking...read that number through SQL...add one, then voila...you have your next unique number.

Let me know if you need more help!
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now