Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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..
    > >

    >
    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page