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

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

Need more than one auto number field in access 2003

Hi.
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?
Thanks
Butch
0
ButchDog
Asked:
ButchDog
2 Solutions
 
ChimerazaCommented:
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!
Regards
Nick
0
 
rockiroadsCommented:
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
        rs.AddNew
    Else
        rs.Edit
    End If
    rs!NextNum = Nz(rs!NextNum, 0) + 1
    GetNextReservationNumber = rs!NextNum
    rs.Update

CloseDown:
    rs.Close
    Set rs = Nothing
   
    Exit Function
   
SodThis:
    '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
        Else
            Resume 0
        End If
    Else
        MsgBox err.Description
        GoTo CloseDown
    End If
End Function

0

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