Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Autonumber by who open 1st...

Discussion in 'Information Technology' started by dcash45, Jul 28, 2006.

  1. dcash45

    dcash45
    Expand Collapse
    Guest

    Hi I want to know if there anyone who knows away to add 1
    to a ID base on who opened the form 1st...so there will not be a problem if 2
    or more users was to try and add a record at the same time....

    I used a DMAX to find the last record and + 1 for the next ID but this would
    later give problems with there a more than 1 user trying to add a record...

    So can anyone help me with this problem....

    Thank you..
     
  2. Loading...


  3. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    Autonumbers have one and only one purpose: To create a unique number that
    makes for a petty good primary key. Autonumbers are not guaranteed to be
    sequential. Unless you really need a sequential number, such as for an
    invoice, do yourself a favor and just use an Autonumber field as your primary
    key.

    Further I don't understand the problem with two users entering a record at
    the same time. Access, if properly set up, can easily handle multiple users
    entering data even into the same table. If the problem is two users entering
    the same data at the same time, a unique index would stop this from happening.

    You could use DMax + 1 but it would have to be on a form event after the
    rest of the record has been entered. Something like the Before Update event
    of the form.
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

    "dcash45" wrote:

    > Hi I want to know if there anyone who knows away to add 1
    > to a ID base on who opened the form 1st...so there will not be a problem if 2
    > or more users was to try and add a record at the same time....
    >
    > I used a DMAX to find the last record and + 1 for the next ID but this would
    > later give problems with there a more than 1 user trying to add a record...
    >
    > So can anyone help me with this problem....
    >
    > Thank you..
     
  4. Roger Carlson

    Roger Carlson
    Expand Collapse
    Guest

    On my website (www.rogersaccesslibrary.com), is a small Access database
    sample called "AutonumberProblem.mdb" which shows one way. There is a
    Multi-User example which used the DMax, but when you try to save the record,
    if the number already exists, it goes and gets another one. Basically, you
    add two procedures to the form:

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    On Error GoTo Err_Form_Error
    Response = IncrementField(DataErr)
    Exit_Form_Error:
    Exit Sub
    Err_Form_Error:
    MsgBox Err.Description
    Resume Exit_Form_Error
    End Sub

    Function IncrementField(DataErr)
    If DataErr = 3022 Then
    Me!ProductID = DMax("ProductID", "Product") + 1
    IncrementField = acDataErrContinue
    End If
    End Function

    The first one finds the duplicate (the field MUST be a primary key) and the
    second one finds the next available number. You can download the sample to
    see how it works.

    --
    --Roger Carlson
    MS Access MVP
    Access Database Samples: www.rogersaccesslibrary.com
    Want answers to your Access questions in your Email?
    Free subscription:
    http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


    "dcash45" <dcash45@discussions.microsoft.com> wrote in message
    news:776D3A08-F4E1-469A-A295-6458A7C44A24@microsoft.com...
    > Hi I want to know if there anyone who knows away to add 1
    > to a ID base on who opened the form 1st...so there will not be a problem

    if 2
    > or more users was to try and add a record at the same time....
    >
    > I used a DMAX to find the last record and + 1 for the next ID but this

    would
    > later give problems with there a more than 1 user trying to add a

    record...
    >
    > So can anyone help me with this problem....
    >
    > Thank you..
    >
     
  5. dcash45

    dcash45
    Expand Collapse
    Guest

    Thank you Roger....

    "Roger Carlson" wrote:

    > On my website (www.rogersaccesslibrary.com), is a small Access database
    > sample called "AutonumberProblem.mdb" which shows one way. There is a
    > Multi-User example which used the DMax, but when you try to save the record,
    > if the number already exists, it goes and gets another one. Basically, you
    > add two procedures to the form:
    >
    > Private Sub Form_Error(DataErr As Integer, Response As Integer)
    > On Error GoTo Err_Form_Error
    > Response = IncrementField(DataErr)
    > Exit_Form_Error:
    > Exit Sub
    > Err_Form_Error:
    > MsgBox Err.Description
    > Resume Exit_Form_Error
    > End Sub
    >
    > Function IncrementField(DataErr)
    > If DataErr = 3022 Then
    > Me!ProductID = DMax("ProductID", "Product") + 1
    > IncrementField = acDataErrContinue
    > End If
    > End Function
    >
    > The first one finds the duplicate (the field MUST be a primary key) and the
    > second one finds the next available number. You can download the sample to
    > see how it works.
    >
    > --
    > --Roger Carlson
    > MS Access MVP
    > Access Database Samples: www.rogersaccesslibrary.com
    > Want answers to your Access questions in your Email?
    > Free subscription:
    > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
    >
    >
    > "dcash45" <dcash45@discussions.microsoft.com> wrote in message
    > news:776D3A08-F4E1-469A-A295-6458A7C44A24@microsoft.com...
    > > Hi I want to know if there anyone who knows away to add 1
    > > to a ID base on who opened the form 1st...so there will not be a problem

    > if 2
    > > or more users was to try and add a record at the same time....
    > >
    > > I used a DMAX to find the last record and + 1 for the next ID but this

    > would
    > > later give problems with there a more than 1 user trying to add a

    > record...
    > >
    > > So can anyone help me with this problem....
    > >
    > > Thank you..
    > >

    >
    >
    >
     

Share This Page